Monitor market data price using Goggle Docs spreadsheet
February 17, 2011 Leave a comment
I have an assignment to do trading in future commodities market. I’d like to monitor latest price data from different market, different provider on daily basis. Another requirement is the data should be able to be shared with my colleagues. So I decided to develop the tool that can do this automatically.
First I can develop the tool that I need using excel, then develop a VBA script to grab market data from internet. But I can’t share the information to others. Second option is to develop it using Google Docs spreadsheet. This approach is preferable since it would be more flexible, but you have to be online to see the data.
Next thing to decide is what market data will be used. First thing that come in my mind is Yahoo Finance, I like it since they have wider sources and have been there for quite long time. Other source is Google Finance data, but most of the data provided are in US market. Both sources are free, but there are 20 minutes delay which acceptable for me.
I use spreadsheet at Google Docs, create a new file. Type in the quotes name in first column. Next columns will display information needed. I created a formula using GoogleFinance() function to grab the information.
For example to grab the last price of US Brent Oil (BNO) just use this funciton
The formula will read the Last Price for NYSE: BNO information. I created column A to type quotes name. Column B to H to display information needed. I also add format, select Format – Change color with rules to automatically paint red color on the change price if the value is negative. Below is sample screen of the result:
I share this sheet to my colleagues. Email them the html link provided. They don’t have to have a google account to see it. You can add more functionalities to provide more information, for example what price did you buy, how much profit do you make, or a signal with certain criteria to tell you whether to buy or sell.
Hope you find it usefull. Let me know what you think.