Excel Sheet updates Exchange Rates from a Web Page

No links - but you should be able to upload a file

go to go advanced and look for the manage attachments link
then upload a desensitized version.
that would save anyone looking at this some effort/allow them to know a solution works.

it isn't a drop down, it is a link.

This process doesn't work for you?

https://www.excelforum.com/faq.php?d...l&titlesonly=0

I assume you have Get&Transform or PowerQuery, from your description in your initial post.

If so...

Use following url, use basic anonymous access.

Please Login or Register  to view this content.

Once data is brought in. Click on observations (list).
This will expand records. Now convert list into table using "List tools".
Use button found at top of column to expand. Then again expand for ALL of FX columns to see data within.
Note: You can create custom function to do this as well.

Once that's done, filter out dates to exclude ranges that you don't need (json response has data from 2017-01 to current).
Demote headers to first row. Then transpose entire table.

Promote first row back to header. This will mirror structure of table found below.
https://www.bankofcanada.ca/rates/ex...xchange-rates/

Once satisfied with result, load it onto worksheet. You can hit Refresh All to bring in new data.

Sample image of result below (just kept few FX codes).
0.JPG

Edit: Alternately if you have Excel 2013 or later, you can use /xml instead of /json and use WEBSERVICE() and FILTERXML() functions to bring in data.
Edit2: In addition to above, you should add argument, "?recent=5" at the end to limit number of records returned.
Ex:

Formula:

Please Login or Register  to view this content.

Hmm? I refreshed the sample file that I did and it worked fine.

0.JPG

See attached sample file (you will need Excel 2013 or later I believe).

Oh, I forgot to mention. You should remove last step in the query editor.
It will prevent query from finishing with new data.

it actually worked now! I tweeked something but I forgot what ?
So here are the applied steps of the queries:
Source > Navigation > Converted to Table > Expanded Column1 > Expanded Column1.FXUSDCAD > Expanded Column1.FXGBPCAD > Expanded Column1.FXEURCAD > Expanded Column1.FXJPYCAD > Expanded Column1.FXMYRCAD > Removed Columns (I removed the other currencies I didint need) > Demoted Headers > Transposed Table > Promoted Headers

does that seem similar to you queries ?
the only way I found to request only the 5 most recent dates and therefore the daily rate is to include the "?recent=5". IT didnt work initially because i filtered the dates and this will only keep the dates filtered but not feed the new ones. I dont think there is a workaround within excel appart from modifiying the source URL data...

You could add variable within M, using Advanced Query Editor.
Though it's probably faster to query with "?recent=5"

Ex: This will return previous week's Monday's date.

Please Login or Register  to view this content.

Then at location where you apply date filter...
replace #date() part with startDate variable.

Please Login or Register  to view this content.