3
  • 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: Example

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.

5

Functions like GOOGLEFINANCE are only updated when the spreadsheet is open by a user, there isn't a Google Apps Script method that is able to do this. The closest is SpreadsheetApp.flush() but this only makes that the changes made by the script be pushed to the spreadsheet.

One alternative is to rethink your model and take advantage that ...

GOOGLEFINANCE already offer a way to report historic data. The syntax is

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Example

The following formula returns the daily close values of NASDAQ:GOOG from January 1, 2017 to today.

A1:

=GOOGLEFINANCE("NASDAQ:GOOG","price","1/1/2017",TODAY())

The following formula returns the daily close values of NASDAQ:AMZN from January 1, 2017 to today.

D1:

=GOOGLEFINANCE("NASDAQ:AMZN","price","1/1/2017",TODAY())

To calculate the daily average, we could not use AVERAGE with ARRAYFORMULA but we could use the + and / operands:

G1:

=ArrayFormula((B2:B+E2:E)/2)

Note:
Suggestion: Delete the blank rows at the bottom in order to make the calculation of the daily average just for the rows with data.

The history daily average will be calculate from the start date to the actual date every time that the spreadsheet be recalculated.

Result (extract):

Date               Close        Date               Close        Average
 1/3/2017 16:00:00  786.14       1/3/2017 16:00:00  786.14      786.14
 1/4/2017 16:00:00   786.9       1/4/2017 16:00:00   786.9       786.9
 1/5/2017 16:00:00  794.02       1/5/2017 16:00:00  794.02      794.02
 1/6/2017 16:00:00  806.15       1/6/2017 16:00:00  806.15      806.15
 1/9/2017 16:00:00  806.65       1/9/2017 16:00:00  806.65      806.65
1/10/2017 16:00:00  804.79      1/10/2017 16:00:00  804.79      804.79

Note:
Google spreadsheet functions are only recalculated while the spreadsheet is open, so using a script to be ran while the spreadsheet is not opened by anyone will retrieve the values saved the last time the spreadsheet was online-opened/synced offline changes.

References

  • Thank you for your answer, but that's not what I'm looking for. What I want to do is to get the benefits or losses over a big amount of data that changes every now and then. I won't have the same shares and same stocks all the time, so, to use that method won't be useful for me. I want to get the value of a combination of stocks which keeps changing over time . I though it should be possible to call an "update" from the script, but for what I can see in your reference, it's not possible :(. – nck Nov 20 '17 at 19:47
  • 1
    @nck: I extended my answer in order to show how to calculate the daily average what I think is what you are looking for. – Rubén Nov 20 '17 at 20:19
  • Thank you @Rubén, but I think I didn't explain my self properly. I just made an update to show the problem in my own simplified project. – nck Nov 21 '17 at 14:40
1

As the yahoo and google finance API's to use them from google apps script are deprecated, I think the only way to work around it is to generate googlefinance queries from the script as strings and write them in the sheet. Then, once we enter in the sheet those queries are sent.

I don't think this is the most elegant way of do this, and it's also pretty slow to work then with those data, but I couldn't find another solution.

If someone wants to do the same here is what I did to generate my historic data as an example. There may be some errors which need to be changed, but as a reference I think is enough. In my project it worked as it should.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.