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.
- StockQuote 2.1.3. Download(254KB .zip). Released Jun 20, 2012.
- Fixed price import error after Yahoo Finance changed site layout.
The Mamak Stall Investor 
I have changed the StockQuote link to a new file host mediafire.com as the old one is buggy.
Pingback: StockQuote 2.0 released « The Mamak Stall Investor
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.
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.
Have you tried Google spreadsheets? you can use =GoogleFinance(“AAPL”, “price”) functions, that pull about 50 valuation parameters…
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. 😦
been a reader a while. I wanted to try out your StockQuote 2.0 but I think mediafire has discontinued your file.
Hello java_guru, thanks for the note. I have fixed the link of StockQuote 2.0. Please try again. Cheers!
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?
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.
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.
You can check out my screenshot at
“http://profile.imageshack.us/user/banyan8/images/detail/#170/errorwv3.png”
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
It works after I downloaded and loaded the SOAP library. Thanks a lot!
Pingback: StockQuote 2.1 released « The Mamak Stall Investor
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
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.
Pingback: StockQuote 2.1.1 released « The Mamak Stall Investor
Pingback: StockQuote 2.1.2 released « The Mamak Stall Investor
run-time error ‘-2147024809 (80070057)’
blah…. blah.. blah…. any help?
How do you get that error? Sorry, I am not sure what that error means.
hey sorry to bring up something so old but what can i do if i don’t have MSSOAP30.dll?
Hi, please see the comment above , dated Thursday, 21 August 2008, 9:20 pm.
I couldn’t get this to work, so I used the spreadsheet at http://investexcel.net/218/importing-historical-stock-prices-from-yahoo-into-excel/ instead
Pingback: StockQuote 2.1.3 released | The Mamak Stall Investor
I’m importing stock data using LordUI tool. See http://www.lordui.com/en/stock-data-import for more 😉