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 และกดบันทึก

2. ใช้งาน
   

ไหนๆทำขนาดนี้แล้ว ขอทำแยกแล้วเอาผลมารวมกันเลยได้ไหม ใช้สูตร เพื่อนำข้อมูลจากหน้าชีตอื่นๆมารวม และใส่สูตรคำนวณสัดส่วน จะได้ดังภาพ

และทุกคนสามารถทดลองสูตรตัวเองได้ในหน้า ทดลองสูตร

PART 2: เพิ่มฟีเจอร์

หลังจาก Investment portfolio ที่สามารถดึงข้อมูลราคาปัจจุบันของหุ้นอเมริกา หุ้นไทย กองทุนไทย ทอง ทองไทย บิตคอย ได้แบบง่ายๆไปแล้ว
*ส่วนข้อมูล PE ปันผล ก็ลองไปดูสูตรได้ในชีทอื่นๆที่ตาม keyword คือ watchlist

รอบนี้หลังจากทิ้งระยะไปสักพักได้ลองชีทอื่นไปหลายแบบแล้ว ขอย้อนกลับอัพเดตเวอร์ชั่นกันหน่อย
ไอเดียในครั้งนี้ คือ เราจะติดตามเปรียบเทียบผลการดำเนินงานเดือนก่อนๆ อย่างไร จะดู Asset allocation (จัดสรรสินทรัพย์) ว่าเราลงอะไรสัดส่วนเท่าไหร่ ควรปรับพอร์ตไหม

จากไอเดียเราก็มาลองปรับเพิ่มจากของเดิมเลย ดังนั้นใครที่มีชีทเก่าอยู่คัดลอกข้อมูลอันเดิมมาใส่อันนี้ควรจะใช้ได้เลย โดยปกติเราจะคัดลอกตาราง port ทุกเดือนไว้ แต่เกิดอยากจะ rebalace ดูสักทีก็อยากรู้ว่าที่ผ่านมาทำยังไงกันบ้าง เราเลยสร้าง portfolio log ขึ้นมาแล้ว สร้างกราฟจาก log

โดยบทความนี้จะแบ่งเป็น

1. ฟีเจอร์ใหม่
2. วิธีใช้
3. อธิบายชีท และ สูตร

ฟีเจอร์ใหม่

1. ปุ่มบันทึกตารางปัจจุบันเพื่อติดตามผลงานย้อนหลัง

2. เพิ่มกราฟ และปรับปรุงโค้ดดึงข้อมูลนิดหน่อย
   กราฟแสดง พอร์ตย้อนหลังมูลค่า-ต้นทุน กำไรขาดทุน Asset allocation
   และ ผลงานแต่ละ port
   กราฟในแต่ละสินทรัพย์

3. แสดงตารางแยกไม่รวมเงินสด ขาดทุนสะสม ในแต่ละสินทรัพย์

วิธีใช้งาน

1. ชีทที่กรอกข้อมูลได้คือ sheet ที่มีแท็บสีเหลือง ได้แก่ เงินสด/ทอง หุ้นไทย กองทุน หุ้นสหรัฐ คริปโต
2. เมื่อเริ่มใช้งานครั้งแรกให้ลบข้อมูลในชีท Portfolio log ก่อน ****
   ถ้าใครเคยใช้ชีทเวอร์ชั่นแรกแล้ว มีการทำเก็บไว้เป็นเดือนๆ สามารถนำมากรอกได้เพิ่มวันที่ใส่เข้าไปได้เลย
3. การกรอกข้อมูลในแต่ละ sheet โดยมีตัวอย่างให้ในแต่ละชีท
4. การกรอก เงินสด ไว้สำหรับเงินที่ยังไม่ได้ซื้อสินทรัพย์
5. การกรอก กำไร/ขาดทุน ไว้สำหรับกรอกกำไรหรือขาดทุนสะสมที่รับรู้แล้วอยากแยกไว้
6. เมื่อต้องการบันทึกตาราง เมื่อกรอกตารางสินทรัพย์ต่างๆครบถ้วน ให้เช็คสรุปในชีท Portfolio dashboard
   ให้กดปุ่ม ระบบจะรันโค้ดคัดลอกตารางปัจจุบันไปใส่หน้า Portfolio log เพื่อให้กราฟแสดงข้อมูล โดยอาจจะกดทุกเดือนหรือครึ่งเดือนแล้วแต่ความถี่ที่ต้องการติดตามผลงาน
7. หากกดปุ่มแล้วขึ้นให้ ข้อมูลไม่ถูกต้อง 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 เป็นหน้าใส่สูตรสำหรับแสดงกราฟ