Thursday, March 14, 2019

GOOGLE SHEET 讀取美股, 港股, 新加坡股 股價

GOOGLE SHEET 讀取美股, 港股, 新加坡股

 當手上的股數目開始變多, 
有必要MONITOR 自己的資產組合
用EXCEL 人手入現價實在太花時間
用GOOGLE SHEET 可以直接LOAD 入現價
就可以很容易看到自己的PORTFOLIO 賺蝕情況
如下

 現價要SET FORMULA
優先股, 美股, 港股可以用最簡單的=GOOGLEFINANCE(A8,"price")
A8 就是股票號, RETURN 現價

 由於GOOGLE FINANCE 不能讀取新加坡股, 所以要用另一個方法
=importxml(https://www.bloomberg.com/quote/BWCU:SP,"//span[contains(@class,'priceText')]")

 這樣, 以我的PORTFOLIO 來講, 只要手動輸入債券價格便可

 而且GOOGLE SHEET 放在GOOGLE DRIVE 上, 隨時隨地都可以更新資料
十分方便


除讀取股價外, GOOGLE FINANCE 還可以讀取其他股票資料
詳細可看以下說明
https://support.google.com/docs/answer/3093281?hl=zh-Hant

另外, 為方便計算, 除股票現價外, 其他金額我會轉回HKD 計算, 如總值, 股息等
而匯率始終每日不同,
所以也要用GOOGLE FINANCE 去讀取最新匯率
FORMULA 是=GOOGLEFINANCE(("CURRENCY:usdhkd"))
這樣就可以方便地計算唔同地方的價錢,
很實用


發佈者： 於
標籤： , , , , , , , , , ,

23 comments:

  1. AnonymousJune 22, 2019 at 5:34 PM

    Thanks! its useful!

    ReplyDelete
  2. HaileyOctober 28, 2019 at 8:09 PM

    謝謝你的分享！想請教一下如想設現價股息率的FORMULA是什麼？
    港股方面我試了這個碼不成功，希望可以指教一下 =(GoogleFinance(B4,"yieldpct"))
    另外也想問新加坡股如想讀取股息率/股息金額之類，可有formula可參考嗎？謝謝你

    ReplyDelete
    Replies
    1. 初心投資者October 29, 2019 at 4:57 AM

      我試過用GOOGLEFINANCE 的YIELD, 但不成功, 最後我是手動輸入過去12個月的派息, 再除返即時股價

      Delete
    2. HaileyOctober 29, 2019 at 1:07 PM

      好的！謝謝你的回覆

      Delete
    3. 初心投資者November 1, 2019 at 2:04 AM

      不客氣, 多謝支持

      Delete
    4. 比高December 5, 2019 at 3:30 PM

      試試這個, 把GOF改成其他code, 新加坡code後加.si

      =SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/GOF?p=GOF&.tsrc=fin-srch","table",2),6,2),"("),1,2),")")

      Delete
    5. 初心投資者December 5, 2019 at 5:32 PM

      很好用喎, 除左息率, 仲可以有咩資料IMPORT 到??

      Delete
    6. KLJanuary 10, 2020 at 12:58 AM

      =importxml(https://www.bloomberg.com/quote/BWCU:SP,"//span[contains(@class,'priceText')]")

      =importxml("https://www.bloomberg.com/quote/BWCU:SP","//span[contains(@class,'priceText')]")

      Delete
    7. 初心投資者January 10, 2020 at 4:50 AM

      新加坡股我也是用這個方法, 想問下除了"priceText" 可以IMPORT 到股價, 仲有咩資料可以LOAD 到? 好似52 周高低, NEXT EX-DIVIDEND?

      Delete
    8. SteveOctober 22, 2021 at 6:46 AM

      萬分感謝比高分享股息率自動匯入指令！好人好報！

      Delete
    9. YUJanuary 23, 2022 at 5:45 PM

      唔好意思，各位高手，參考咗你們的方法，所以我將上述formula改成
      =SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML("https://finance.yahoo.com/quote/1310.HK?p=1310.HK&.tsrc=fin-srch","table",2),6,2),"("),1,2),")")以查閱港股股息率，但想請問為什麼計算出#N/A???

      Delete
  3. NatalieJune 24, 2020 at 12:21 AM

    唔好意思...新手試左幾次
    想用 google sheet 報取新加坡 Stock
    Copy 以下落 google sheet 都唔得...上youtube 又搵唔到教學
    如果要知RW0U.SI ...應該要改邊度? Thanks
    =importxml(https://www.bloomberg.com/quote/BWCU:SP,"//span[contains(@class,'priceText')]")

    ReplyDelete
    Replies
    1. 初心投資者June 24, 2020 at 1:57 AM

      你的https: 前面, 少了一個"
      應該如下, 你再試試
      =importxml("https://www.bloomberg.com/quote/RW0U:SP","//span[contains(@class,'priceText')]")

      Delete
    2. NatalieJune 24, 2020 at 11:08 AM

      感激師姐...剛剛成功喇..我想講...你勁過 google and youtube .... 我 search 左成晚都搵唔到 answer...
      試左 BWCU & Z74 都成功
      但想問如果係google sheet A1 column 打個code (example : RW0U)下面 formula RW0U 打 A1 就食唔到變 error ....請問可以點 set ? 感激
      =importxml("https://www.bloomberg.com/quote/RW0U:SP","//span[contains(@class,'priceText')]")

      Delete
    3. 初心投資者June 26, 2020 at 3:30 AM

      我既做法,
      A1 照打RW0U, 係表格最後加一欄, 如Z1係網址: https://www.bloomberg.com/quote/RW0U:SP

      =importxml(Z1,"//span[contains(@class,'priceText')]")

      我係咁樣做

      Delete
    4. NatalieJune 26, 2020 at 11:39 AM

      Oh....我反複試左十幾次,終於明白喇...要有三個 column 先得... 謝謝教識我...

      Delete
    5. 初心投資者June 27, 2020 at 9:18 PM

      不客氣

      Delete
    6. NatalieJune 29, 2020 at 11:47 AM

      師姐....
      唔好意思啊....琴日留左言問野...點知唔記得 save ....搵唔返自己個留言,要係度再留過...sorry ...
      我 IB開好, excel 打好,想買美國國庫債券 Ｕ.Ｓ. treasury bonds,係ＩＢ打左好多次都搵唔到 ?請問應該點 search ? 同埋買Ｕ.Ｓ. treasury bonds 要唔要扣 30% tax ? 謝謝你反教導 thanks

      Delete
  4. AnonymousJuly 3, 2020 at 10:45 AM

    近期formula用唔到, 請問你有冇咁嘅情況?

    ReplyDelete
    Replies
    1. 初心投資者July 4, 2020 at 2:31 AM

      美股和港股, 英股, 匯率都無問題, 你是那一隻股票有問題?

      Delete
    2. AnonymousJuly 4, 2020 at 7:58 AM

      是GOOGLEFINANCE港股，最近一直loading,睇唔到個價，你的沒問題嗎？

      Delete
    3. 初心投資者July 5, 2020 at 4:53 PM

      沒有這個問題, 不論是港股, 美股, 都一樣LOAD 到價錢喎

      Delete
  5. GigiOctober 4, 2020 at 7:27 PM

    最近日股同新加坡股都load唔倒價錢，請問你有這個問題嗎？

    ReplyDelete

Subscribe to: Post Comments (Atom)