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!
PS: Besides using StockQuote, you can try the following two alternatives to import stock quotes and currency rates into Excel:
- Use Excel web queries. Follow this GUIDE.
- 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.
20 responses so far ↓
choozm // Tuesday, 26 December 2006, 12:47 pm at 12:47 pm |
I have changed the StockQuote link to a new file host mediafire.com as the old one is buggy.
StockQuote 2.0 released « The Mamak Stall Investor // Sunday, 14 January 2007, 11:28 pm at 11:28 pm |
[...] 2.0, you can now import unit trust fund prices from Fundsupermart.com into Excel. Go to the download page for [...]
genie47 // Wednesday, 7 March 2007, 9:27 pm at 9:27 pm |
Hi there, will there be a StockQuote that can be used in OOo? Would be helpful for some of us cheapos out here using OOo Suite Portable or U3 from our UFDs.
choozm // Thursday, 8 March 2007, 1:01 am at 1:01 am |
Hi genie47, does OOo mean OpenOffice? I think it should be possible. Actually much of the work of StockQuote is in the webservices server side (the StockQuoteWS folder). Once you get that running, then you can call the StockQuote webservices from any program that support webservices. So I think one just needs to write the macros in OOo to call the StockQuote webservices (can refer to the macro code in the StockQuoteDemo.xls file). I am not familiar with macros in OOo.
Hope this helps.
kevin // Sunday, 3 June 2007, 9:59 am at 9:59 am |
Have you tried Google spreadsheets? you can use =GoogleFinance(”AAPL”, “price”) functions, that pull about 50 valuation parameters…
choozm // Monday, 4 June 2007, 1:16 am at 1:16 am |
Thanks kevin. I do use Google spreadsheet sometimes. That’s cool!
I tried to extract prices of stocks listed in Singapore exchange but it gave error. I think the function works for US listed stocks only.
java_guru // Saturday, 8 December 2007, 9:31 pm at 9:31 pm |
been a reader a while. I wanted to try out your StockQuote 2.0 but I think mediafire has discontinued your file.
choozm // Sunday, 9 December 2007, 12:31 pm at 12:31 pm |
Hello java_guru, thanks for the note. I have fixed the link of StockQuote 2.0. Please try again. Cheers!
banyan // Sunday, 17 August 2008, 5:19 pm at 5:19 pm |
Hi Choozm, I got a VB compile error message “Can’t find project or library” after I click a button in the Exel sheet. The code seems to stop at “Private sc_StockQuote As SoapClient30″.
I have installed the Office Web Services Toolkit and .NET Framework 2.0. Do I miss something?
choozm // Monday, 18 August 2008, 12:59 am at 12:59 am |
Hi banyan,
Can you do the following check please?
When you open StockQuoteDemo.xls, go to Tools > Macro >Visual Basic Editor, you should see clsws_StockQuote as shown in this image: http://img172.imageshack.us/img172/9089/clswsstockquotenh5.png
After you start StockQuote, copy and paste this URL http://localhost:8080/StockQuoteServices.asmx
into your web browser and you should see three links to GetQuoteXXX.
banyan // Tuesday, 19 August 2008, 10:30 pm at 10:30 pm |
Hi Choozm, thanks for ur prompt reply. Yes, I can see the clsws_StockQuote class module. And the three links to retrieve prices are working.
banyan // Tuesday, 19 August 2008, 10:41 pm at 10:41 pm |
You can check out my screenshot at
banyan // Tuesday, 19 August 2008, 10:42 pm at 10:42 pm |
“http://profile.imageshack.us/user/banyan8/images/detail/#170/errorwv3.png”
choozm // Thursday, 21 August 2008, 9:20 pm at 9:20 pm |
Hi banyan,
Can you try this?
1. Open StockQuoteDemo.xls, go to Tools > Macro >Visual Basic Editor.
2. Inside Visual Basic Editor, go to Tools > References and you should see “Microsoft Soap Type Library 3.0″ is checked. If not, click Browse button, go to “C:\Program Files\Common Files\MSSoap\Binaries” and select MSSOAP30.dll to load it.
See this screenshot: http://tinyurl.com/6kmuog
banyan // Thursday, 21 August 2008, 9:50 pm at 9:50 pm |
It works after I downloaded and loaded the SOAP library. Thanks a lot!
StockQuote 2.1 released « The Mamak Stall Investor // Thursday, 4 September 2008, 3:10 pm at 3:10 pm |
[...] 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 [...]
Aaron // Tuesday, 21 October 2008, 11:23 pm at 11:23 pm |
i try doing this step Edit ’setenv.bat’ to point ‘APP_PATH’ to the ‘StockQuoteWS’ directory
(use full path enclosed in “”) but has no success. How do u point the path in the bat file
choozm // Wednesday, 22 October 2008, 12:06 am at 12:06 am |
I put the StockQuote folder in My Documents. This is what I have tried successfully in ’setenv.bat’:
set APP_PATH=”D:\Documents and Settings\abcd\My Documents\StockQuote\StockQuoteWS”
After editing ’setenv.bat’, run ’start.bat’ should bring up the server in a Command Prompt window and accessing http://localhost:8080/StockQuoteServices.asmx should show the StockQuote description page.
StockQuote 2.1.1 released « The Mamak Stall Investor // Saturday, 13 December 2008, 12:14 pm at 12:14 pm |
[...] Download this release at the download page. [...]
StockQuote 2.1.2 released « The Mamak Stall Investor // Sunday, 5 April 2009, 2:05 pm at 2:05 pm |
[...] Download this release at the download page. [...]