Sunday April 27, 2008

How to insert currency exchange rates into Google Spreadsheets

The financial formula lookups that Google Spreadsheets provides have proved very useful for managing the multiple portfolios that I have - besides running the family fund, I also manage my wife's account and I am also a fund manager for a private investment fund that specializes in commodities investments.

The only problem is that, although the Google finance lookup functions are able to provide many useful figures including stock prices, market capitalizations and so on, there is one critical component missing - the ability to do currency exchange rate conversions. I manage my accounts on a Singapore dollar (SGD) basis, while I invest in the US and Canada markets which are denominated in the US and Canadian dollars (USD, CAD) respectively. I found a couple of suggestions from online forums and actually used one of them for a while, which scraped the forex rates from a Google finance page, but then it stopped working recently. So I did some research on my own and came up with a better alternative that uses a Yahoo-based currency converter instead.

If you want to skip the explanation that follows, just insert the following formula, change CAD to the currency you want to convert from, and change SGD to the currency you want to convert to :

=Index(ImportHTML("http://finance.yahoo.com/q?s=CADSGD=X","table",1),8,2)

And there you are. Once you enter the formula, the exchange rate will be inserted into the cell, and it will be automatically updated whenever the forex markets are open (see screenshot above).

There is a disclaimer, however. This currency conversion formula is not real-time. There will be a delay of a few minutes (to monitor real-time rates, check out my livequotes page). Even so, this formula is already better than the Google Finance-based one I was using earlier - that one had a delay of 1-2 hours or more. To display the time of update, you can use this formula (as before, change the CAD and SGD symbols to whatever you wish to use) :

=Index(ImportHTML("http://finance.yahoo.com/q?s=CADSGD=X","table",1),2,2)

When the forex markets are open (which is most of the time, since forex exchanges are 24/5 markets, ie 24 hours a day, 5 working days a week), the time field should show something like "11:37AM ET", where ET is Eastern Time (i.e. New York's timezone). When the markets are closed, such as during weekends, this field should show the last trading day's date instead (such as the above, "4/25/2008").

Here's how it works : the ImportHTML function scrapes a web-page that you specify (in this case, the Yahoo Finance page for the specific currency pair). The "table" parameter tells the formula to use table-scanning mode, and the "1" refers to the index of the table to use, in case there are multiple tables on that page.

But if you use the ImportHTML function alone, you will get the entire table in an array (as in the bottom part of the screenshot above). So it's actually an array formula that returns the results in an array. The next thing to do is to isolate the cell that you want in that array. That's what the Index function does - the addressing is via row, then column, and the index starts from 1 and not 0, as C- or Java-based programmers might usually expect.

So, if you look at row 8, column 2 from the screenshot above, you will note that we are referring to the bid value for that currency pair. For most typical purposes, the bid value is what you will be using. And the time field (or last trading date when the markets are closed) is obtained from row 2, column 2.

This should be applicable until let's say a. Google decides to add a built-in real-time (or nearly so) currency conversion function, or b. Yahoo decides to change its currency conversion URL, URL format, or the format of the currency conversion page - even if Yahoo does so, with the explanation above, you should probably be able to amend the formula to get the figures back again.

Hope this is of help to you.

(2008-04-27 18:49:45 SGT) [Tech] Permalink

Most popular blog postings on lowem.log :

1. Singapore SIBOR interest rates fall to 1.5%, lowest since Dec 2004
2. 2009 Honda Global Small Hybrid details released : bigger than Jazz/Fit, smaller than Civic
3. Singapore : Inflation rate could push past 6% in Q1 2008
4. Singapore SIBOR rate fell to 1.25% in Apr 2008, lowest since Aug 2004
5. Fuel prices seen stoking Malaysia inflation in 2008
6. Singapore CPI inflation rate for May 2008 continues at 26-year high of 7.5%
7. Singapore CPI inflation hits 6.6% in Jan 2008 - a new 25-year record high
8. Singapore SIBOR rate falls to 1.31%, lowest since Nov 2004

Featured articles on lowem.log :

1. ABC Guide to Beating Inflation in Singapore and Elsewhere
2. Singapore inflation rate hits new 26-year high of 7.5% in Apr 2008
3. Hyper-inflation : early warning signs
4. Singapore hyperinflation warning signs #2 : Cooking oil price up 75%
5. Bread and inflation
6. Singapore M3 money supply growth vs STI stock index
7. Singapore SIBOR rate fell to 1.25% in Apr 2008, lowest since Aug 2004
8. Singapore : Inflation erodes away bank savings