Get live and historical Forex and CFD Rates in Google Sheets for Free

This tutorial will take you through some simple steps to obtain live and historical exchange rates in Google Sheets in real-time. You'll be amazed by the speed and accuracy of data previously only accessible to banks and big financial institutions.

So, let’s get started!

Step 1: Sign-Up for a Free API Key

Initially, sign-up to get your Free API Key. Signing up is absolutely free and it takes less than a minute!

Step 2: Copy the Spreadsheet

Copy the spreadsheet, though this is not essential it makes it easy to copy formulas and run your spreadsheet at the outset.

Once you have the spreadsheet:

1) Duplicate the Google sheet by clicking the ‘File’ Tab, followed by the ‘Make a Copy’ tab.

2) Save your Google sheet with a desired filename — Let's say ‘livefxrates’ (Marked in pink)

3) Enter the API Key you obtained by signing up in the A2 cell marked in red in the image below:

Also, watch our Forex Google Sheet Video Tutorial

Once you have copied the spreadsheet:

Step 3 : Copy the 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. 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:

Overwrite the function with the code you just copied in the previous step. And your code.gs file should look like the below image.

Save the project by clicking the ‘Save Project’ button marked in red as shown above.

This will enable the extension to fetch the forex rates we need.

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. You can also copy this formula in any spreadsheet provided you have added the code in the code.gs file. Let us take a quick look at these formulae:

1) Historical Rate

getHistoricalRate(B4,to_text(C4), A2): the first parameter is the currency code, the second is the date (we convert it to text) and the third is the API key.

2) Live Rate

getLiveRate(B7, A2): the first parameter is the currency code and the second is the API key.

3) Currency Conversion

getConversionRate(B10, C10, A2): the first parameter is the 3 digit currency code from (example EUR), the second is is the 3 currency code to (example USD) and the third is the API key.

4) Time-Series Data

getTimeseries(B13,to_text(C13),to_text(D13),E13,A2): the first parameter is the currency code, the second is the start date (we convert it to text), the third is end date, fourth is frequency (daily, hourly) and fift is the API key.

Copy-paste your unique API Key into the A2 cell to get the results leveraging the pre-populated formulae.

In our example, you can see:

1) The historical price of the 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.

You can alter the spreadsheet or start a new one as you wish (check the code.gs file exists). The spreadsheet you copied will help you understand the formula and apply it as you need.

Hope you liked this tutorial, please let us know how you got along and follow and clap our work.