Automated Currency Conversion Using Power Automate Desktop

In this video, Jonathon Silva demonstrates how to use Power Automate Desktop (PAD) to automate the process of currency conversion for an Excel sheet. Instead of manually converting amounts from one currency to another, which can be time-consuming and tedious, Silva shows how to streamline the process using PAD’s automation capabilities. This is particularly useful for businesses that deal with international transactions and need to convert currencies regularly.

Setting Up the Excel Table

Jonathon starts with a simple Excel workbook that contains a table with currency amounts in US dollars (USD). This table includes columns for an ID, company name, and the amount to convert. The goal is to convert these values into another currency, such as Euros or Canadian dollars, and automatically populate the converted values into the same Excel file.

• Step 1: Launch Excel Automatically - The first step is to launch the Excel file automatically using Power Automate Desktop. Jonathon uses the "Launch Excel" action to open the specified Excel file. He ensures that the file opens in a visible instance, so changes can be made directly in the document. This eliminates the need for manual intervention.
• Step 2: Identify Table Boundaries - Next, Silva demonstrates how to automatically identify the range of the data by using variables to determine the first free row and column. This ensures that the automation only processes rows with valid data, avoiding unnecessary processing of empty rows.

Setting Up the Loop for Currency Conversion

The process then continues with creating a loop to iterate through each currency value in the Excel table. The loop is set to start from the second row (the first row contains headers) and will iterate through each row until the last value is processed. The loop ensures that every single currency value is captured and converted one by one.

• Step 3: Read Data from Excel - Within the loop, Jonathon adds an action to read from the Excel worksheet. The data for each currency value is captured row by row, and it’s stored in a variable, which will be used for conversion.
• Step 4: Launch Web Browser for Currency Conversion - After reading the original currency value, the next step is to use a browser automation action to launch a new Chrome browser window. Jonathon uses a popular online currency converter website (like Wise) to handle the conversion. The flow automatically inputs the original currency value into the website’s converter form.
• Step 5: Extract the Converted Value - Once the original currency amount is entered into the converter, the next action is to extract the converted value from the web page. This is done by selecting the UI element for the converted currency amount on the website, which is then stored in a variable for later use.

Writing Back the Converted Value to Excel

After the conversion, the next step is to write the converted amount back into the Excel sheet. Jonathon uses the "Write to Excel" action to input the newly converted value into the appropriate row and column (in this case, column D). The loop continues to process each row in the table, converting each value and adding the result to the Excel sheet.

• Step 6: Close the Web Browser and Excel - To ensure that the automation runs smoothly and does not leave unnecessary instances open, Jonathon closes the web browser and the Excel file once all values have been processed. This prevents multiple browser windows or Excel files from remaining open after the task completes.

Finalizing the Automation

After successfully running the flow, Jonathon demonstrates how to save the Excel file and close it. This allows the entire process—from reading the data to converting and saving the results—to be completed automatically without any manual input. Once the flow is set up, it can be reused indefinitely for similar tasks.

Conclusion

Jonathon Silva concludes by emphasizing the power of Power Automate Desktop to eliminate repetitive manual tasks, like currency conversion, and save time. Automating these processes ensures greater efficiency and consistency, especially for businesses involved in international trade.

• Key Takeaways:
  • Automating currency conversion with Power Automate Desktop saves time and reduces errors.
  • Power Automate Desktop can interact with Excel and web pages, making it a versatile tool for various tasks.
  • Once set up, this automation can be reused for future tasks, streamlining workflow.

This video offers a practical example of how businesses can leverage Power Automate Desktop to improve efficiency, particularly in industries that handle multiple currencies.

Posted by Pragmatic Works

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Automate and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.