Query Google finance for previous currency data (weekdays only) within google sheets?

0
votes

After reviewing the following:

On Google Spreadsheet how can you query 'GoogleFinance' for a past exchange rate?

and subsequently:

Google Spreadsheet, finding the previous weekday

I tried the following:

=(GoogleFinance("CURRENCY:USDGBP", "price", WORKDAY("1/1/2020"+1,-1), WORKDAY("1/8/2020"+1,-1)))

However this is return weekend values also (not required)

Date                Close
1/1/2020 23:58:00   0.7548
1/2/2020 23:58:00   0.7606
1/3/2020 23:58:00   0.7644967
1/4/2020 23:58:00   0.7640879
1/5/2020 23:58:00   0.764575
1/6/2020 23:58:00   0.75925
1/7/2020 23:58:00   0.7625
1/8/2020 23:58:00   0.7631

I just wish to query the past prices for weekdays only:

Expected:

Date                Close
1/1/2020 23:58:00   0.7548
1/2/2020 23:58:00   0.7606
1/3/2020 23:58:00   0.7644967
1/6/2020 23:58:00   0.75925
1/7/2020 23:58:00   0.7625
1/8/2020 23:58:00   0.7631
1
google-sheets

1 Answers

1
votes

I think this will work.

=ARRAYFORMULA({"Date","Close";QUERY(N(GoogleFinance("CURRENCY:USDGBP", "price", WORKDAY("1/1/2020"+1,-1), WORKDAY("1/8/2020"+1,-1))),"where (Col1 % 7)>2")})