${log.root}/lowem.log
Inflation, Investing and Everything


All | Energy | Java | Tech | Musings | Env | Biz

AddThis Feed Button
20080427 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

Comments:

Post a Comment:

Comments are closed for this entry.




Most popular blog postings on lowem.log :

1. Singapore MRT rail network length to double by 2020
2. 2010 Nissan Leaf electric car specifications : 107hp, 24KWh lithium-ion batteries, 100-mile range
3. Live spot gold price quotes chart on COMEX
4. 2010 Toyota Prius specifications released : 50 mpg, 1.8L, 134hp, Ni-MH, solar roof option
5. AVG Anti-Virus Free Edition 2011 direct download link
6. Real-time live gold and silver price quotes chart on COMEX
7. Singapore electric vehicles : Government agencies EMA and LTA to study EV introduction
8. Book review : Shut Down by William Flynn

Featured articles on lowem.log :

1. Book review : Shut Down by William Flynn
2. Singapore electric cars testing starts with 9 electric vehicles
3. Honda, GS Yuasa JV to make lithium-ion batteries for 2010/2011 Honda Civic Hybrid
4. 2010 Honda Civic Hybrid preliminary specifications released
5. 2010 Honda CR-Z hybrid, 2010 Honda Fit/Jazz hybrid models confirmed
6. 2010 Toyota Prius specifications released : 50 mpg, 1.8L, 134hp, Ni-MH, solar roof option
7. NYMEX crude oil recovers from $32.40 low after 2.2 mbpd OPEC production cut announced
8. Singapore : Nuclear power not ruled out



search
sponsored links



bookmarks

about
my profile
contact me

blogroll
biow/ken/wenn
reviewem
afternote

sites
photo gallery
wiki

quotes
live oil prices
live gold prices

charts
live forex rates
live oil chart
live brent crude chart
live gold chart
live silver chart

historical
crude oil chart
gold chart
silver chart


navigation
decals

Click for Singapore, Singapore Forecast





rss feed for lowem.log

Get Firefox!

powered by
hosted by