How to Use Excel to Download Stock Quotes

Written by The Dividend Guy on December 16, 2007

Spreadsheet

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:

Excel Quote DownloadClick 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:

Figure 1

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:

Figure 2

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

Figure 3Click 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.

Figure 4Click 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”.

Figure 5Click 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”:

Figure 6Click 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.

Figure 7

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)


Recommended: ING DIRECT makes $aving money simple! Open your account online today and start earning 3.00% variable APY. No Fees and No Minimums!

del.icio.us| Digg| Furl| Reddit| Google| StumbleUpon| Technorati| Help|

3 Comments so far

  1. dunamis December 16, 2007 11:19 am

    Great entry - thank you so much for sharing your skills. Paying it forward - love it!

  2. Dividends4Life December 16, 2007 1:34 pm

    This was really interesting. I wasn’t aware that this functionality was available within Excel. What I have done is to construct a MSN portfolio with all the data elements I need for my analysis. I will then copy it to a clipboard the copy it to a input tab in one of my two linked spreadsheets. This in effect becomes my database that I query from other sheets using vlookup and such. I will have to experiment with your version to see it it can be expanded to pull all the fields that I use.

    Thanks for sharing!

    Best Wishes,
    D4L

  3. Dividend Tree April 3, 2008 7:51 pm

    I do same thing but use yahoo finance (instead of MSN). Although, I use this same approach, it has one problem. When I open the excel sheet, it asks whether I want to update? when I say ‘yes’, it does not update all the sheets that I have (i maintain one sheet for one security). I have to go to individual sheet and refresh it. I tried putting everything on one sheet. That also does not work. I have to go to individual cell (where this funtion is used) and refresh it. Unfortunately, I do not know anything better. If you know, please share with us.

    DT

POST A COMMENT

If you would like to make a comment, please fill out the form below.

Name - (Required)

Email - (Required but not published)

Website - (Optional)

ENTER YOUR COMMENTS BELOW






Student Loan

www.financialwebring.com