1

I am trying to get specific exchange rates for a given interval from the Bank of Canada I am not very familiar with XLM importing but all the videos and examples on the internet seem to be very different compared to this)

I am using something like this to get the values

=importxml("https://www.bankofcanada.ca/valet/observations/FXCADUSD/xml?start_date=2021-02-01&end_date=2021-05-01","//o")

I tried different XPath alternatives but I was not able to get the dates that correspond to the values. I am trying to get the dates and values side by side.

Any help is much appreciated. Thanks

mkelleci
  • 13
  • 3

1 Answers1

1

The dates are an attribute of the d elements, so to get them you have to change your xpath expression from

//o

to

//o/@d

to get both dates and values (in one column), change the expression to

//o/@d | //o/v

Google Sheets supports only xpath 1.0, unfortunately, so more effective xpath expressions aren't available.

Jack Fleeting
  • 24,385
  • 6
  • 23
  • 45
  • 1
    Thank you Jack, for the detailed answer. I'll have the data in two columns no problem. I wasn't able to figure out getting the attribute looking at other examples. Have a great day! – mkelleci Jul 20 '21 at 19:28