using Google Sheets with Google Finance

One assignment for a class I teach is having students create a stock portfolio. To track the class portfolio's performance, I used Google Sheets so I could share it with  students (I couldn't for the life of me figure out how to share a portfolio created in Google Finance). Lucky for me, Google Sheets can easily work with Google Finance to pull data about stocks. I am very indebted to this post for helping me create the portfolio: https://business.tutsplus.com/tutorials/how-to-track-stock-data-in-google-sheets-with-googlefinance-function--cms-28182

And then I decided I wanted to track daily record of the value of the portfolio and decided to automate the process. To do that, I am indebted to this post: http://www.gadgetsappshacks.com/2013/08/how-to-automatically-record-daily.html

Just some notes on the process since I was a little confused (and I view this blog out as a kind of code-help for REALLY, REALLY non-coders :) ) This is the script that automates the recording of a daily value of the stock portfolio. Here's the script:

1 2 3 4 5 6 7 8

function recordHistory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("History"); var source = sheet.getRange("A1:B1"); var values = source.getValues(); values[0][0] = Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); sheet.appendRow(values[0]); };

Notice that the script references only one worksheet-- the "History" worksheet (line 3). For some reason, I didn't pay enough attention that, but what that means is that any cells you reference must be in the same worksheet. In my case, the total value for the portfolio is calculated in the main worksheet so I also need to create cells that in the History worksheet that pull the values from the original worksheet.

• Create a new document in Google Sheets
• Create two worksheets in the document
• Worksheet 1 should contain all the data (using Google Finance)
• one cell in this worksheet should be designated "=Today" to pull the current date, and 
• another cell is the day's portfolio total value
• Worksheet 2 will contain the daily record (in the code above, worksheet 2 is the "History" sheet)
• set up one cell in worksheet 2 so that it references Sheet 1's "= today" cell (this is the reference A1)
• another cell should reference Worksheet 1's portfolio total (this is the reference to B1)

Don't forget that after you run the script and it works, you'll also want to create triggers to automatically record the date.

I'll post a template for the entire thing later, but I hope this helps someone.