How to Use Excel to Download Stock Quotes

I offer for sale a watch list of dividend stocks that I update on a monthly basis that lists buy prices. This list is not intended to be a money-maker for me in any way nor is it meant to be investment advice. I just feel that asking for a small fee is only fair due to the huge amount of time I put into tracking the companies on the list. That being said, I like to pour my heart out to provide insight into my methodologies and portfolio makeup out in the open on this blog. On Sundays, I have been writing posts that explain certain aspects of my portfolio and how I manage it. This Sunday will be no different. Today I want to talk about how I use Excel to help with the watch list and automate the downloading of stock prices.

Note: I use Excel 2002 on my home computer, so keep that in mind as I go through the steps to download stock prices. Your version may be slightly different, however in my experience it is not much of a difference from version to version.

Before we get started, I want to show you a snapshot of the Excel sheet I use:

Click to Enlarge

You will see that in Column L I have the Current Price heading – this is where I am going to be auto-populating JNJ’s stock price using Excel’s Web Query function. There is a list of about 33 companies in total that I watch right now and going out to the web every time to fill these in myself would be very time consuming and boring. Here are the steps that I go through to populate Column L with the stock price, using a simplified example with only two columns:

1. Open a spreadsheet and type the word “Company” in cell A1 and the phrase “Stock Price” in cell B1:

2. In cell A2, type in a ticker for the company you want to get a stock price for. In this example we will use Johnson & Johnson’s ticker – JNJ:

3. Click on cell B2 then go to Data > Import External Data > Import Data. A box like this should pop up:

Click to Enlarge

4. Click on “MSN MoneyCentral Investor Stock Quotes”

5. A box asking you where you want to put the data that you download will pop up. Select cell D2.

Click to Enlarge

6. The next box that will come up will ask you what stocks or funds you want to download data for. Use your mouse to select cell A2. Make sure you tick the box labeled “use this value/reference for future refreshes”.

Click to Enlarge

7. Once you click OK, Excel will go out to the web to get the quote for you. Once it is done it will bring up a bunch of data in a table. For this example we are focused on the stock price, which is labeled as “Last”:

Click to Enlarge

8. With your mouse, click on cell B2, type in the equal sign (=), and then click on the cell in the MSN data in the column named “Last”. You table will now look like this.

9. To refresh the data, right click on the MSN downloaded data and click “Refresh”. When you open the file again, it will refresh automatically.

Go ahead now and format the table however you want and play around with it. You can start to import multiple stock quotes in one table. One thing I do, is import the data to a separate worksheet so that it is hidden, and then link the stock quotes from that sheet. It works well and saves some time. I am trying to get a query that will allow me to import dividend yield, but am not having much luck getting something that consistently works. If I figure it out I will let you all know.

For more information on downloading data in Excel, check out these links:

Microsoft
AllFinancialMatters

(Photo Credit: lustfish)