Google's Undocumented Finance API

這幾天來因yahoo finance的歷史股價下載api中斷，我便決心轉用google finance。
原本很簡單的，google finance的網頁有過往股價的顯示和下載如：
https://www.google.com.hk/finance/historical?q=HKG:2800&startdate=2012-01-01&enddate=2017-12-31&num=200&output=csv
但... :( 竟然得到以下對待！：-
404. That’s an error.
The requested URL was not found on this server. That’s all we know.
原來非美國股票只能顯示(&output=)，不能下載(&output=csv)。

我參考了 http://www.networkerror.org/component/content/article/1-technical-wootness/44-googles-undocumented-finance-api.html
動手做了以下的 ods oobasic 代碼 (excel vba 請斟酌改動)：

-------------------------------------------------------
'20170520 - try google instead of yahoo
'    cURL = "http://ichart.finance.yahoo.com/table.csv?s=" & cSymbols & "&a=" & cStartMonth & "&b=" & cStartDay & "&c=" & cStartYear & "&d=" & cEndMonth & "&e=" & cEndDay & "&f=" & cEndYear & "&g=" & cGranularity & "&ignore=.csv"
     cURL = "https://www.google.com/finance/getprices?q=" & LEFT(cSymbols,4) & "&x=HKG&i=86400&p=5Y&f=d,c,h,l,o,v"

   ' Open up a new spreadsheet from the above URL.
   ' Specify the CSV filter with options that decode the CSV format comming back from Yahoo.
   ' Specify the Hidden property so that the spreadsheet does not appear on the screen.
      oCalcDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0,_
      Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ),_
            MakePropertyValue( "FilterOptions", "44,34,SYSTEM,1,1/10/2/10/3/10/4/10/5/10/6/10/7/10/8/10/9/10" ),_
            MakePropertyValue( "Hidden", True ) ) )
            ' 20170520 - change True to False for debug
 
Do Until oCalcDoc.IsLoaded
  wait(100)
Loop

   ' Get the first sheet of the Calc document.
    oSheet = oCalcDoc.getSheets().getByIndex( 0 )
    oCell = oSheet.getCellRangeByName("K1")
    oCell.setString("")
    oCell.setFormula("=COUNTA(A2:A3737)")
    iCount = oCell.getValue()

' 20170522 - convert unix timestamp to date in Google finance history OSheet
'            also swap Open / close price
Dim iRow
Dim tempCell
Dim tempint as double
Dim iUnixTime
for iRow = 9 to iCount + 1
    oCell = oSheet.getCellRangeByName("B" & CSTR(iRow))
    tempCell = oCell.getValue()                                       ' tempCell = B[i]
    oCell.setValue(oSheet.getCellRangeByName("E" & CSTR(iRow)).getValue())  ' B[i] CLOSE = E[i] OPEN
    oSheet.getCellRangeByName("E" & CSTR(iRow)).setValue(tempCell)          ' E[i] = tempCell
    oSheet.getCellRangeByName("G" & CSTR(iRow)).setValue(tempCell)          ' G[i] = tempCell adjclose = close
 
 ' convert unix timestamp in google finance getprices to date
'  =IF(LEFT(A9,1)="a", INT(VALUE(RIGHT(A9,10))/86400) + DATE(1970,1,1), IF(LEFT(A8,1)="a", G8+A9, G8+A9-A8))
 
    oCell = oSheet.getCellRangeByName("A" & CSTR(iRow))
    if LEFT(oCell.getString(),1) = "a" then
       tempint = RIGHT(oCell.getString(),10)
       iUnixTime = INT(tempint/86400) + DATEVALUE("1970-01-01")
       oCell.setValue(iUnixTime)
    else
       oCell.setValue(iUnixTime + oCell.getValue())
    endif
next

' v1_14 - use cellrange copy
' OldPosition =  "A2:G" & CSTR(1+iCount) ' "A2:G90"
' 20170522 - for google finance, copy A9:
 OldPosition =  "A9:G" & CSTR(1+iCount) ' "A9:G90"

  oSource = oSheet.getCellRangeByName(OldPosition)    

               ' NewPosition =  "I" & CSTR(1+iRow) & ":J" & CSTR(1+iRow) ' "I18:J18"
               ' print "1+iRow=", 1+iRow, " NewPosition=", NewPosition
               ' oTargetSheet = ThisComponent.Sheets.getByName("stockprice")
               
               'oTarget = oTargetSheet.getCellRangeByName(NewPosition)
 '              NewPosition =  "A37:G" & CSTR(36+iCount) ' "A37:G90"
 ' 20170522 - for google finance, end row - 7
                iCount = iCount - 7
             
                NewPosition =  "A37:G" & CSTR(36+iCount) ' "A37:G90"              
               oTarget = oChartSheet.getCellRangeByName(NewPosition)
           oTarget.DataArray = oSource.DataArray

   ' Be sure to close the spreadsheet, because it is hidden, and the user cannot close it.
   ' 20170520 - do not close for debug
    oCalcDoc.close( True )
 ThisComponent.calculateAll()

-----------------------------------------------------