Historical stock quote Price using Web Query and MS Excel 2003

“Be fearful when others are greedy, and be greedy when others are fearful”. That’s how the Oracle of Omaha, Warren Buffett makes money. In the midst of economic crisis, most of you who maintain a portfolio or want to take advantage of the drop in points by buying stocks, there might be a question at the back of your mind: “What was the stock price of WXYZ on 25th October 2005?”. There are many websites which you can use to look up historical stock quotes. The best of all is the Yahoo!Finance historical stock quote search, found at their stock research center page.

I was wondering whether I can build this in Xcelsius. I was looking for an easy and quick solution. But I coudn’t find one. Well, if Xcelsius supported Excel Web Queries then it can definitely be done. However, unfortunately it does not. Hence I decided to build this using my first love, Microsoft Excel.

Usage: If you need to lookup quote price for a specific date, say June 2, 1997, enter June 1, 1997 as the start date, and June 3, 1997 as the end date. Then chose “Daily” prices. After clicking “Get Prices,” you should end up with opening, high, low and close, along with the adjusted close price for those dates.

Design: This model was built using Excel Web Query feature. Its very simple to use. All you need to find is a well organized website which displays data in tabular form. Yahoo Finance was perfect for this. I used simple components from the Form Toolbar. Radio buttons and the regular button components were used to build interactivity. Here is a complete article on “Pull data into Microsoft Excel with Web queries”.

  • Pingback: Biggest Halloween » Blog Archive » Historical stock quote Price using Web Query and MS Excel 2003()

  • Pingback: Real Time Fuel Gauge Dashboard using Excel Web Query | MyXcelsius()

  • Andrew Ribner

    Kalyan –

    After a great deal of searching I ran across you article “Historical stock quote Price using Web Query and MS Excel 2003” along with its accompanying download. I was very happy to find it, since it was exactly what I am searching for. It downloaded just fine and opened up in “compatibility mode” in Excel 2007, which is the version of Excel that I have. When I tried to use it, however, it didn’t quite work, even though I permissioned the macro and data-connection security alerts that popped up when the program opened. When I hit “get prices” after inputting the dates and ticker symbol, the program jumps to cell N4, with columns N through U highlighted, but nothing appears in any of the cells. Boy, I’ll tell ‘ya, if you can turn me on to a version of your little program that would work for me I would be soooooo thankful! t would do exactly what I have been spending WAY too much time trying to accomplish!

  • Lyra

    I managed to get a web query to download data from Yahoo Finance, but I found a much simpler to use option. The spreadsheet at http://optimizeyourportfolio.blogspot.com/2011/05/importing-historical-stock-prices-from.html uses VBA, you just need to specify a ticker symbol and a couple of dates.

  • Jack

    Hi,

    I like this spreadsheet template and this is exactly what I am looking however there are some fields which I need to do little formatting. However, I am wondering if you could help in changing the source of information. I want pull the data from yahoo finance and I want to give the link to spreadsheet. Whenever I click “Get Prices” I want to get the data for a particular date and month. Please help me out!

    Thanks!
    Best regards,
    Jack

  • Simulationconsultant

    You could use this spreadsheet instead, it uses VBA (not web queries):
    http://investexcel.net/218/importing-historical-stock-prices-from-yahoo-into-excel/