'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Import and Refresh Exchange Rates in Excel using Power Query
Question details
The user needs to retrieve live exchange rate data into Excel and configure it to refresh automatically, specifically extracting values from websites like BBC Market Data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking financial markets or automating currency conversions using live web data.
- Observed behavior
- Requires a structured method to pull specific exchange rate values from a web page, load them into a spreadsheet, and set up a recurring data refresh schedule.
Ensure you have a stable internet connection to retrieve live web data and verify that the target website permits data extraction without requiring specialized API authentication.
Import Exchange Rates Using Power Query from the Web
Extract specific currency exchange rates directly from websites like BBC Market Data using the 'From Web' feature in Power Query.
Power Query is the most robust tool for scraping and formatting data from HTML tables on web pages. It allows you to clean the imported data and set up automatic refresh schedules so your exchange rates are always up to date.
Open Excel, go to the 'Data' tab on the ribbon, and click 'From Web' in the Get & Transform Data group.
In the dialog box that appears, paste the URL of the exchange rate website (e.g., the specific BBC Market Data page) and click 'OK'.
The Navigator window will open, displaying the available elements from the webpage. Click through the 'Table' items on the left until you find the one containing the exchange rates, then click 'Transform Data'.
In the Power Query Editor, remove any unnecessary columns or rows. Once the data is clean, click 'Close & Load' in the top-left corner to import it into your worksheet.
To ensure the rates stay current, right-click anywhere in your new data table, select 'Table' > 'External Data Properties', and check the box for 'Refresh every X minutes' or 'Refresh data when opening the file'.
Use Excel's Built-in Currency Data Types
For users with supported Microsoft 365 versions, you can use the built-in Stocks or Currency data types to easily pull exchange rates without web scraping.
How to Import Web Data in WPS Spreadsheets
WPS Spreadsheets provides a straightforward 'Import External Data' feature, making it incredibly easy to pull exchange rate tables from financial websites directly into your workbook without complex configurations.
- 1. Navigate to Data Import: Open your workbook in WPS Spreadsheets and click on the 'Data' tab located on the top ribbon.
- 2. Select Import External Data: Click on 'Import Data' and select the 'Import External Data' option from the drop-down menu.
- 3. Enter the Website URL: In the web query dialog that appears, type or paste the URL of the exchange rate provider into the address bar and click 'Go'.
- 4. Select and Import the Table: Identify the data table containing the exchange rates on the loaded web page, check the small yellow box next to it, and click 'Import' to place the data into your sheet.
Frequently Asked Questions
Why is my Power Query web data not refreshing automatically?
To enable automatic refresh, you need to configure the connection properties. Right-click your query in the Queries & Connections pane, select 'Properties', and check the box for 'Refresh every X minutes' or 'Refresh data when opening the file'.
Can I import exchange rates without using Power Query?
Yes. In supported versions of Excel, you can use the built-in 'Currencies' data type found in the Data tab. Simply type a currency pair like 'EUR/USD', select it, and click 'Currencies' to fetch live data.
How do I fix the 'DataFormat.Error' when importing from a webpage?
This error typically occurs when the website's structure changes or the selected HTML table does not match the expected format. Open the Power Query Editor, review the 'Applied Steps' on the right, and ensure the columns are mapped to the correct data types (e.g., removing text characters from number columns).
Does WPS Office support importing data from web pages?
Yes, WPS Spreadsheets includes an 'Import External Data' feature found under the Data tab. It allows you to navigate to a specific webpage and select HTML tables to import directly into your cells, providing an excellent alternative for retrieving online exchange rates.
