Managing stock portfolio in Excel
In a previous entry, I described how to add new entries to the rest-Excel library. Up till now we’ve just looked at populating static data in a worksheet with data picked up from a call to site that can provide jSon response to a rest query.
You may want to just update part of a sheet that has some calculations in it, for example a stock portfolio. Here is the code to populate a sheet with data from Google Finance
Public Sub testGoogleFinance() generalReport restQuery _ ("googlefinance", "google finance", , "ticker", , , , , False) _ , True End Sub
In this case, our sheet looks like this –
The input is the ticker column, the other columns with yellow headers are populated by the code given earlier.The columns with the blue header contain formulas based on the data returned by the rest query
In this case we needed to do 2 things differently than previous examples on this blog.
- Instruct that the library does not blank out the sheet before populating (the false in the call arguments)
- Leave a blank column between the data to be populated (the yellow), and that to be left alone (the blue), and also a blank row between the data and the total. This will ensure the formulas remain intact.
That’s all there is to it. See rest to Excel library for downloads and more information
