How to record daily portfolio values in a Google Spreadsheet
In another article I described how to automatically record the history of a row of values in a Google Spreadsheet. Essentially, it describes how to create a History sheet into which a new row is added each day capturing the values of some row that is elsewhere in the spreadsheet. I mentioned in that article that one limitation of the approach is that it only works for values and formulas that don't require the spreadsheet to be open. Specifically, you can't use that approach to record the history of values calculated using the GoogleFinance() function. This means that it can't be used to record the history of a set of portfolio values.
There is a workaround to this which involves using the Google Apps Script FinanceApp service. So if you have a spreadsheet with a set of ticker symbols and quantities, and you want to build a sheet which records a portfolio value at the close of each trading day, then read on...
If you are not familiar with the scripting capabilities in Google Spreadsheet then it might be helpful to start with the previous article.
Create a spreadsheet which has a History tab where cells B1:E1 contain ticker symbols, and the cells below those - B2:E2 - contain the quantities of each in your portfolio. Column A is left blank because it will be used to record the date of each row of values.
Now create a new blank project by visiting Tools > Script Editor and add the following function
function recordHistory() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("History");
var source = sheet.getRange("A1:E2");
var values = source.getValues();
var result = new Array(5);
result[0] = new Date();
for (var i=1; i<5; i++) {
var info = FinanceApp.getStockInfo(values[0][i]);
Logger.log('The stock %s is trading at %s', info.name, info.price);
result[i] = info.price * values[1][i];
}
sheet.appendRow(result);
};
Click save in the editor and pick recordHistory in the pulldown on the toolbar before clicking the play button. The first time you run this it will prompt you that authorization is required. Click continue and then accept. If you visit the History tab you should now see a new row added with the date in the first column and the values of each stock in your portfolio alongside.
If you want this to run at the end of each day, click the Current Project Triggers button in the script editor toolbar. From there, click the link to add a new trigger. Select recordHistory in the first pull-down menu, and time-driven in the second one. Now choose Day Timer, and 9pm-10pm.
That's all there is to it. I've put this sample code in a public google spreadsheet which you can copy by clicking the link. Feel free to ask any questions below or contact me on twitter.
Update: Unfortunately the Google Finance service is deprecated and as of Oct '14 has been shut down. The code above will no longer work. I have rewritten the same code linked above in the public google spreadsheet using the Yahoo Finance URL.
Update 2: Sometime in 2017 the Yahoo Finance URL started returning 404. I have rewritten the code again to use the Google Finance URL.
Update 3: Sometime later in 2017 the Google Finance URL started failing as well. I have rewritten the code again, this time to use the Google Finance website.
Update 4: Sometime in early 2018 the Google Finance website started failing as well. I have rewritten the code again, this time using barrons.com. A side effect of this is you need to include the exchange with the ticker, and they need to be lower case. So where you might previously lookup "IBM" it will now need to be "xnys/ibm". You can work out the exchange names by searching on barrons.com for a ticker, and then looking at the URL.
There is a workaround to this which involves using the Google Apps Script FinanceApp service. So if you have a spreadsheet with a set of ticker symbols and quantities, and you want to build a sheet which records a portfolio value at the close of each trading day, then read on...
If you are not familiar with the scripting capabilities in Google Spreadsheet then it might be helpful to start with the previous article.
Create a spreadsheet which has a History tab where cells B1:E1 contain ticker symbols, and the cells below those - B2:E2 - contain the quantities of each in your portfolio. Column A is left blank because it will be used to record the date of each row of values.
Now create a new blank project by visiting Tools > Script Editor and add the following function
function recordHistory() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("History");
var source = sheet.getRange("A1:E2");
var values = source.getValues();
var result = new Array(5);
result[0] = new Date();
for (var i=1; i<5; i++) {
var info = FinanceApp.getStockInfo(values[0][i]);
Logger.log('The stock %s is trading at %s', info.name, info.price);
result[i] = info.price * values[1][i];
}
sheet.appendRow(result);
};
Click save in the editor and pick recordHistory in the pulldown on the toolbar before clicking the play button. The first time you run this it will prompt you that authorization is required. Click continue and then accept. If you visit the History tab you should now see a new row added with the date in the first column and the values of each stock in your portfolio alongside.
If you want this to run at the end of each day, click the Current Project Triggers button in the script editor toolbar. From there, click the link to add a new trigger. Select recordHistory in the first pull-down menu, and time-driven in the second one. Now choose Day Timer, and 9pm-10pm.
That's all there is to it. I've put this sample code in a public google spreadsheet which you can copy by clicking the link. Feel free to ask any questions below or contact me on twitter.
Update: Unfortunately the Google Finance service is deprecated and as of Oct '14 has been shut down. The code above will no longer work. I have rewritten the same code linked above in the public google spreadsheet using the Yahoo Finance URL.
Update 2: Sometime in 2017 the Yahoo Finance URL started returning 404. I have rewritten the code again to use the Google Finance URL.
Update 3: Sometime later in 2017 the Google Finance URL started failing as well. I have rewritten the code again, this time to use the Google Finance website.
Update 4: Sometime in early 2018 the Google Finance website started failing as well. I have rewritten the code again, this time using barrons.com. A side effect of this is you need to include the exchange with the ticker, and they need to be lower case. So where you might previously lookup "IBM" it will now need to be "xnys/ibm". You can work out the exchange names by searching on barrons.com for a ticker, and then looking at the URL.
Update 5: Sometime around Aug 2021 barrons.com began blocking Apps Script fetches. So I've rewritten the code once again, this time using fool.com. This changes the ticker format again. For example, for IBM you'll need to use "nyse/ibm/ibm" and for MSFT you'll need "nasdaq/microsoft/msft". You can work out others by searching on fool.com for the ticker and look at the URL. As before, click the link above to get a copy of the new code.
Update 6: It seems that fool.com occasionally rejects requests. For folks that just want stocks from the London Stock Exchange I've implemented a lookupTickerLSE function which works with lse.co.uk. I'm open to suggestions of other sites which support stocks on a wide range of exchanges. To use lookupTickerLSE see the comments in the code above the function.
Image Credit: www.paulpreacher.com
Image Credit: www.paulpreacher.com