Extracting Daily Exchange Rates and Saving them into Excel

[Anonymous]

Hi All,

I'm very new to Power Automate Desktop and I have been watching many YT clips to see if I can find a tutorial  for my request below none of which have been exactly what I need.

I would like to know how to use PAD to extract daily exchange rates using the websites historical data tables when a date is selected and then extracting that data into excel, either using the Get Data function or copy and pasting the table into an excel sheet.

The website I'm using is XE and the URL is https://www.xe.com/currencytables/

I've started my flow with Launch new Chrome and then got stuck. What I would like to do next is as follows:

1. Select currency to be converted into GBP 
2. Select the day before the current date (XE do not provide current day's rates in a table only historical tables)
3. Open new excel file go to the Data tab > Get Data From Web > copy and paste in URL click OK
4. Once connected select Table 0 under Display Option then click Transform Data
5. In Power Query Editor I want to remove columns Name and GBP per Unit then Close & Load
6. Once loaded I would like to save the Excel file as the same date of the currency conversation which would be the date selected in step 2.

I would appreciate any help with this

[MichaelAnnis]

1. Select currency to be converted into GBP 
   1. Populate Text Field on Web Page
2. Select the day before the current date (XE do not provide current day's rates in a table only historical tables)
   1. Get Current DateTime
   2. SubtractDate CurrentDateTime - 1 day to %Yesterday% (this is a date variable)
   3. Convert Date to Text:  %Yesterday% to Custom Format MM-dd-yyyy (or whatever text format you need) to %Yesterday_mmddyyyy% (this is a text variable)
   4. Populate Drop Down (or Text Field) on Web Page with %Yesterday_mmddyyyy% 
3. Open new excel file go to the Data tab > Get Data From Web > copy and paste in URL click OK
4. Once connected select Table 0 under Display Option then click Transform Data
5. In Power Query Editor I want to remove columns Name and GBP per Unit then Close & Load
   1. Sounds like items 3-5 are all done in Excel, can you do this in a VBA Macro.
   2. If the URL changes, PAD can get the url and paste it somewhere in the workbook, and the Macro can reference the cells value to pull it into the Macro
6. Once loaded I would like to save the Excel file as the same date of the currency conversation which would be the date selected in step 2.
   1. Save Excel As '%Yesterday_mmddyyyy% Title.xlsx'

Best of Luck!

[Anonymous · Accepted solution]

Hi @Anonymous 

There's no need of PAD or VBA to achieve what you want, just edition in Power Query. Once you select currency and date, the result page is an URL as follow:

https://www.xe.com/currencytables/?from=GBP&date=2021-08-22#table-section.

You can work directly with this URL instead of having to select currency and date each time you want to update your data. Create your query in Excel / Power Query then tweak it in Advanced Editor so date becomes a parameter you inform:

https://www.xe.com/currencytables/?from=GBP&date=DateParameter#table-section.

In doing so, you just have to inform your date parameter to PQ update the query.

If you want to stick with PAD, try this sequence (tested):
1. 'Get current date and time', %CurrentDateTime% as variable produced
2. 'Add to datetime'. Add= -1, Time unit= Days. %ResultedDate% as variable produced
3. 'Convert datetime to text': Datetime to convert= %ResultedDate%, Format to use= Custom, Custom format= yyyy-MM-dd. %FormattedDateTime% as variable produced
4. 'Set variable': Define %URL% to https://www.xe.com/currencytables/?from=GBP&date=%FormattedDateTime%#table-section
5. 'Launch new Edge' (or your preferred browser) with URL= %URL%
6. 'Extract data from web page'. Here you have to capture the data you want. Select only 'Currency' and 'GBP per unit' columns, as you only want those. Set 'Store data mode'= 'Excel spreadsheet'. This gives you %DataFromWebPage% as variable produced.
7. 'Launch Excel' with a blank worksheet (%ExcelInstance% as variable produced)
8. 'Write to Excel worksheet': Excel instance= %ExcelInstance%, Value to write= %DataFromWebPage%, Write mode= On specified cell, Column=Row= 1
9.'Save Excel': Save mode= Save document as, Document path= [Your path]\%FormattedDateTime%

[Anonymous]

Hi @Anonymous ,

Stage 8 Write to Excel worksheet, is bringing back only some of the Units per GBP in column B and no Currency names in column A.

At Stage 6 Extract data from web page I select the first value USD under the Currency then right clicked and selected Extract element value > Text: ('USD'). I did the same for Units per GBP and repeated this with the second values which highlighted all values both columns . In my Extraction preview window I could see all values in Value#! and Value#21. Clicked finish and was good to go.

Plus I tweaked Stage 6 as the Set  'Store data mode' = Excel spreadsheet opens up excel at this point and later in Stage 8 excel is blank.  My Stage 6 now has Set  'Store data mode' = Variable and then I changed the variable name to %DataFromWebpage% to keep track with your solution.

Is there a step in at Stage 6 Extract data from web page that I could have missed when I was selecting the data I wanted or I have I missed something in the Extraction preview window?

[Anonymous]

Hi @Anonymous 

Just found the error I made and I have accepted your first response as the soultion.  Basically at Stage 3 Convert datetime to text I had changed round the text using format to dd-MM-yyyy (UK date formatting) having noticed that XE's URL was yyyy-MM-dd I changed it back as this was your original instruction.

Many thanks for your help with this! I'll be looking into the Power Query option too!

Thanks again