สวัสดีครับผม ขอนำเสนอวิธีการใช้ Excel ในการสร้าง portfolio ของกองทุนรวมง่ายๆ ให้เพื่อนๆลองทำตามกันดูนะครับ โดยสาเหตุที่ยกตัวอย่างการสร้าง portfolio ของกองทุนรวมเนื่องจากคิดว่าคงเป็นประโยชน์กับทุกท่านมากกว่าทำ portfolio ของหุ้นนะครับ เพราะสำหรับ portfolio ของหุ้นนั้นท่านแค่ log in เข้าไปใน App streaming ท่านก็สามารถทราบข้อมูลเกี่ยวกับหุ้นตัวที่ท่านถือ พร้อมทั้งกำไรขาดทุนได้ทันที   แต่สำหรับท่านที่ถือกองทุนรวม (รวมไปถึงกองทุนประเภท LTF / RMF ที่สามารถนำไปใช้ลดหย่อนภาษีได้) อยู่หลายๆบลจ. ผมเชื่อว่าท่านคงพบปัญหาเดียวกับผมคือ เวลาประมาณ 2-3 ทุ่มจะต้องเข้าเวบไซต์ของแต่ละบลจ. แล้วมองหากองทุนรวมตัวที่ถือแล้วก็ copy NAV มาไว้ในไฟล์ Excel ที่เรามีการผูกสูตรไว้เพื่อคำนวณหาราคาปัจจุบัน  โดยถ้าเรามีแค่ 1 บลจ. เราก็ยังสามารถเข้าไปในเวบไซต์ของบลจ.นั้นๆ เพื่อดู portfolio ของเราได้ แต่ถ้าเราถือกองทุนรวมหลายๆ บลจ.การที่เราต้องเสียเวลาเข้าเวบไซต์ของบลจ. หลายๆ บลจ.แล้วก็มองหากองทุนรวมตัวที่เราถือ แล้วก็มานั่ง copy and paste (ยังไม่นับว่าจะ copy ผิดตัวหรือเปล่า) นี่คงไม่สนุกแน่นอน  สำหรับผมแล้วการทำแบบนั้นผมมีความสุขมากเวลาที่เราทำแล้วรู้ว่ามูลค่า NAV วันนั้นขึ้น (เพราะตลาดหุ้นขึ้น) แต่วันไหนที่หุ้นลง (โดยเฉพาะแบบ 20 จุดขึ้นไป) วันนั้นแทบไม่อยากจะทำขั้นตอนเดิมๆ ซ้ำๆกันเลยทีเดียวอยากเห็นแค่ว่าวันนี้สรุปแล้วยังกำไรอยู่หรือเปล่าแค่นั้น

ที่มา: http://www.techfice.com/2013/10/16/excel-mutual-fund-portfolio/


วันนี้ผมขอนำเสนอวิธีการใช้ฟังก์ชั่น web query (เป็นฟังก์ชั่นที่ใช้ในการดึงข้อมูลจาก web page มา update ที่ไฟล์ excel ของเราโดยที่เราไม่ต้องเข้าไปที่เวบนั้นๆเพื่อ copy แต่อย่างใด)  โดยฟังก์ชั่นนี้มีติดมาให้ตั้งแต่ Excel 2007 เป็นต้นไป)



สำหรับสูตร / ฟังก์ชั่นที่จะใช้ในการสร้าง Portfolio มีดังต่อไปนี้ครับ

Sum
Vlookup
Web query
Record macro




เปิดไฟล์ Excel ไฟล์ใหม่ขึ้นมาแล้ว save as เป็น macro enable file (.xlsm) เราจะมีการใช้ macro ในการทำปุ่ม refresh ให้ portfolio กันด้วยครับ (เก๋ไหมหละ) สำหรับมือใหม่ที่ไม่เคยใช้ไม่ต้องกลัวนะครับ รับรองว่าทำตาม step by step ได้แน่นอนเพราะเราจะไม่ได้เขียน code VBA ลงไปโดยตรงแต่เราจะใช้การ record macro แทนครับ







สร้าง sheet ชื่อ“Summary” ขึ้นมา 1 Sheet และสร้างตารางตามภาพด้านล่าง และสร้าง sheet เปล่าเท่ากับจำนวนบลจ.ที่ท่านต้องการดึงข้อมูล NAV มา โดยในตัวอย่างนี้ผมสมมติว่าผมต้องการดึงข้อมูล NAV จากเวบไซต์ของบลจ.บัวหลวง และบลจ. กรุงศรีฯ ผมจึงมี sheet เพิ่มขึ้นมาอีก 2 sheet รวมเป็น sheet ทั้งหมด 3 sheet





ใส่สูตรในตารางล่างตามรูปเลยครับ







หลังจากใส่สูตรข้างต้นท่านจะได้ข้อมูลตามรูปด้านล่างครับ





ตอนนี้เรามีตารางคำนวณ NAV พร้อมแล้ว เรามาเริ่มใช้ Web query ในการดึงข้อมูลกันดีกว่าครับ แต่ก่อนที่เราจะเข้าไปในเมนู Web Query ให้เราเข้าไปที่หน้าเวบไซต์ที่มีข้อมูล NAV ของแต่ละบลจ.ตามที่เราต้องการ และ copy address ด้านล่างเป็นของ BBLAM ครับ สำหรับ url ของ BBLAM หน้าที่มี NAV คือ “http://www.bblam.co.th/web2/index.php?id=1&langsel=th”





เริ่มใช้คำสั่ง Web query ด้วยการเข้าเมนู “Data”  ภายใต้ “Get External Data” เลือก “From Web”





Paste URL ของบลจ.ลงบนช่อง Address  แล้วกด “Go”


ถ้ามีข้อความ error ขึ้นมาไม่ต้องสนใจครับตอบ “Yes” ไปเลยครับ
จากนั้นให้สังเกตุเครื่องหมาย รูป

ถ้ามีเครื่องหมายนี้ขึ้นมาหมายความว่าเราสามารถดึงข้อมูลมาได้ครับ (หมายเหตุในบางเวบไซต์อาจมีโครงสร้างของการเก็บข้อมูลที่ไม่เอื้อต่อการดึงข้อมูลผ่านวิธี Web query หรือมีการป้องกันการดึงข้อมูลผ่านช่องทางนี้ทำให้ตารางที่เก็บข้อมูลไม่มีเครื่องหมายถูกให้เลือก หรือสามารถเลือกได้แต่ไม่สามารถดึงข้อมูลได้นะครับ)



ให้เราทำเครื่องหมายถูกหน้าบริเวณที่เราต้องการดึงข้อมูลครับในที่นี้เราต้องการดึงข้อมูล NAV ให้ทำเครื่องหมายถูกที่บริเวณตารางนั้นครับ  จากนั้นกดปุ่ม “Import” ได้เลยครับ


จะมีหน้าต่างขึ้นมาให้เราเลือกว่าจะ import ข้อมูลไปไว้ที่ไหน ให้เราเลือกที่ Existing worksheet แล้วไปเลือกที่เซลล์ A1 ของ sheet ทีเราเตรียมไว้ครับ (ในที่นี้เราจะทำของ BBL ก็ให้เลือกsheet “BBL_data” ครับ จากนั้นกดปุ่ม “OK” ครับ





ได้มาแล้วครับข้อมูล NAV จากเวบไซต์ BBLAM

จะเห็นว่าข้อมูลที่เราดึงมาจะไม่มีหัวตารางมาให้ ดังนัน้ให้เราไปเปรียบเทียบกับบนหน้าเวบว่าข้อมูล NAV คือคอลัมน์ไหน โดยในที่นี้ข้อมูล NAV จะอยู่ที่คอลัมน์ตัวเลขคอลัมน์แรก ซึ่งตรงกับคอลัมน์ “C” นะครับเราก็ทำการ Vlookup ได้เลยครับ
ใส่สูตร Vlookup ในเซลล์ E5 ใน sheet “summary” ดังนี้ครับ =VLOOKUP($D5,BBL_data!$A$1:$G$153,3,false)  เลือกช่วง Table array เป็น A1 – G153 เพราะผมเลือกเผื่อๆอนาคตไว้นะครับ ท่านอาจเลือกช่วงน้อยกว่านี้ก็ได้  ส่วนการทีเลือก column number เป็น 3 เพราะข้อมูลที่เราต้องการอยู่คอลัมน์ “C” ซึ่งจาก Table Array ที่เราเลือกไว้คอลัมน์ A เป็นคอลัมน์ แรกที่เราเลือก ทำให้คอลัมน์ “C” เป็นคอลัมน์ที่ 3 ในสูตร Vlookup นะครับ  สำหรับค่า false เป็นค่าส่วนใหญ่เวลาเราทำ Vlookup ครับ ความหมายสั้นๆคือเราต้องการ match lookup value แบบตรงตัว (exact match) ดังนั้นไม่ต้องคิดมากครับใส่ false ไปเลยครับ  (วันหลังผมจะอธิบายส่วนนี้ให้ละเอียดอีกทีในหัวข้อเจาะลึก Vlookup ครับ)


หลังจากนั้นให้ทำการ copy สูตรไปไว้ในเซลล์ E6 ด้วยครับ เพราะเป็นของ BBLAM เหมือนกัน
ได้มาแล้วครับค่า NAV ล่าสุดที่ประกาศจากเวบไซต์ BBLAM  สำหรับ BAY ก็ทำแบบเดียวกันนะครับแต่ต้องเปลี่ยนสูตร Vlookup เพราะโครงสร้างตารางข้อมูลไม่เหมือนกัน
จนถึงตอนนี้เราได้ตารางคำนวณพร้อมบอกกำไรขาดทุนสำหรับกองทุนของเราแล้วนะครับ แต่ๆๆ เรายังขาดวิธีการ update ครับ สิ่งที่เราอุตส่าห์ทำไปมันจะไม่ work เลยถ้าต้องมานั่งกด import ทุกวันที่เราต้องการทราบราคา NAV   วิธีการ update ตารางที่เราเคย import ไว้แล้วทำง่ายๆครับ แค่คลิ๊กขวาบริเวณข้อมูลในตารางแล้วกด “Refresh” ครับ  ท่านต้องมั่นใจว่าเครื่องของท่านกำลังต่ออินเตอร์เนตอยู่เวลา refresh นะครับไม่งั้นข้อมูลไม่มานะครับ   โดยหลังจาก refresh ให้รอ excel update ข้อมูลแปบนึง แล้วข้อมูล NAV ก็จะlink มาที่ตารางคำนวณกำไรขาดทุนของเราในหน้า summary เองครับ (เพราะเราทำ vlookup ไว้แล้ว)




แต่ๆๆๆ แค่นี้ยังไม่เก๋ครับเราจะไปคลิ๊กขวาเลือก refresh อยู่ทุกครั้งได้ยังไงเราต้องมีปุ่มกดเพื่อให้ excel ช่วย update ข้อมูลให้เราครับ
เริ่มต้นด้วยการ record macro เลยครับ (ก่อนจะ record ตั้งสติให้ดีๆแล้วทำตามขั้นตอนเท่านั้นนะครับอย่าเปิดไปเปิดมาๆหรือพิมพ์อะไรเพิ่มเติมไม่งั้นสิ่งที่เราทำมันจะไปอยู่ใน code แล้วมันก็จะเกิดขึ้นทุกครั้งที่เรากดปุ่มครับ)  ให้เลือกเมนู “View” แล้วเลือก drop down ตรง Macro แล้วเลือก “Record Macro” ครับ


จะมีหน้าต่าง pop up ขึ้นมาให้ตั้งชื่อ macro ตามต้องการ แล้วเลือก Store macro in เป็น “This Workbook” นะครับ หลังจากนั้นให้ทำดังนี้ครับ
ไปที่ sheet ที่เก็บข้อมูล์ NAV ของ BBL คือ BBL_Data แล้วคลิ๊กขวาที่ตารางแล้วกด refresh ครับ
ไปที่ sheet ที่เก็บข้อมูล์ NAV ของ BAY คือ BAY_Data แล้วคลิ๊กขวาที่ตารางแล้วกด refresh ครับ
กด “Stop macro” (หลังจากเรากด “record macro” ปุ่ม “record macro” จะเปลี่ยนเป็น “stop macro” แทนครับ)





ได้ Macro แล้วครับ ต่อมาเรามาสร้างปุ่มกันเลยนะครับ การสร้างปุ่มเราต้องใช้เมนู Developer ซึ่งปกติแล้วจะยังไม่ขึ้นมาให้กดครับ ให้เข้าไปที่ เมนู File  — > Options ครับ





ตรง Customize Ribbon ให้เราติ๊กถูกหน้า Developer ตามภาพเลยครับ


เรามาสร้างปุ่มกันเลยครับ เลือกเมนู “Developer” แล้วเลือก “Insert” ครับ





เลือกอันแรกเลยครับ



หลังจากนั้นเราก็ลากปุ่มตามขนาดที่เราต้องการเลยครับ  พอปล่อย mouse จะมีหน้าต่าง Assign macro ขึ้นมาก็ให้เราเลือก Macro ที่เราเพิ่งสร้างเมื่อกี้ครับ





เราได้ปุ่มสำหรับ update แล้วครับแต่ชื่อปุ่มจะเป็น default มาว่า Button…  ถ้าต้องการแก้ไขก็ให้คลิ๊กขวาที่ปุ่มแล้วเลือก “Edit Text” ครับ  ผมจะแก้เป็นชื่อ “Update NAV”







เสร็จแล้วครับ Portfolio กองทุนรวมของเรา ผมหวังว่าบทความนี้จะเป็นประโยชน์ต่อคุณผู้อ่านทั้งในแง่ของการทำ portfolio ส่วนตัวหรือนำเทคนิคที่ได้ไปใช้ในการทำงานนะครับ



หมายเหตุผมเพิ่มในส่วนของการใช้ conditional formatting มาจัด format ให้เซลล์กำไรขาดทุน โดยถ้ากำไรให้ระบายสีเขียวถ้าขาดทุนให้ระบายสีแดงครับ  อยู่ใน คคห.ที่ 37 ครับ

ด้วยความปรารถนาดีจาก Mr. TechFice



ปล. ถ้าคุณผู้อ่านคิดว่าบทความของ TechFice.com มีประโยชน์ ท่านสามารถให้กำลังใจ Techfice.com ได้ง่ายๆ ดังต่อไปนี้ครับ

1) กด Like แฟนเพจ ของ TechFice ได้ง่ายแค่คลิ๊ก http://www.facebook.com/techfice

2) กด Like ที่บทความที่ชื่นชอบ หรือ share บทความนั้นบน Wall ของท่าน

3) บอกต่อเกี่ยวกับเวบไซต์ TechFice.com ให้กับเพื่อนร่วมงาน หรือ คนรู้จักของท่าน