Hello, I want to keep track of the profit of the combination of many stock values. To do that I have my google Spreadsheet with many formulas calculating the profits or losses ratios taking into account the price I bought them, broker costs and the price right now calculated with:
=GOOGLEFINANCE("MARKET:COMPANY";"price")To keep things simple for this question, let's say I have the following values: In cell A1:
=GOOGLEFINANCE("NASDAQ:GOOG";"price")In cell A2:
=GOOGLEFINANCE("NASDAQ:AMZN";"price")
And I want to keep track of the average of those two values, so A3:
=AVERAGE(A1:A2)
Now I create a new sheet and I put the header of two columns, date, and value. In this sheet I'll store every day at 00:00 the value of the cell A3 with the date in order to build a graph of the daily values.
To do this, I create a function:
function recordHistory() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("Stocks"); //Get values
var source = sheet1.getRange("A2:A3");
var values = source.getValues();
var result = new Array(2); //Sort by date
result[0] = new Date();
result[1] = values[1][0];
var sheet2 = ss.getSheetByName("History"); //Store
sheet2.appendRow(result);
};
Finally I create a trigger, I'm not sure if the translation is correct as I have the spreadsheet in another language, but it's something like:
function, time based, daily, at midnight
Well, this seems to work. Everyday that value get's updated at midnight. The last days I was using the spreadsheet daily so I didn't realize if there was any problem. But after being a week without looking at it at all I saw that the value was always the same, that is, the value calculated from GOOGLEFINANCE the last time I opened the sheet in my browser.
So although it works, it doesn't take updated values of GOOGLEFINANCE.
Is it possible to update all the GOOGLEFINANCE dependent values from the script before storing the values?
Update:
I think by trying to make my problem simpler I gave the wrong question. So, I'll try to reformulate it. I just made a simplified version of the sheet I want to have to store all operations of many international markets and currencies:
The overall net profit is:
=(sum(SOLD)-sum(BOUGHT))/sum(BOUGHT)
The Bought cells are entered manually:
=Shares * Price * Currency Exchange when bought
The Sold cells are filled automatically with Googlefinance data:
=Shares * Current price * Current Currency exchange conversion - Broker costs
Now with the script I wrote above this update (just changing the cell number) I want to keep track of the cell M6, too see how is it going over time with the combination of all my stocks.
Once I sell something I just change manually the cell which until now updated it self with google finance to a fixed value.
The problem and question: The script does this properly but only when I have the spreadsheet open in my browser. I'd like to know if there is some way to call something like "update" to all Googlefinance values (Current Price and $/€ (Current)) from the script, which is executed everyday at midnight.
