Category Archives: Tools

StockQuote 2.1.3 released

This is a bug fix release for the price import error when importing price from Yahoo Finance, due to changes in its HTML code recently. No new feature was added.

Download this release at the download page.

StockQuote 2.1.2 released

This is a bug fix release for the price import error when importing price from Yahoo Finance, due to changes in its HTML code recently. No new feature was added.

Download this release at the download page.

StockQuote 2.1.1 released

This is a bug fix release for the price import error from Fundsupermart.com after it has changed its site layout. No new feature was added. Thanks to the feedback that reported this bug.

Download this release at the download page.

 

StockQuote 2.1 released

Since I have bought SGS bonds from Fundsupermart, I need to import SGS bond prices into my portfolio spreadsheet. So I updated StockQuote code base to add this functionality and released it as version 2.1 “Wisdom Tooth”.

Grab StockQuote 2.1 zip file at the download page. And also read the README.txt in the zip file on how to obtain ticker for SGS bond from Fundsupermart.

Looking forward to your feedback. Thanks.

ETF purchase cost

One of the frequently asked questions about ETF is the purchase cost. The question tries to find out the purchase cost as % of the purchase amount (just like the sales charge of unit trust funds), and also the minimum purchase amount required to keep the purchase cost below a target percentage.

The “How you can use exchange-traded funds” (PDF, 92KB) booklet from Vanguard’s “Learn About the Unique Benefits of Vanguard ETFs” web page has a table on page 7 that shows the calculation of the purchase cost, taking into account the share price, commission and spread. Using this table as a reference, I have made a spreadsheet for calculating the % purchase cost given a purchase amount, and the minimum purchase amount given a target % purchase cost. Four ETFs are included in the spreadsheet as examples. Each one of them is purchased from a different stock exchange (US, UK, SG, HK), using a Singapore-based broker with the lowest commission (to my knowledge).

Download the spreadsheet (XLS, 64KB).

The spreadsheet uses macros to calculate the minimum purchase amount. If you don’t feel comfortable of running macros on your PC, follow these steps to enable macro protection in Excel. If you choose to disable the macros, you will have to manually call the Tools > Goal Seek function in Excel to calculate minimum purchase amount.

Besides ETFs, you can also use the spreadsheet for shares. And if you use different broker, just adjust the commission formula to what your broker uses.

StockQuote 2.0 released

With StockQuote 2.0, you can now import unit trust fund prices from Fundsupermart.com into Excel. Go to the download page for details. Also refer to README.txt in the zip file on how to obtain ticker for unit trust funds in Fundsupermart.com.

By the way, I finished StockQuote 2.0 on the day I took the photo below, so I codenamed it “Genting” (see HISTORY.txt). Wanna guess where this place in the photo is?

CodenameGenting

Import stock quotes into Excel using web services

Excel is a very useful portfolio and investment tracking tool. You can customize it to your heart content with formulas and macros. However, I find it troublesome to enter stock price and currency rate manually from the web. I thought it would be nice to automate this using web services but I am not aware of websites that provide such services.

Well, I have found a web services sample code that grabs stock quotes from Yahoo Finance web page. With this, I can run the web services on my PC, and then use Excel macro to call the web services to extract stock quotes. Ya, I could have rewritten the sample code to Excel macro, so that Excel can call it without running the web services. But I am not good at writing Excel macro, and I am lazy to rewrite it, hehe.

So what do I have here?

I have packaged the web services sample code (SOURCE) with a simple web server (SOURCE), written some Excel macros to consume the web services, and here I have: StockQuote — my little program that imports stock quotes and currency rates into Excel, with a click of a button 😀

How to use StockQuote? Download the latest release below, extract the zip file and read the README.txt for the instruction to setup. After setting up, open ‘StockQuoteDemo.xls’ (see screenshot below) to test it. Have fun!

StockQuote Screenshot

PS: Besides using StockQuote, you can try the following two alternatives to import stock quotes and currency rates into Excel:

  1. Use Excel web queries. Follow this GUIDE.
  2. Use MSNStockQuote plug-in. Read this POST by Indexfundfan.

StockQuote Release History

  • StockQuote 1.0. Download(247KB .zip). Released Oct 24, 2006.
    • Import stock price from Yahoo Finance
  • StockQuote 2.0. Download(251KB .zip). Released Jan 14, 2007.
    • Added support for importing unit trust fund price from Fundsupermart.com
  • StockQuote 2.1. Download(251KB .zip). Released Sep 03, 2008.
    • Added support for importing SGS bond price from Fundsupermart.com
  • StockQuote 2.1.1. Download(251KB .zip). Released Dec 09, 2008.
    • Fixed price import error after Fundsupermart.com changed site layout.
  • StockQuote 2.1.2. Download(251KB .zip). Released Apr 05, 2009.
    • Fixed price import error after Yahoo Finance changed site layout.
  • StockQuote 2.1.3. Download(254KB .zip). Released Jun 20, 2012.
    • Fixed price import error after Yahoo Finance changed site layout.