Answered

Extracting Daily Exchange Rates and Saving them into Excel

Like (0) Share

Report

Posted on 23 Aug 2021 15:34:33 by Community Power Pla...

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:

Select currency to be converted into GBP
Select the day before the current date (XE do not provide current day's rates in a table only historical tables)
Open new excel file go to the Data tab > Get Data From Web > copy and paste in URL click OK
Once connected select Table 0 under Display Option then click Transform Data
In Power Query Editor I want to remove columns Name and GBP per Unit then Close & Load
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

Categories:

Process Advisor

All responses (4)

Answers (1)

Community Power Pla... on 24 Aug 2021 at 12:14:17

Like (1)

Report

Re: Extracting Daily Exchange Rates and Saving them into Excel

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
Community Power Pla... on 24 Aug 2021 at 11:38:23

Like (0)

Report

Re: Extracting Daily Exchange Rates and Saving them into Excel

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?
Verified answer

Community Power Pla... on 23 Aug 2021 at 18:06:38

Like (1)

Report

Re: Extracting Daily Exchange Rates and Saving them into Excel

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%
MichaelAnnis 5,721 Moderator on 23 Aug 2021 at 15:50:21

Like (0)

Report
Re: Extracting Daily Exchange Rates and Saving them into Excel

Select currency to be converted into GBP
Populate Text Field on Web Page
Select the day before the current date (XE do not provide current day's rates in a table only historical tables)
Get Current DateTime
SubtractDate CurrentDateTime - 1 day to %Yesterday% (this is a date variable)
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)
Populate Drop Down (or Text Field) on Web Page with %Yesterday_mmddyyyy%
Open new excel file go to the Data tab > Get Data From Web > copy and paste in URL click OK
Once connected select Table 0 under Display Option then click Transform Data
In Power Query Editor I want to remove columns Name and GBP per Unit then Close & Load
Sounds like items 3-5 are all done in Excel, can you do this in a VBA Macro.
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
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.
Save Excel As '%Yesterday_mmddyyyy% Title.xlsx'

Best of Luck!