Google Spreadsheet Autoupdater for Stock/Crypto Prices

spreadsheet, stock, crypto


最近在用google spreadsheet来track自己的stock和crypto,想自动更新price,就去做了一点小研究。

Get Stock Price

对于 Stock price query , Google已经提供了 GOOGLEFINANCE 内置函数来帮助你query

基本语法如下

=GOOGLEFINANCE("GOOG", "price")

你也可以把 GOOG 放在 某一个cell,然后用cell的ID来输入

=GOOGLEFINANCE(A14, "price")

A14的值是 GOOG 或者其他股票代码

第二个参数是 attributions, 可以是volume,等别的数据,不过我们只关心price,其他使用方法请看这里

Get Crypto Price

虽然官方没有crypto的API,但是在add-on里 有人已经做出了基于coinmarketcap的API的小插件

在 Add-ons 里 添加, 搜索 CRYPTOFINANCE 用法和 GOOGLEFINANCE 类似

=CRYPTOFINANCE("BTC/USD", "price")

Refresh Price

其实我还没用弄清楚这个api到底会不会自动update,还是说会在打开文件的时候update。 不过网络给出的结论 controversial, 感觉如果文件是关闭的时候,是不会自动update的

  • https://support.geckoboard.com/hc/en-us/articles/207337458-Use-Google-Sheets-GoogleFinance-function-to-display-data-in-Geckoboard-#updating_your_data_automatically
  • https://webapps.stackexchange.com/questions/58406/can-a-google-spreadsheet-be-recalculated-if-its-not-manually-opened
  • https://productforums.google.com/forum/#!topic/docs/wJeMaKMDpYM

根据文档,写了一个app scripts,每次跑的时候会更新一下所有的prices

function getData() {
  var ss = SpreadsheetApp.openById("<you-spreadsheet-id>");
  var sheet = ss.getSheets()[0]; // default, only one sheet
  var data = sheet.getDataRange();
  Logger.log('num of rows: ' + data.getNumRows())
  for (var irow =1; irow <= data.getNumRows(); irow++) {
    //Asserts,Quant	Price,Owner,Type,Cost Basis,Balance,Last updated
    if (data.getCell(irow, 5).getValue() == 'Stock') {
      Logger.log('Asset name: ' + data.getCell(irow, 1).getValue());
      Logger.log('Asset price: ' + data.getCell(irow, 3).getValue());
      data.getCell(irow, 3).setValue('=GOOGLEFINANCE("'+data.getCell(irow, 1).getValue()+'","price")')
    }
    
    if (data.getCell(irow, 5).getValue() == 'Crypto') {
      Logger.log('Asset name: ' + data.getCell(irow, 1).getValue());
      Logger.log('Asset price: ' + data.getCell(irow, 3).getValue());
      data.getCell(irow, 3).setValue('=CRYPTOFINANCE("'+data.getCell(irow, 1).getValue()+'","price")')
    }
  }
}

为了自动跑,可以设置trigger, 在script里 Edit -> Current Project Trigger 里设置成 间隔一定时间跑一下脚本即可