- Google Sheet
- Investment
Investment portfolio Google Sheet ดึงข้อมูลราคาหุ้น
เชื่อว่าหลายคนคงเคยทำบัญชี หรือทำพอร์ต (portfolio) เก็บไว้ติดตามผล การลงทุนให้เป็นปัจจุบัน ลง google sheet กันมาบ้าง กำไรเท่าไหร่ ขาดทุนเท่าไหร่ แต่รู้หรือไม่ เราสามารถดึงข้อมูลมาลงได้แบบที่ไม่ต้องมากรอกเองปวดหัว ตาลายกันทุกรอบที่อัปเดตเลย มาดูกันว่าทำอย่างไร
บทความนี้ขอแบ่งเป็น 2 Part คือ
- สอนการดึงข้อมูลโดยละเอียด
- เพิ่มฟีเจอร์ให้ทำติดตามได้เป็นระบบมากขึ้น
PART 1: วิธีดึงข้อมูลราคาหุ้น Google sheet
- วิธีดึงข้อมูลหุ้นสหรัฐ
- วิธีดึงข้อมูลหุ้นไทย
- วิธีดึงข้อมูลราคาทอง
- ดึงข้อมูลราคากองทุน
1. วิธีดึงข้อมูลหุ้นสหรัฐ
google มีสูตรที่ดึงข้อมูลหุ้นอยู่แล้วซึ่งสามารถใช้ได้เลย
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
ticker เป็นชื่อย่อของหลักทรัพย์ที่ต้องการดูข้อมูล ชื่อย่อของตลาดหลักทรัพย์ ตามด้วย ชื่อย่อของหลักทรัพย์ จริงๆ ชื่อย่อของหลักทรัพย์ อย่างเดียวก็ได้แต่มันจะเลือกให้เองถ้าใช่แค่สหรัฐ ใส่แค่ชื่อหุ้นก็ได้ เช่น ใช้ “NASDAQ:GOOG” แทน“ GOOG”
ตัวอย่าง GOOG โดย NASDAQ
GOOGLEFINANCE("NASDAQ:GOOG", "price")
ได้ผลดังรูป
Ref
ชื่อตลาดหลักทรัพย์: https://www.google.com/intl/th/googlefinance/disclaimer/
วิธีใช้แบบเต็มๆ: https://support.google.com/docs/answer/3093281?hl=th
เราก็จะไม่ลืมที่จะลองของไทย และก็พบว่า ตลาดเหล่านี้ดึงไม่ได้ SHA SGX TYO KLSE TSE BKK(SET ของเรานั้นเอง)
ปล.ดึงอัตราแลกเปลี่ยนได้ด้วยนะ
ลองมาปรับตาราง port ดู ให้แสดงหน่วยเป็น บาท
2. วิธีดึงข้อมูลหุ้นไทย
เนื่องจากดึงผ่าน google ไม่ได้เราจึงต้องมาดึงท่าอื่นกันหน่อยโดยการอ่านจากเว็บต่างๆกันแทน
2.1 เว็บ yahoo finance
1.โดยไปที่ Extensions และ Apps Script นำโค้ดไปวาง และบันทึกด้วย
*โค้ดดูได้ใน sheet เลย
หา ticker ของ yahoo finance
https://finance.yahoo.com/
ซึ่งดูได้จากตรงนี้ สังเกตว่าของไทยจะมี .BK ต่อท้าย
ใส่สูตร. *โดยมันจะบอกว่าไม่รู้จัก กด enter หลังใส่สูตร
แต่วิธีนี้จะไม่มี DRX และ อื่นๆ เราก็อย่าลดความพยาม
2.2 เว็บ set (https://www.set.or.th/th/home)
ซึ่งก็ทำได้โดยใช้สูตร
IMPORTXML(url, xpath_query, locale)
url: link เว็บ
xpath : คำค้นหา (ที่อยู่ข้อมูลบนเว็บ)
URL หุ้นไทย
SET : https://www.set.or.th/th/market/product/stock/quote/[ชื่อหุ้น]/price
DRX: https://www.set.or.th/th/market/product/drx/quote/[ชื่อ DRX]/price
*** จากการใช้งานสักพักพบว่าช่วงก่อนตลาดเปิดเว็บจะไม่แสดงราคาเลยเป็น "-" ถ้าอยากได้แบบเก็บอัพเดตทุกเที่ยงคืนลองรีเควทกันมาได้ครับ
วิธีหา url
1. ค้นหาชื่อที่ต้องการบนเว็บ https://www.set.or.th/th/home
2. เลือกและสังเกต url ด้านบน https://www.set.or.th/th/market/product/stock/quote/KBANK/price
เอาชื่อหลักทรัพย์ไปแทนจะได้
url: https://www.set.or.th/th/market/product/stock/quote/[ชื่อหุ้น]/price
xpath ราคาหุ้น = "//div[contains(@class,'stock-info')]”
วิธีหา xpath
1. คลิ้กขวา ตรวจสอบที่ราคาหุ้น inspect
2.1 หาชื่อ class [แนะนำ] ดูคำตรง class ที่สื่อถึงความหมาย ในที่นี้คือ stock-info นำไปใช้ "//div[contains(@class,'stock-info')]”
2.2 หา xpath ตรงๆ คลิ๊กขวา คัดลอก XPath
จะได้
=importxml("https://www.set.or.th/th/market/product/stock/quote/AOT/price","//div[contains(@class,'stock-info')]")
ผลลัพธ์:
3. วิธีดึงข้อมูลราคาทอง
ดึงจากสมาคมทองไทย https://www.goldtraders.or.th/
ใช้วิธี IMPORTXML เหมือนเดิม
=importxml("https://www.goldtraders.or.th/","//span[@id='DetailPlace_uc_goldprices1_lblBLBuy']")
4. ดึงข้อมูลราคากองทุน
ผ่าน settrade.in.th
url:https://www.settrade.com/th/home
หุ้นไทยก็แล้ว แลัวกองทุนละดึงยังไง ไม่รอช้าเราก็ลอง IMPORTXML ก่อน จะได้ค่าว่างมา 555 เพราะเว็บใช้ javascript ในการแสดงผลจึงต้องท่ายากกันหน่อย
ใช้การสร้าง app script เหมือน yahoo ต้องเขียนโค้ดกันหน่อย แต่ทำมาแล้วไม่ต้องห่วง copy paste ได้เลย
โดยไปที่ Extensions และ Apps Script ใส่ code และกดบันทึก
- ใช้งาน
ไหนๆทำขนาดนี้แล้ว ขอทำแยกแล้วเอาผลมารวมกันเลยได้ไหม ใช้สูตร เพื่อนำข้อมูลจากหน้าชีตอื่นๆมารวม และใส่สูตรคำนวณสัดส่วน จะได้ดังภาพ
และทุกคนสามารถทดลองสูตรตัวเองได้ในหน้า ทดลองสูตร
PART 2: เพิ่มฟีเจอร์
หลังจาก Investment portfolio ที่สามารถดึงข้อมูลราคาปัจจุบันของหุ้นอเมริกา หุ้นไทย กองทุนไทย ทอง ทองไทย บิตคอย ได้แบบง่ายๆไปแล้ว
*ส่วนข้อมูล PE ปันผล ก็ลองไปดูสูตรได้ในชีทอื่นๆที่ตาม keyword คือ watchlist
รอบนี้หลังจากทิ้งระยะไปสักพักได้ลองชีทอื่นไปหลายแบบแล้ว ขอย้อนกลับอัพเดตเวอร์ชั่นกันหน่อย
ไอเดียในครั้งนี้ คือ เราจะติดตามเปรียบเทียบผลการดำเนินงานเดือนก่อนๆ อย่างไร จะดู Asset allocation (จัดสรรสินทรัพย์) ว่าเราลงอะไรสัดส่วนเท่าไหร่ ควรปรับพอร์ตไหม
จากไอเดียเราก็มาลองปรับเพิ่มจากของเดิมเลย ดังนั้นใครที่มีชีทเก่าอยู่คัดลอกข้อมูลอันเดิมมาใส่อันนี้ควรจะใช้ได้เลย โดยปกติเราจะคัดลอกตาราง port ทุกเดือนไว้ แต่เกิดอยากจะ rebalace ดูสักทีก็อยากรู้ว่าที่ผ่านมาทำยังไงกันบ้าง เราเลยสร้าง portfolio log ขึ้นมาแล้ว สร้างกราฟจาก log
โดยบทความนี้จะแบ่งเป็น
- ฟีเจอร์ใหม่
- วิธีใช้
- อธิบายชีท และ สูตร
ฟีเจอร์ใหม่
-
ปุ่มบันทึกตารางปัจจุบันเพื่อติดตามผลงานย้อนหลัง
-
เพิ่มกราฟ และปรับปรุงโค้ดดึงข้อมูลนิดหน่อย
กราฟแสดง พอร์ตย้อนหลังมูลค่า-ต้นทุน กำไรขาดทุน Asset allocation
และ ผลงานแต่ละ port
กราฟในแต่ละสินทรัพย์ -
แสดงตารางแยกไม่รวมเงินสด ขาดทุนสะสม ในแต่ละสินทรัพย์
วิธีใช้งาน
- ชีทที่กรอกข้อมูลได้คือ sheet ที่มีแท็บสีเหลือง ได้แก่ เงินสด/ทอง หุ้นไทย กองทุน หุ้นสหรัฐ คริปโต
- เมื่อเริ่มใช้งานครั้งแรกให้ลบข้อมูลในชีท Portfolio log ก่อน ****
ถ้าใครเคยใช้ชีทเวอร์ชั่นแรกแล้ว มีการทำเก็บไว้เป็นเดือนๆ สามารถนำมากรอกได้เพิ่มวันที่ใส่เข้าไปได้เลย - การกรอกข้อมูลในแต่ละ sheet โดยมีตัวอย่างให้ในแต่ละชีท
- การกรอก เงินสด ไว้สำหรับเงินที่ยังไม่ได้ซื้อสินทรัพย์
- การกรอก กำไร/ขาดทุน ไว้สำหรับกรอกกำไรหรือขาดทุนสะสมที่รับรู้แล้วอยากแยกไว้
- เมื่อต้องการบันทึกตาราง เมื่อกรอกตารางสินทรัพย์ต่างๆครบถ้วน ให้เช็คสรุปในชีท Portfolio dashboard
ให้กดปุ่ม ระบบจะรันโค้ดคัดลอกตารางปัจจุบันไปใส่หน้า Portfolio log เพื่อให้กราฟแสดงข้อมูล โดยอาจจะกดทุกเดือนหรือครึ่งเดือนแล้วแต่ความถี่ที่ต้องการติดตามผลงาน - หากกดปุ่มแล้วขึ้นให้ ข้อมูลไม่ถูกต้อง ERROR ให้แก้ไขก่อน คือ มีบางช่องดึงข้อมูลไม่ได้ให้ตรวจให้ไม่ขึ้น error จึงจะกดบันทึกตารางได้
อธิบายชีท และ สูตร
เราจะขออธิบายเพิ่มในสูตรที่เพิ่ม และเปลี่ยนแปลง
- สูตร yahooF มีการอัพเดตเนื่องจากเปลี่ยนรูปแบบนิดหน่อย
- โค้ดในการคัดลอกตารางสำหรับการบันทึกตาราง
function appendTransactionPort() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
//collect data
const srcRange = ss.getRangeByName("inputTansactionPort");
const srcVals = srcRange.getValues().filter((a) => a[0] != "");
// validate
const anyCellError = srcVals.flat().findIndex((cell) => cell == "#ERROR!");
if (anyCellError !== -1) {
const ui = SpreadsheetApp.getUi();
ui.alert("ข้อมูลไม่ถูกต้อง ERROR", "โปรดแก้ไขก่อน", ui.ButtonSet.OK);
return;
}
// Get current date
var currentDate = new Date();
// Prepend the date to each sub-array
for (var i = 0; i < srcVals.length; i++) {
srcVals[i].unshift(currentDate);
}
const desSheet = ss.getSheetByName("Portfolio log");
var lastRow = desSheet.getLastRow();
var lastColumn = desSheet.getLastColumn();
desSheet
.getRange(lastRow + 1, 1, srcVals.length, lastColumn)
.setValues(srcVals);
ss.toast("เพิ่ม Portfolio log สำเร็จ");
}
ชีท Portfolio dashboard เป็นชีทที่เอาไว้ดูสรุปเดือนปัจจุบัน และมีกราฟย้อนหลัง
ชีท Portfolio log ชีทสำหรับเก็บข้อมูล port ย้อนหลัง
ชีท Portfolio query เป็นหน้าใส่สูตรสำหรับแสดงกราฟ