2

I am trying to use GOOGLEFINANCE function to query the weekly close price of an instrument (SP500, ticker ".INX"), for the last 52 weeks.

I first tried using the following formula:

​GOOGLEFINANCE("INDEXSP:.INX";"close";TODAY()-365;TODAY();"WEEKLY")

Where TODAY() in this case corresponds to 31/01/2020 (Thursday).

However, when I look at the results, I see that the following issues: I get 57 results, instead of the expected 52 There are 8 rows correspond to dates not falling on a Friday. I do not get a result for the nearest Friday (25/12/2020).

See first screenshot.enter image description here

I then decided to force the start and end dates passed as arguments to the Google Finance function to fall on a Friday:

GOOGLEFINANCE("INDEXSP:.INX";"close";SWITCH(WEEKDAY($E3;2);1;TO_DATE(WORKDAY($E3;-1));2;TO_DATE(WORKDAY($E3;-2));3;TO_DATE(WORKDAY($E3;-3));4;TO_DATE(WORKDAY($E3;-4));5;TO_DATE($E3);6;TO_DATE(WORKDAY($E3+1;-1));7;TO_DATE(WORKDAY($E3+1;-1)));SWITCH(WEEKDAY($E4;2);1;TO_DATE(WORKDAY($E4;-1));2;TO_DATE(WORKDAY($E4;-2));3;TO_DATE(WORKDAY($E4;-3));4;TO_DATE(WORKDAY($E4;-4));5;TO_DATE($E4);6;TO_DATE(WORKDAY($E4+1;-1));7;TO_DATE(WORKDAY($E4+1;-1)));"WEEKLY")

With this query, if the dates passed to the start or end date fall on a day other than a Friday, then the dates are adjusted to the previous Friday (workday).

In this case, the results look very similar but shifted one week before. The issues are the same, I get results for dates that do not fall on a Friday, and additionally, I do not get a result for the most recent Friday (25/12/2020).

One could think, that GOOGLEFINANCE ​adjusts results for those trading days when the stocks are closed, however there are several examples where I get results for a Tuesday (28/07/2020), but I also get results for the previous (24/07/2020) and next Friday (31/07/2020), and there are no bank holidays in between. See second screenshot.

enter image description here

I would appreciate your help.

You can also find a link to the sheet here: https://docs.google.com/spreadsheets/d/1_FmpRNKVGUtbdLUa2msrFej_WEQ9QkAwvXz89c7l4hU/edit?usp=sharing

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.