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.
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.
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
