We think that everyone should have access to forex data. Therefore, we created an easy-to-follow guide to let Excel users access the exchange rates. You will discover how to use Power Query to import and change real-time FX rates in Excel. Anyone with a basic understanding of Excel should be able to follow the tutorial since it doesn't require any programming skills.
Please read our guide on the Excel Add-In if you don't care about power queries and would prefer to utilize our Add-in to import Forex and CFD data into Microsoft Excel. A video is also included with the story.
You must sign up for our API and open an Excel spreadsheet before we can begin. Not to worry. It happens quickly.
You may see this tutorial as a video as well:
[You must sign up for our API and open an Excel spreadsheet before we can begin. Not to worry. It happens quickly.
You may see this tutorial as a video as well:
Let's start!
Part I
Step 1
Please click Data in the spreadsheet. Then, as displayed below, click From Web (To import data from the web).
First, log in now. Then go to our documentation page and copy the Live endpoint's URL as displayed below.
Step 2
After copying the URL, paste it into the Excel box and hit OK.
Step 3
Your browser will take you to another box when you click OK. Drill down by selecting List from the context menu (next to the quotes), as seen below.
Step 4
When you click, a redirect will appear. Once more, using the right-click menu, select List and select "To Table," as shown in the figure below. There will be a box. Please press OK to close that box.
Step 5
Click the box with two arrows to the right of column 1 on the following page. To utilize the original column name as a prefix, uncheck the box and click "OK."
Step 6
Live rates will now appear in the boxes; click close and load in the top left corner.
That's it! You may now access real-time exchange rates in an Excel spreadsheet. To get the most recent prices, reload this page.
Excel FX rates are nice things, but we're still not there yet because we'll need to adjust when we need new rate sets. Not to worry. We'll demonstrate how simple it is.
We need to know how to alter the request we make to the API in the future. So, it would be beneficial to break it out in detail before we start.
Part II
If you recall, in Step 2, we copied and pasted the URL from the documentation page. But only if we comprehend what the URL contains. Our request and the resulting data in the table cannot be changed. In the example below, we're asking for EURUSD, GBPUSD, and UK100:
"marketdata.tradermade.com/api/v1/live?
currency=EURUSD,GBPUSD,UK100&api_key=your-api-key"
This can be changed by doing the following actions:
"marketdata.tradermade.com/api/v1/live?
currency=USDJPY,EURGBP,EURJPY,USDCHF&api_key=your-api-key"
The data return will be consistent because USDJPY, EURGBP, EURJPY, and USDCHF have been added.
We know what needs to change now. Let's put it into action first. Click twice on the 'connection' on the right side of the spreadsheet and the 'live connection' under queries, as shown below.
When the Query window appears, select Advanced Editor under View.
Replace the previous currency string with the new one in the Advanced Editor, then click "Done"- as displayed below.
If you don't like the columns set up, you may also drag and drop them. Once finished, click the home button. Then select "close" and "save," and the spreadsheet will display the updated rates.
If you observe, columns have been moved, and rates have been changed. That's how easy it is.
You can also change requests directly from the spreadsheet, among other dynamic changes. But that is the subject of another lesson in this series. As we all know, learning new ideas may be overwhelming.
Additionally, historical forex data will be covered in the course that follows. We anticipate that this guide will help encourage non-programmers to use our data.
Contact us if you have any inquiries. We are also open to recommendations for tutorials.
TraderMade provides reliable and accurate Forex data via its Forex API. You can sign up for a free API key and start exploring real-time and historical data at your fingertips.
Also, go through the originally published tutorial on our website:
Import Currency Rates Into Excel With Power Query - Part 1Along with that, please read the following tutorials that help you get live and historical forex data into MS Excel and Google Sheets and plot graphs and charts for better market analysis:
Top comments (0)