How To Get Forex Data in Google Sheets?
08 August 2023
In this tutorial, we will take you through some simple steps to use our Google Sheets Plug-in to obtain real-time and historical exchange rate data. You will require an API Key to get live and previous rates on Google Sheets.
So, let’s get started!
Step 1: Sign-Up for Free to Get Your API Key
Initially, sign-up to get your API Key. Signing up is absolutely free and it takes less than a minute!
Please follow the steps explained in the following tutorial to sign-up and get a unique API Key exclusively made for you:
Step 2: Copy the Spreadsheet
Copy the spreadsheet we have designed for easy implementation.
Please open the Forex Google Sheet link. You can see a Google Sheet pre-populated with information as shown below:
1) Duplicate this Google sheet by clicking the ‘File’ Tab, followed by the ‘Make a Copy’ tab.
2) Save your Google sheet with a desired filename - I have saved it as ‘livefxrates.’ (Marked in pink)
3) Enter your API Key in the A2 cell marked in red in the image below:
Step 3 (A): Copy the Code
This step is only required if your app script doesn't have code.gs file populated with code.
Copy the code from this link: https://tradermade.com/google-sheets/code.js
You require this code to add essential functionality to your Google Sheets.
Step 3 (B): Get the Extension
Click the ‘Extensions’ tab in the top menu, and choose ‘App Script’ from the dropdown, as shown in the below image:
You will see the App script page like this:
1) Click the ‘Edit’ option in the left side menu (marked in green).
2) Copy-paste the code you got through the link in the previous step.
3) Save the project by clicking the ‘Save Project’ button marked in red.
These steps add the extension to enable your Google Sheets to fetch the requested information from our server.
About Formulae Pre-populated on the Google Sheet Plug-in
The Currency Rate Google Sheet Plug-in simplifies currency conversion calculations and API integration. As you duplicate the plug-in sheet, you can see various formulae pre-populated in the ‘F’ column. Let us take a quick look at these formulae:
1) Historical Rate
This formula helps you get the closing exchange rate for a currency pair on a specific date. Enter the desired currency pair and the date, and you will get the historical rate.
2) Live Rate
This formula provides the latest exchange rate for a desired currency pair or CFD instrument. Enter the currency and get the live rate.
3) Currency Conversion
This formula is a go-to solution to help you get currency conversion in a snap. Enter the desired base and quote currency to get the currency conversion rate.
4) Time-Series Data
This formula provides a comprehensive historical data set for analysis. Enter the desired currency pair, start date, end date, and the desired interval, like hourly or daily, and get the historical Open-High-Low-Close (OHLC) rates.
Copy-paste your unique API Key into the A2 cell to get the results leveraging the pre-populated formulae.
Step 4: Request Data and Get Results
You can request data by entering the desired parameters, like Currency, Start Date, End Date, and Interval, as shown in the image in step 3 (marked in blue).
As you enter your API Key in the A2 cell, request data by entering parameters, you instantly get the real-time and historical Forex data in the subsequent cell in the ‘F’ column..
Here is an example of the results you get by entering the required fields:
In this example, you can see:
1) The historical price of Euro/US Dollar (EURUSD) on a desired date.
2) Real-time rate of UK100 (FTSE 100 Stock Index)
3) The currency conversion rate of the US Dollar (USD) to the Euro (EUR)
4) Time-series data for Euro/US Dollar (EURUSD) on a daily interval between given dates.
And that’s it! You have successfully obtained real-time and historical Forex and CFD Data into Google Sheets.
Contact our market data experts via live chat or email for your technical queries. Our experts respond to your questions promptly.