9/22日更新google試算表興櫃上櫃股價抓取教學

 

意料之外

解決上櫃股價的同時順手也把興櫃搞定了

為了避免方法太快被bang掉

這次就暫時先不公開

只放在pressplay

教學連結：

https://www.pressplay.cc/link/D96A8D1DA8?oid=69C6EB15C8

用 Google 試算表抓取上櫃 興櫃交易量、收開盤價等資料 教學(使用IMPORTXML函式)

本次文章以上市、上櫃、興櫃各一支股票為例，來跟大家說明如何使用IMPORTXML函式抓取第三方來源的股價、開盤價、前一日收盤價、交易量、市值等等資料


如果還沒看前面文章的記得先去補充基本概念

用 Google 試算表製做簡易股票報價表及理財表

用 Google 試算表製做歷史股價走勢圖(K線)

用 Google 試算表進行技術分析(雙週線)（月線）

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

因為有網友問能不能抓成交量

秉持研究的精神，反覆試驗後終於成功了

一樣先帶大家複習一下IMPORTXML函式

官方文件請見下方連結

https://support.google.com/docs/answer/3093342?hl=zh-Hant

簡單的說，就是針對特定網址去做XPath_查詢

我知道講太多技術的東西大家會想放棄

直接告訴大家結論

本次用IMPORTXML函式從bloomberg的網站抓資料

在Bloomberg上，台股的代碼通常是XXXX:TT

前一篇文章已經講過如何抓股價了

接下來要說明的是其他的資料

因為這次撈回來的資料比較多

所以我們轉變一下格式

改成橫的

B3用下方這串來撈股價

=IMPORTXML("https://www.bloomberg.com/quote/"&$B2&"","//span[@class='priceText__1853e8a5']")

然後在B4那欄打上下方的程式碼，就可以撈到開盤價、前一日收盤價、交易量、市值等等資料

=IMPORTXML("https://www.bloomberg.com/quote/"&$B2&"","//div[@class='value__b93f12ea']")

再把其他欄位如法炮製

就成功了

是不是很簡單呢？

本次程式碼已經更新到理財表格長期支援版內

有訂閱的朋友記得去下方連結文章留言email就可以收到表格了

https://www.pressplay.cc/link/F61286A858?oid=69C6EB15C8

我將思想傳授他人，他人之所得，亦無損於我之所有；猶如一人以我的燭火點燭，光亮與他同在，我卻不因此身處黑暗。湯瑪斯‧傑弗遜（Thomas Jefferson）

用 Google 試算表抓取上櫃 興櫃股價資料 教學(使用IMPORTXML函式查詢bloomberg網站資料)

本次文章以上市、上櫃、興櫃各一支股票為例，來跟大家說明如何使用IMPORTXML函式抓取第三方來源的股價與其他資料


如果還沒看前面文章的記得先去補充基本概念

用 Google 試算表製做簡易股票報價表及理財表

用 Google 試算表製做歷史股價走勢圖(K線)

用 Google 試算表進行技術分析(雙週線)（月線）

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

好啦以下是正文，之前每次改了一下抓取的資料源，過沒多久就被封掉了

感覺每次都在跟資料源競賽，看是先找到抓取方式，還是先被封掉XDD

這次要感謝一位網友提供一個方法

不過直接貼上使用卻失敗了，後來我重新去Bloomberg網站研究了一下，並且研究了一下IMPORTXML函式的使用方式，終於成功了

首先先來說明一下IMPORTXML函式

官方文件請見下方連結

https://support.google.com/docs/answer/3093342?hl=zh-Hant

簡單的說，就是針對特定網址去做XPath_查詢

我知道講太多技術的東西大家會想放棄

直接告訴大家結論

本次用IMPORTXML函式從bloomberg的網站抓資料

在Bloomberg上，台股的代碼通常是XXXX:TT，公式如下

=IMPORTXML("https://www.bloomberg.com/quote/"&$B2&"","//span[@class='priceText__1853e8a5']")

我先在B2的欄位放入我要查詢的股票代號，如下圖，把想要查詢的台股代碼加上:TT

然後再於C2的欄位放上公式

就成功啦！！！

以下如法泡製，就可以把上櫃的金洲，與興櫃的乾杯股價查詢出來了

是不是很簡單呢？

本次程式碼已經更新到理財表格長期支援版內

有訂閱的朋友記得去下方連結文章留言email就可以收到表格了

https://www.pressplay.cc/link/F61286A858?oid=69C6EB15C8

我將思想傳授他人，他人之所得，亦無損於我之所有；猶如一人以我的燭火點燭，光亮與他同在，我卻不因此身處黑暗。湯瑪斯‧傑弗遜（Thomas Jefferson）

用 Google 試算表製作理財表格抓取共同基金(Mutual Fund)各項資料

之前很多人問如何抓取基金資料
現在寫了一篇彙整的
來教大家如何運用內建函式來抓取共同基金資料



包含晨星評比、淨資產、配息率、週報酬、年報酬、三年報酬等等

這次也會用比較簡單的寫法
可以讓大家只要改動股票或基金代號即可更新一切的資料

以之前我們常用的寫法為例
=GOOGLEFINANCE("股票代號", "屬性")

以查詢VTI股價做範例的話該這樣寫
=GOOGLEFINANCE("VTI", "price")



但這樣每次都要重新輸入股價跟屬性很麻煩
下方這次的寫法可以快速解決本次問題

本篇文章以先鋒退休目標2055為例
Vanguard Target Retirement 2055 Fund Investor Shares

選這隻純粹是確定可以抓到所有的資料

首先我們先在左上角輸入我們要查詢的基金資料

這樣我們等等要運用的時候才不會需要手動重複輸入

然後建立我們要的屬性表
屬性概述如下

• "closeyest" - 前一天的收盤價。
• "date" - 所回報的基金淨值所屬日期。
• "returnytd" - 年初至今報酬率。
• "netassets" - 淨資產。
• "change" - 最近回報基金淨值與上一筆基金淨值的變化。
• "changepct" - 基金淨值的變化百分比。
• "yieldpct" - 配息率、前 12 個月收入分配總額 (股息與固定收益利息) 以及基金淨值收益除以上個月的基金淨值。
• "returnday" - 單日總計報酬率。
• "return1" - 單週總計報酬率。
• "return4" - 4 週總計報酬率。
• "return13" - 13 週總計報酬率。
• "return52" - 52 週 (1 年) 總計報酬率。
• "return156" - 156 週 (3 年) 總計報酬率。
• "return260" - 260 週 (5 年) 總計報酬率。
• "incomedividend" - 最近的現金配息金額。
• "incomedividenddate" - 最近的現金配息日期。
• "capitalgain" - 最近的資本利得分配金額。
• "morningstarrating" - 晨星的「星級」評比。
• "expenseratio" - 基金的支出比率。

我們把它輸入在下方

接著我們在右側輸入
=GoogleFinance($B$1,A5)

這裡可能有人不知道$代表的是什麼意思
先簡單說明一下

1.相對位址[B1]   欄列均未鎖定 
2.絕對位址[$B$1]   欄名及列號均鎖定 
3.混合位址[$B1]或[B$1]
 [$B1]為鎖欄不鎖列
 [B$1]為鎖列不鎖欄

而這裡的B1就是我們剛剛寫好的代號那欄
A5就是前面的屬性
兩相對照一下是不是容易理解了呢
=GOOGLEFINANCE("股票代號", "屬性")
=GoogleFinance($B$1,A5)
兩相對照一下是不是容易理解了呢

接下來就可以用把滑鼠移到右下角出現十字往下拉

我們可以看看拉到底後
最後一行是
=GoogleFinance($B$1,A24)
google很聰明地幫我們依序填好了

這樣未來只要改動B1的代號，就可以一次更新所有的資料了


補上中文對照讓大家參考

本次表格全部公式已更新至理財表格長期支援版
有訂閱的朋友記得去留言你的email
我會開啟權限給你
https://www.pressplay.cc/link/F61286A858?oid=69C6EB15C8

長期支援版本是因為之前無償分享的理財表格常常被改動

加上分流的檔案太多已經沒有心力去維護

於是建立一個長期支援版本供有訂閱的朋友們運用

顧名思義就是會持續維護的版本

主要有幾個特點

1.會每週定期維護，確保檔案能夠維持在正確的狀況

2.會不斷更新新的功能，目前在研究匯入其他券商的資料，定期紀錄資產的方式

=============================================================

該分頁也有新增繪製近一年走勢圖的公式與製作方式
詳細的教學會在下一篇文章說明

參考資料來源
https://support.google.com/docs/answer/3093281?hl=zh-Hant


歡迎分享轉載文章，願每一個人都能夠衣食無虞。

我將思想傳授他人，他人之所得，亦無損於我之所有；猶如一人以我的燭火點燭，光亮與他同在，我卻不因此身處黑暗。湯瑪斯‧傑弗遜（Thomas Jefferson）

如果你覺得我寫的文章不錯
可以按讚給我支持與鼓勵
https://www.facebook.com/IEPSYfinancenote/

資工心理人的理財筆記

新成立的ig歡迎追蹤:iepsyfinancenote
https://www.instagram.com/iepsyfinancenote/ 

用 Google 試算表製做簡易股票報價表及理財表(整合版) 教學--新增多種外幣匯率表

前面有人問除了虛擬幣之外
是否能夠建個台幣兌換外幣的匯率表呢？

其實也滿簡單的，只要查一下各種外幣的代碼即可

舉兩個例子
台幣的代號是TWD
美金的代號是USD
如果是想知道多少台幣可以換1美金的話，公式如下
=GoogleFinance("CURRENCY:USDTWD")
如果是想知道1台幣可以換多少美金的話，公式如下
=GoogleFinance("CURRENCY:TWDUSD")

同理，以瑞士法郎為例
如果是想知道多少1瑞士法郎可以換多少台幣的話，公式如下
=GoogleFinance("CURRENCY:CHFTWD")
如果是想知道多少1台幣可以換多少瑞士法郎的話，公式如下
=GoogleFinance("CURRENCY:TWDCHF")

是不是很簡單呢？

本次常用外幣匯率表已經更新到理財表格長期支援版內
包含美金 、歐元、英鎊 、澳幣、加拿大幣、新加坡幣、瑞士法郎、日圓、南非幣

有訂閱的朋友記得去該文章留言email就可以收到表格了
https://www.pressplay.cc/link/F61286A858?oid=69C6EB15C8



========================================


如果是第一次來的朋友
關於本表格的說明
請見下方內文
============================================

有鑑於表格分成太多種了，為了方便許多網友使用，所以我做了一個整合版的表格

教學連結依序留在下方(請由上往下服用)
用 Google 試算表製做簡易股票報價表及理財表(更新版) 教學
用 Google 試算表製做歷史股價走勢圖(K線) 教學
用 Google 試算表進行技術分析(雙週線)（月線） 教學
用 Google 試算表抓取第三方資料教學 （以基金淨值為例）
用 Google 試算表抓取上櫃 興櫃股價資料 教學


額外小提醒：
1.抓取上櫃興櫃股票部分，無法使用內建的函式，請參照上方的教學。
2.基金或者是其他第三方資料的抓取，可以參考上方抓取第三方資料教學。
3.獲得表格使用權限後，請記得建立副本之後再編輯，不然之後的使用者會就看不到原本的表格。



不同方法的表格放在左下角不同的分頁
更方便各位網友切換

常用的屬性如下

屬性 - [選用- "price" 為預設] - 要從 Google 財經服務擷取的代號相關屬性。如果指定日期，即須使用這個項目。

• 屬性指的是下列即時資料：
• "price" - 即時報價，最多延遲 20 分鐘。
• "priceopen" - 股市開盤時的價格。
• "high" - 今天的最高價。
• "low" - 今天的最低價。
• "volume" - 今天的股市交易量。
• "marketcap" - 股票的市場價值。
• "tradetime" - 最後成交時間。
• "datadelay" - 即時資料延遲的時間。
• "volumeavg" - 平均每日交易量。
• "pe" - 每股價格/每股盈餘比例。
• "eps" - 每一股的盈餘。
• "high52" - 52 週期間的最高價。
• "low52" - 52 週期間的最低價。
• "change" - 自上一個交易日收盤以來的價格變化。

歡迎分享轉載文章，願每一個人都能夠衣食無虞。

我將思想傳授他人，他人之所得，亦無損於我之所有；猶如一人以我的燭火點燭，光亮與他同在，我卻不因此身處黑暗。湯瑪斯‧傑弗遜（Thomas Jefferson）

如果你覺得我寫的文章不錯
可以按讚給我支持與鼓勵
https://www.facebook.com/IEPSYfinancenote/

資工心理人的理財筆記

新成立的ig歡迎追蹤:iepsyfinancenote
https://www.instagram.com/iepsyfinancenote/ 

用 Google 試算表製做簡易股票報價表及理財表(整合版) 教學--新增台灣與各國指數教學

本篇文章來告訴大家新增指數資料的方式

有網友提到需要用google試算表抓取一些指數數據
卻不知從何下手

查找了一下相關文件後
詳細公式臚列如下

台股加權指數
=GOOGLEFINANCE("TPE:TAIEX", "price")
美國道瓊指數
=GOOGLEFINANCE(".DJI")
美國納斯達克
=GOOGLEFINANCE(".IXIC")
美國標普五百
=GOOGLEFINANCE(".INX")
美國羅素兩千
=GOOGLEFINANCE("INDEXRUSSELL:RUT")
日本東證股價指數
=GOOGLEFINANCE("INDEXTOPIX:TOPIX","price")





簡單整理供大家參考
已新增至股票理財表格訂閱長期支援版1.2
有訂閱的朋友可以直接去看表格

========================================


如果是第一次來的朋友
關於本表格的說明
請見下方內文
============================================

有鑑於表格分成太多種了，為了方便許多網友使用，所以我做了一個整合版的表格

教學連結依序留在下方(請由上往下服用)
用 Google 試算表製做簡易股票報價表及理財表(更新版) 教學
用 Google 試算表製做歷史股價走勢圖(K線) 教學
用 Google 試算表進行技術分析(雙週線)（月線） 教學
用 Google 試算表抓取第三方資料教學 （以基金淨值為例）
用 Google 試算表抓取上櫃 興櫃股價資料 教學


額外小提醒：
1.抓取上櫃興櫃股票部分，無法使用內建的函式，請參照上方的教學。
2.基金或者是其他第三方資料的抓取，可以參考上方抓取第三方資料教學。
3.獲得表格使用權限後，請記得建立副本之後再編輯，不然之後的使用者會就看不到原本的表格。



不同方法的表格放在左下角不同的分頁
更方便各位網友切換

常用的屬性如下

屬性 - [選用- "price" 為預設] - 要從 Google 財經服務擷取的代號相關屬性。如果指定日期，即須使用這個項目。

• 屬性指的是下列即時資料：
  • "price" - 即時報價，最多延遲 20 分鐘。
  • "priceopen" - 股市開盤時的價格。
  • "high" - 今天的最高價。
  • "low" - 今天的最低價。
  • "volume" - 今天的股市交易量。
  • "marketcap" - 股票的市場價值。
  • "tradetime" - 最後成交時間。
  • "datadelay" - 即時資料延遲的時間。
  • "volumeavg" - 平均每日交易量。
  • "pe" - 每股價格/每股盈餘比例。
  • "eps" - 每一股的盈餘。
  • "high52" - 52 週期間的最高價。
  • "low52" - 52 週期間的最低價。
  • "change" - 自上一個交易日收盤以來的價格變化。

如果你覺得我寫的文章不錯
可以按讚給我支持與鼓勵
https://www.facebook.com/IEPSYfinancenote/

資工心理人的理財筆記

新成立的ig歡迎追蹤:iepsyfinancenote
https://www.instagram.com/iepsyfinancenote/