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.

33 responses to “Import stock quotes into Excel using web services

  1. I have changed the StockQuote link to a new file host mediafire.com as the old one is buggy.

  2. Pingback: StockQuote 2.0 released « The Mamak Stall Investor

  3. 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.

  4. 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.

  5. Have you tried Google spreadsheets? you can use =GoogleFinance(“AAPL”, “price”) functions, that pull about 50 valuation parameters…

  6. 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. 😦

  7. been a reader a while. I wanted to try out your StockQuote 2.0 but I think mediafire has discontinued your file.

  8. Hello java_guru, thanks for the note. I have fixed the link of StockQuote 2.0. Please try again. Cheers!

  9. 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?

  10. 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.

  11. 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.

  12. You can check out my screenshot at

  13. “http://profile.imageshack.us/user/banyan8/images/detail/#170/errorwv3.png”

  14. 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

  15. It works after I downloaded and loaded the SOAP library. Thanks a lot!

  16. Pingback: StockQuote 2.1 released « The Mamak Stall Investor

  17. 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

  18. 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.

  19. Pingback: StockQuote 2.1.1 released « The Mamak Stall Investor

  20. Pingback: StockQuote 2.1.2 released « The Mamak Stall Investor

  21. run-time error ‘-2147024809 (80070057)’

    blah…. blah.. blah…. any help?

  22. How do you get that error? Sorry, I am not sure what that error means.

  23. hey sorry to bring up something so old but what can i do if i don’t have MSSOAP30.dll?

  24. Pingback: StockQuote 2.1.3 released | The Mamak Stall Investor

  25. I’m importing stock data using LordUI tool. See http://www.lordui.com/en/stock-data-import for more 😉

  26. I was curious if you ever thought of changing the structure of
    your blog? Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people coyld connect with
    it better. Youve ggot an awful lot of text for only having one or 2 images.
    Maybe youu could space it out better?

  27. адвокат арбитраж москва – ведение уголовных дел адвокатом , юристы москва .

  28. Hurrah! After all I got a web site from where I know how to
    really get useful information regarding my study and knowledge.plans to shed

  29. One fan also went so far since to produce a GoFundMe page to help raise money to
    pay Meek to deliver a response song to Drizzy ASAP.

  30. Simply a smiling visitant here to share the love (:, btw great style and design .

  31. Thanks a lot for sharing this with all of us you really know what you are talking about! Bookmarked. Please also visit my web site =). We could have a link exchange agreement between us!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s