Google sheets and Bank Of Canada Valet API - how to import data monthly for USD/CAD

Question

I managed to play around with the BoC Valet API in Google sheets to obtain daily USDCAD rate for a specific period.

=importxml("https://www.bankofcanada.ca/valet/observations/FXCADUSD/xml?start_date="&text(Q31,"YYYY-mm-dd")&"&end_date="&text(Q32,"YYYY-mm-dd"),"//o/@d | //o/v")

Now I wish to do this between two date but return monthly data.

=importxml("https://www.bankofcanada.ca/valet/observations/group/FX_RATES_MONTHLY/xml?start_date=2023-01-23&end_date=2023-07-19&order_dir=asc","//o/@d | //o/v")

The result with this query is all the monthly FX rates available. What xpath should I use in the Google sheets xpath_query field to filter this data for USDCAD only ?

Answer 1

Can you test this one out:

=chooserows(wrapcols(importxml("https://www.bankofcanada.ca/valet/observations/group/FX_RATES_MONTHLY/xml?start_date=2023-01-23&end_date=2023-07-19&order_dir=asc","//o/@d|//v/@s|//o/v"),47),1,-1,-2)

UPDATED ANSWER:

=let(Σ,importxml("https://www.bankofcanada.ca/valet/observations/group/FX_RATES_MONTHLY/xml?start_date=2023-01-01&end_date=2023-12-31&order_dir=asc","//o/@d|//v/@s|//o/v"),Λ,sequence(counta(Σ)),
     exch_,filter(Λ,xmatch(Σ,"FXMUSDCAD")),
     date_,filter(Λ,isdate_strict(Σ)),
     chooserows(Σ,sort({exch_;date_;exch_-1})))