Using Excel’s native Web Query capability to extract data from Internet pages, you can automatically import market data from free sites such as Yahoo! Finance, Google, MSN, AOL, etc. This works fine for delayed and end of day data.
The benefits of web queries are zero cost and the availability of a broad range of data elements, including price, volume open interest, and fundamental data such as splits, balance sheet, cash flows, analyst estimates, etc.
The downsides to web queries are understanding the syntax required for each data provider, capturing all the data in a single query, and the inability to easily organize the data in Excel. It can be challenging to get a simple array of price-volume history with all the dates lined up properly and no extraneous data mixed in. Price data may not be split-adjusted. Web queries can also cause Excel files to become bloated and slow.
The best solution using web queries is to buy pre-built software that does all the work for you. These solutions capture, cleanse, and output pre-formatted data for Excel and other software platforms. They are very low cost and easy to use, and save a ton of money over the long term compared to buying a data subscription.
If you want to build your own web query by hand, here is an example below. This one grabs daily prices for YHOO from Yahoo!Finance, but a similar procedure can be used for Google, AOL, MSN, etc. You can also use the same procedure to import other data into Excel such as cash flows, analyst ratings, etc.
1) Select the Excel spreadsheet cell where you want your price-volume data. In Excel 2007, click Data > From Web. This opens the New Web Query window.
New Web Query window
Excel Web Price Query
2) Go to http://finance.yahoo.com/q/hp?s=YHOO&a=03&b=12&c=1996&d=04&e=3&f=2010&g=d which is the Historical Prices page for YHOO.
3) Copy the URL at the top of the Yahoo page.
4) Return to the New Web Query window and paste the URL in the Address field at the top. This should fetch the Yahoo! Finance historical prices page for YHOO using the date parameters you gave it.
5) Select the PRICES table in the main window by clicking the yellow arrow so it becomes a green check box.
6) Click the Import button. This will automatically import about 65 rows of data into Excel.
7) If you want more data, select a cell below the bottom of the first data import and create a new web query. Return to Yahoo! Finance Historical Prices page above and click the Next link at the bottom. When the data appears, copy the new URL from the top and paste it into the new Excel web query. Repeat until you have all the data you need.
8) Clean up any errant data in the Excel target spreadsheet.
