I’m studying how Major Events affect the US and AU stock markets, such as the Dotcom bubble, 911, COVID etc.
Using GoogleFinance(), I’m trying to compare the weekly close for the S&P500 Index (INDEXSP:.INX) and the ASX All Ords Index (INDEXASX:XAO) for various time periods, for example 2000-2010.
However, the ASX All Ords only returns records from 2010-04-20, which is a problem as I’d like to display the data as a Chart.
The following is one of the functions that return the data:
=QUERY( googlefinance(E10,"all",$F7,$F8,"WEEKLY"), "SELECT Col1, Col5 LABEL Col1 'All Ords', Col5 'Value' ")
I’d like to add extra blank rows for the ASX data so the weekly dates line up. In the case of the range 2000-2010, I need to add 67 blank rows to align dates.
Is there any way to structure the Query, such as adding a UNION clause somehow, to achieve this? Or any alternate method?
Advertisement
Answer
You’ll want to use a vlookup function of the dates on the S&P results INTO the array of the ASX results.
I’ve laid out the basics here on this spreadsheet that was created specifically for this thread.
the formula I’m talking about is in cell D4 on the tab called Comparison. I rounded the dates so that the chart is cleaner.
=ARRAYFORMULA(IFERROR(VLOOKUP(B4:B,QUERY(ROUND(GOOGLEFINANCE(D3,"close","1/1/1990",TODAY(),"weekly"),{0,2}),"offset 1",0),2,TRUE)))