No. of Recommendations: 17
This works in Excel 2002 and XP:

1. Create a new workbook

2. On Sheet1, create a cell with the ticker you want to import

3. On Sheet2, put your cursor in cell A1

4. Go to the Data menu, select "Import External Data" and "New Web Query"

5. Using the browser that pops up, navigate to your favorite data provider. I personally use the consolidated data from MSN Money (and before making an investment, look at the unconsolidated data from SEC filings to just make sure I know what I'm seeing)

6. Click the arrows that Excel displays on the web page to indicate what data you want to import into Excel

7. You can choose to put this query, as is, into your excel spreadsheet now using the "Import" button, or you can follow the next steps to make it work in a more generic case. To stop now and get the numbers you've selected for just this company, use "Import." If you're going to do this a lot, do not select "Import" and go to the next step.

8. Click on the "Save Query" icon in the upper right hand corner. Pick a file name for your query that you can remember.

9. Click "Cancel" rather than "Import" to get rid of this window.

10. Open the file you just saved (ended in .iqy) with a text editor.

11. Go to the line that contains the URL that you just visited. It might look something like this:

Find the ticker symbol you were browsing and replace it with the string


like this["Ticker"]

12. Save the query file.

13. In Excel, go to Data menu, Import External Data, and select "Import Data" this time.

14. In the file dialog that pops up, find your saved query that you just edited.

15. When you select open, you'll be prompted by Excel on where you want the data from the query to land. Then, you'll get a pop-up window that asks you to enter the "Ticker" for the query. You can enter any ticker in the query and get the data for it from the web site.

16. Now, you can right click on cell A1, where you started the query, and select "Parameters." That dialog will allow you to specify where to get the Ticker parameter, either by prompting you, or from another cell in another worksheet, like Sheet1!A1, and you can even monitor for changes to that cell to automatically update this query.

This is an extremely powerful feature. I use it, along with free data from MSN Money, Yahoo Historical Quotes, Earnings estimates, etc. to query substantial stock data into a spreadsheet and automatically calculate many ratios of interest to me.

You can't pull data from Anumati with this feature. The style of web queries they use does not allow you to edit the Excel query so that you can specify arguments to the web query through the URL. (In tech speak, they use POST methods for generating their data, and not the GET method).

You'll spend some time making it work the first time. But once you've got it, it's a real time-saver.

Search on the web for Value Sheet if you want to find someone who's done this for you that's commercially supported. They have their own commercial data provider, so you'll pay them fees and they'll pay you fees, but you'll get a canned spreadsheet that can be updated for any company you want. I chose not to go this route because I wanted to calculate the numbers myself, and use my own definitions for cash flow, owner earnings, etc. that are more in line with how the Fool does business.


Print the post  


What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.