Xe com php to idr

Small and medium-sized companies usually use Google Sheets to monitor their income and costs. Quite often, their cash flow includes different currencies, while their tax reporting must be submitted in their national currency. This means that you have to take into account the currency rate when calculating income and costs. Regular users simply Google “currency converter” to solve this task.

However, it would be quite useful to automate this flow and embed the conversion feature into your spreadsheet. Read on to find out how you can do this!

Track currency rates in Google Sheets with the native function – GOOGLEFINANCE

GOOGLEFINANCE is the Google Sheets function to import real-time data on financial markets. The function takes this data from Google Finance, a web service that provides information on local and world market trends including daily stock prices, currency rates, and so on. Read our GOOGLEFINANCE function advanced tutorial to learn more.

For our currency exchange rate monitor, we can use a simple GOOGLEFINANCE formula syntax without any optional parameters:

=GOOGLEFINANCE("ticker-symbol")

• ticker-symbol, in our case, is an abbreviation to represent currency pairs, for example, EURUSD, USDCAD, etc. 

Note: EURUSD shows the Euro to USD exchange rate. If you need the USD to Euro exchange rate, use the reverse ticker-symbol – USDEUR.

Currency exchange rate monitor with GOOGLEFINANCE

Now, let’s use the function in practice. We will track the exchange rate of following currencies to USD:

• EUR (Euro)
• AUD (Australian dollar)
• CAD (Canadian dollar)
• GBP (Pound sterling)
• IDR (Indonesian rupiah)
• INR (Indian rupee)
• PHP (Philippine peso)
• BRL (Brazilian real)
• PLN (Polish złoty)
• JPY (Japanese yen)
• CNY (Chinese Yuan Renminbi)

To use the power of the GOOGLEFINANCE function, we need to have a ticker-symbol consisting of two currency codes – Our conversion currency and USD. The CONCAT function nested with ARRAYFORMULA will help us handle this:

=arrayformula(if(len(B2:B)=0,,concat(B2:B,"USD")))

Read our blog post to learn more about how to merge data in Google Sheets.

Even better, we can remove the Ticker column and simply insert the CONCAT formula in the GOOGLEFINANCE formula as follows:

=GoogleFinance(concat("USD",B2:B))

It would seem that we could simply combine ARRAYFORMULA and this GOOGLEFINANCE formula to expand the results. However, GOOGLEFINANCE as an argument for ARRAYFORMULA doesn’t work 🙁 So, you’ll have simply to drag the formula down or use the Ctrl+Enter Google Sheets shortcut. 

It’s not convenient, since every time you add a new row at any point and populate it with a new ticker, you’ll have to manually copy and paste the GOOGLEFINANCE formula. 

Let’s modify our GOOGLEFINANCE formula to get the exchange rate to USD. You need to swap “USD” and B2:B in the CONCAT formula piece, and drag the formula again:

=GoogleFinance(concat(B2:B,"USD"))

That’s it! That’s it! The currency rates will be updated on change, but you can set it to refresh every minute. Go to File => Spreadsheet settings => Calculation and choose “On change and every minute“. Save settings to enable the every-minute refresh.

You can copy this exchange rate tracker template to your spreadsheet and customize it for your needs.  

GOOGLEFINANCE #N/A error

Though GOOGLEFINANCE is a native Google Sheets function, we can’t claim that it is highly reliable. On StackOverflow and Google community, you can find recent comments that GOOGLEFINANCE formulas have stopped working. The #N/A error message usually returns the following:

When evaluating Google Finance, the query for symbol "****" returned no data.

This is typically a GOOGLEFINANCE internal error, so neither adding IFERROR to your formula nor changing the refresh interval will help. What’s the solution?

The GOOGLEFINANCE function is best when you are using a smaller number of ticker symbols (let’s say, less than 25). For a reliable import of bigger sets of stock data, you should use an API service.

How you can import currency rates data from financial services into Google Sheets via APIs

Usually, developers use APIs to integrate some capabilities, such as currency exchange, into their apps. We’re going to explain how you can use APIs to import currency data into Google Sheets without any coding.

Connect JSON API without coding

As a rule, financial services use the JSON format to transfer data via APIs. We will use the JSON importer to fetch and convert JSON data into Google Sheets. It is a source supported by Coupler.io, a product for importing data into Google Sheets, Excel, or BigQuery from different sources.

To set up an importer, sign up to Coupler.io, click Add new importer and enter the name of your importer in the Title field. Next, you’ll need to complete the three steps: source, destination, and schedule:

Source

• Choose JSON as a source app from the list.
• Insert the API endpoint to import data from in the JSON URL field. You will find the endpoint in the API documentation of the financial service you use. For example, 

https://xecdapi.xe.com/v1/currencies.json/?iso=USD,EUR,CAD

1. Click Continue to seeAdvanced settings to expand the optional fields:

• Click Continue to expand the optional fields where you can specify additional parameters, such as HTTP method, request headers, URL query string, and others. In most cases, you will need information about the request headers and query options that you can find in the API documentation of the financial service you use. For example, here is how the Authorization header may look:

Authorization: Basic enJlY29yZHM0OTEamFiZjk2MWZtNnA4ZjY=

Destination

• Select a file on your Google Drive to transfer data to. Select an existing sheet, or enter a name to create a new one. 
• Optionally, you can 
  • Change the first cell where to import your data range, specify your value in the Cell address field. The A1 cell is set by default. 
  • Change the import mode from replace to append 
  • Add a column specifying the date of the last data refresh.

Click Save and Run to load your data to Google Sheets on demand. If you need to automate this pipeline, enable the Automatic data refresh and configure the schedule settings.

Let’s check out how it works on a real-life example.

Import currency exchange rate data into Google Sheets via API

We’ve reviewed top 10 currency and forex APIs by Yasu and picked two options to test.

Foreign Exchange Rates API

Exchange rates API is a free service for current and historical foreign exchange rates published by the European Central Bank. They provide a free plan with up to 250 requests per month. Once you sign up, you’ll get an API Access Key required to retrieve information from the API.

To import data using the JSON importer, you’ll need to specify the JSON URL and URL query string. For example, let’s load the most recent exchange rate data:

JSON URL

https://api.exchangeratesapi.io/v1/latest

URL query parameters: 

access_key: {your-access-key}

Here is what the configured importer looks like:

Click Save & Import and welcome your data into the spreadsheet. 

That was easy, but not exactly what we needed. The API retrieved many currency rates that are quoted against the Euro. Now, let’s request specific currency rate quotes against the USD. For this, add the following string to the URL query parameters:

base: USD 
symbols: AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://api.exchangeratesapi.io/latest?base=USD&symbols=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN

If you enable the Automatic data refresh, Coupler.io will automatically request currency exchange rates on the schedule you specified. 

XE Currency Data API

Now, let’s check out how the JSON Client works with a paid solution. We chose the XE Currency Data API since it provides a free trial and endpoints for converting one currency to another. After signing up, we downloaded the technical documentation explaining the API endpoints. The main difference between XE and Exchange Rates API is that all requests to the XE API must be authenticated via HTTP Basic Access Authentication. 

This means that you’ll have to enter the Authorization header in the “HTTP headers” field in the following format:

Authorization: Basic {credentials}

{credentials} is the Base64 encoding of account ID and API key joined by a single colon “:“. For example, if your XE account ID is example491919043 and your account API Key is jabf961f4u6p8k9usfmfu6amf6, you’ll get the following:

example491919043:jabf961f4u6p8k9usfmfu6amf6

Encode this string to Base64 using a dedicated tool or the formula that we introduced in the blog post about the CONCATENATE Google Sheets function:

ZXhhbXBsZTQ5MTkxOTA0MzpqYWJmOTYxZjR1NnA4azl1c2ZtZnU2YW1mNg==

Now, let’s check out the JSON Client parameters required to import currency rates data from XE:

• JSON URL:

https://xecdapi.xe.com/v1/convert_from.json/?

• HTTP Method: GET
• Request Headers:

Authorization: Basic {credentials}

• URL query parameters: 

from: USD
to: AUD, BRL, CAD, CNY, EUR, GBP, IDR, INR, JPY, PHP, PLN
amount: 1

Here is the result:

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://xecdapi.xe.com/v1/convert_from.json/?from=USD&to=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN&amount=1

If you want to include a column with the inverse rate (a quote for which the base and target currencies are switched), add inverse: true to the URL Query parameters field:

from: USD
to: AUD, BRL, CAD, CNY, EUR, GBP, IDR, INR, JPY, PHP, PLN
amount: 1
inverse: true

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://xecdapi.xe.com/v1/convert_from.json/?from=USD&to=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN&amount=1&inverse=true

When you have the data imported into your spreadsheet, you can reference it for your calculations using VLOOKUP, QUERY or FILTER. Check out our blog post dedicated to each of these Google Sheets functions. 

Why you should use a custom currency tracker in Google Sheets instead of an exchange rate app 

The best thing about Google Sheets is that you can automate much of your workflow using different functions or add-ons. This means that you spend less of your time on recurring manual work, and have more time available for more valuable tasks. Some exchange rate apps are excellent, but they cannot be embedded into your spreadsheet. So, you’ll have to either manually transfer data from an app to Google Sheets or set up some complex integrations. The options introduced in this blog post are time-efficient and easy to implement. Between GOOGLEFINANCE and Coupler.io, the solution you choose is up to you. Good luck!

Back to Blog