reformating now() function so it works in GoogleFinance function

1
votes

I'm using GoogleFinance function in Google spreadsheets to display a chart over the last 5 years. I'm using it as follows:

=GoogleFinance(A1;"all";Date(2012,02,08);Date(2013,02,11); "weekly")

This works fine. However, I have to increment the two dates each day if I want the chart to be up to date. I want to change the above so that it automatically updates each day and always shows a chart of the last 5 years. I tried using the now() function like so

=GoogleFinance(A1;"all";Date(2012,02,08);Date(now()); "weekly")

but it seems now() returns the Date in format d/MM/yyyy instead of the required yyyy,MM,dd format. I also tried.

=GoogleFinance(A1;"all";Date(2012,02,08);Date(Text(Now(), "yyyy,MM,dd")); "weekly")

This did not work either. Ideally I need something like the following:

=GoogleFinance(A1;"all";Date(now()-5years);Date(now()); "weekly")

The above does not work, how can I change it so my 5 year chart is always up to date.

1
google-sheetsgoogle-docsgoogle-drive-apigoogle-finance

1 Answers

1
votes

I have now managed to work out how to fix my problem. I used the today() function to solve it as follows

=GoogleFinance(A1;"all";TEXT(TODAY()-YEAR(5);""); TEXT(TODAY();""); "weekly")

A1 contains the stock in my case it was GOOG I can generate the data to create a 5 year chart of any stock now just by changing the value in A1.