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

My first Google Adsense cheque

My first Google Adsense cheque has arrived in the mail. A grand total of SGD $156.34 based on accumulated earnings from end-Jan 2008 when I started monetizing my blog to the end of Mar 2008 when the payout was calculated. Based on USD $116.30 earnings at that point, the USD/SGD currency exchange rate used would work out to 1.34428. It's funny how I have been watching and blogging on the falling dollar, and how now my very first Google cheque has been hit by this. But of course I have been expecting this, and I also expect that the US dollar has further to fall.

Vagaries of the forex markets aside, I'd suppose $116.30 for 2 months isn't too bad, seeing how I initially set out to recoup my hosting costs of $12.85 per month. Or to look at it another way, the couple of dollars or so every day now helps to cover my tea-break expenses (a cup of tea or coffee and a bun at the local coffeeshop). My tea-break costs are hedged, as are my petrol bills with the monthly dividends of a couple hundred dollars or so coming in from my energy income trusts.

Talking about coffee, once the cheque clears, I will be owing shooperman a cup of coffee (as promised earlier) - he was the one who suggested to me to try to make some money from my blog. Though it's a modest start, I can say it has done far better than my initial expectations. Thank you, readers of lowem.log for your support.

See also :

1. Google Adsense - first month report, and thanks for the clicks

(2008-04-27 16:55:39 SGT) [Musings] Permalink Comments [4]

NY paper reports food rationing appearing in US

This article belongs to the Global food crisis story arc.

kptm.com, newsroomamerica.com -> nysun.com :

Food shortages and rationing has been a third-world problem as of late, but recently, the phenomenon once thought unthinkable in the United States could start happening. The New York Sun newspaper is reporting that major retailers on both coasts are limiting customers' purchases of flour, rice and cooking oil. The Sun reports that a Costco Warehouse in California ran out of rice, frustrating shoppers. Most Costco members were only allowed to buy only one bag. One clerk reportedly dropped two sacks back on the stack after taking them from a customer who tried to buy more than the one bag limit.

Shoppers said the limits had been in place for a few days, and that rice supplies had been spotty for a few weeks. A New York Costco reportedly was not restricting rice, but is limiting oil and flour. Rumors floating on the Internet say that bakery owners bought up flour at warehouse stores after their commercial suppliers doubled their prices. Spiking food prices have led to riots in recent weeks in Haiti, Indonesia, and several African nations. India recently banned export of all but the highest quality rice, and Vietnam blocked the signing of a new contract for foreign rice sales.

- One of the last places you would expect a food shortage would be the U.S. but yet here we are. This reminds me of the Japan running out of butter case.

See also :

1. Thai rice price at new record high of US$1000 a tonne as Asian food crisis worsens
2. Global food crisis looms as Asia's rice bowl empties and world price soars
3. High food prices seen leading to strikes, protests in Asia

(2008-04-27 15:48:31 SGT) [Biz] Permalink Comments [1]

Zimbabwe inflation hits 165,000%

peakoil.com -> news.bbc.co.uk :

Zimbabwe's soaring inflation hit an annual rate of almost 165,000% in February, official figures show. Continuing shortages of food and fuel helped to push up inflation from January's rate of 100,000%. Government officials say the shortages make it hard to work out inflation with any degree of accuracy. About 80% of the country's population lives in poverty and it is estimated that three million people have left the country for a new life in South Africa. The economy has been in trouble for several years, with supplies of basic foodstuffs, cooking oil and petrol all running low. The central bank has introduced new banknotes to cope with the spiralling prices. Last month it issued a 10 million Zimbabwe dollar note.

- Note that these are official CPI figures from the Zimbabwe government. The actual inflation rate is probably much higher. For example, when the official figures were 100,000% back in Jan 2008, the inflation rate as estimated by the IMF was 150,000%. And for first-hand updates on the Zimbabwe situation, you can go to Cathy Buckle's site.

See also :

1. Zimbabwe: IMF estimates inflation at 150,000%
2. Zimbabwe : Not Reality TV
3. Zimbabwe on brink of financial collapse [1000% inflation back in Jan 2004]
4. Zimbabwe money loses three zeros
5. Peak Oil Crisis : Zimbabwe

(2008-04-27 15:33:33 SGT) [Biz] Permalink

Thai rice price at new record high of US$1000 a tonne as Asian food crisis worsens

businesstimes.com.sg :

Prices of benchmark Thai rice leapt more than 5% to a record high of above US$1,000 a tonne yesterday [24 Apr 2008], and traders in the world's top exporter warned of further gains if buyers Iran and Indonesia step into the market. 'If Iran buys rice from Thailand, Thai 100 per cent B grade white rice would hit US$1,300 a tonne,' one exporter said. This year rice finally joined in a global rally in food crop prices amid a surge in demand from major importers who feared that export restrictions by key suppliers like Vietnam might leave them short, especially with global stocks having halved since touching a record high in 2001.

Prices have now nearly trebled since around US$383 in early January, sparking food riots in African countries and Haiti and adding to growing fears that millions of the world's poor may soon struggle to feed themselves. Malaysia was to urge Thailand to fill its orders for 480,000 tonnes of rice this year at the two nations' prime ministers' meeting yesterday. Malaysia, which imports 650,000-700,000 tonnes of rice a year to satisfy annual needs of more than two million tonnes, announced plans at the weekend to open up large-scale rice farming and boost irrigation to meet growing demand and cut imports.

- Benchmark rice prices continue to set records. Meanwhile, rice futures traded on the Chicago exchange have also been setting all-time record highs, and the Asian food crisis continues to worsen :

a. Rice jumps above $25 in Chicago for first time on tight supply

Rice prices in Chicago advanced above $25 per 100 pounds for the first time on speculation more countries may introduce export curbs, reducing supplies needed to combat shortages and cool inflation. Brazil may restrict exports of rice to build domestic inventories amid tightening international supplies. Rough rice for July delivery rose to $25.01 per 100 pounds in after-hours electronic trading on the Chicago Board of Trade.

b. Rice climbs to record as World Bank warns of Thai export risk

Rice advanced to a record as World Bank officials said they are concerned Thailand, the largest exporter, may restrict shipments, worsening a global food crisis. "If a key exporter like this limits foreign sales, it would be very much like Saudi Arabia reducing oil exports," said James Adams, vice president of the bank's East Asia and Pacific department.

In Cambodia, 13-year-old Pin Oudam gets a free breakfast of rice, fish and yellow split peas every morning at his school in Kampong Speu, the poorest province. Next week he won't. The World Food Program cut off rice deliveries to 1,344 Cambodian schools last month after prices doubled and suppliers defaulted on contracts. Schools will run out of food by May 1, depriving about 450,000 children of meals, the WFP estimates.

- The comments about the impact of Thai rice export restrictions being equivalent to Saudi Arabia reducing oil supplies echoes my own comments earlier. And the part of the Cambodia school-boy getting his food aid cut off is particularly poignant. The Asian food crisis is worsening and here's another snippet on the developing situation :

c. Rice shortage in Philippines - adults going hungry to let children eat

Myrna Lacdao used to eat two meals a day. Now she eats one and gives the rest to her two grandchildren. Lacdao, 53, shares a 70-square-foot shack in Manila's San Roque shantytown with her husband, two adult children and grandchildren. After the price of rice rose 41% in the past year, only the youngsters get three meals a day. Increasing global demand for food, speculation in commodities and rising fuel prices intersect in San Roque, where 8,000 families live in wooden huts with roofs made of scrap metal and plastic.

See also :

1. Global food crisis looms as Asia's rice bowl empties and world price soars
2. High food prices seen leading to strikes, protests in Asia
3. Rice price jumps to record high after doubling since 2007
4. Rice prices are steaming, with many implications
5. Soaring rice prices hurting Asia's neediest nations

(2008-04-27 13:37:42 SGT) [Biz] 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