Using Excel Web Queries to Retrieve Data

Spreadsheets allow us a great deal of flexibility when it comes to analyzing and manipulating financial data. However, a big hurdle is simply getting that data to manipulate and analyze.

While several Web sites, such as Yahoo! Finance and Google Finance, provide price and volume data that is easily downloadable to a spreadsheet, finding other data—and importing it into a spreadsheet—poses more of a challenge. Manually entering selected financial data for a few companies may not be overly time-consuming, but attempting to enter in detailed financial statement data for even a single company would be tedious and laborious.

...To continue reading this article you must be a Computerized Investing Subscriber.

Gain exclusive access to this article and all of the benefits and investment education a Computerized Investing subscription offers.

Log inAlready a CI subscriber? Login to read the rest of this article.

SubscribeA subscription to Computerized Investing includes a monthly email and access to the CI Website, all of which aim to benefit your investing skills with respect to computers and the Internet.

Discussion

Jan from CA posted over 4 years ago:

I am running Excel 2008 for the Mac, and Excel Web Queries does not appear to be available on the Data > Get External Data pulldown menu.

Wayne from IL posted over 3 years ago:

This article from Microsoft explains how to set up Web queries using Excel for Mac: http://support.microsoft.com/kb/274787. Hope it helps.

Thomas from NM posted over 3 years ago:

I have tried to replicate your Web Queries for Financial Statement Data, but, get a "Microsoft Office Excel could not open or read this query file. Either the file is damaged or the file format is not valid" error. I was successful in pulling the simple web query, but, I'm having trouble when altering the web query file with Notepad. I do notice the Reuters.com web query file data that you show in Notepad does not match exactly to what I get on the simple web query.

Thomas from NM posted over 3 years ago:

I was able to get this to work after several tries. I think it is important to point out that the custom queries work best if you start from scratch, in a new Excel spreadsheet and create an entire new query all the way through placing the variables in the query files.

Alan from PA posted over 2 years ago:

I cannot get the custom query to work in Excel. Once I put in the http://finance.yahoo.com/q?s=[“symbol”, “Enter ticker symbol”] my query doesn't work.

I think it has do to with the fact that the query I change looks different than what Wayne uses in his example.

Wayne's example:
http://finance.yahoo.com/q?s=goog

I'm seeing instead:
http://finance.yahoo.com/q?s=GOOG&ql=1

Not to mention my script editor says it sees two problems with my initial query.

Appreciate any help.

Jae Jun from WA posted over 2 years ago:

Ive created full stock valuation spreadsheets that automatically pull ten years of annual financial statements as well as quarterly statements.
You can download free and premium ones at http://www.oldschoolvalue.com

David Spoede from TX posted about 1 year ago:

Just found this article. Very well written and presented.

However, it does not seem to be an ideal solution. First, not all data on Yahoo Finance is in tables, so it's not available for a query. Secondly, the data that is available is typically in a group, requiring the download of more data than you want. My guess is that one can customize the query to be more specific, but I'll need to read the last half of the article above to determine if that's so.

Finally, the queries just don't work that well or consistently.

All that to get to my point, which is the question - are there any other alternatives or better sources of data, even sources that require a fee? Can one take the data out of the AAII Stock Pro, or whatever it's called, and automatically load it into a spreadsheet and automatically update the data?