How to Import Current Stock Price Data into Microsoft Excel

Microsoft Excel is a very powerful tool to use for analizing a company's stock. Data can be quickly manipulated, therefore making you a more efficient and successful investor.

Being able to import up-to-date stock prices is a valuable feature that Microsoft Excel makes easy.

In this project, we will import current data from the exchanges into MS Excel and create a spreadsheet that will compare stock prices to desired entry prices. This trading analysis allows you to quickly determine whether a stock price is above or below your desired entry point.

Difficulty: Easy

Instructions

Things You'll Need:

• Computer
• Internet Access
• MS Excel

1. 
    
   Header Row

   Open Microsoft Excel. On your new spreadsheet, create a header that includes the following: Stock Symbol, a blank column, Current Price, Target Buy Price (see adjacent picture).

2. 
    
   Selecting Web Query

   Place your cursor on cell B4 and select it. On the Menu Bar, select Data - Import External Data - New Web Query (See adjacent screen shot).

3. 
    
   Open Query Dialog Box

   This will open the "New Web Query" dialog box. Your home page will be displayed. In the address block, manually type in www.finance.yahoo.com, and click the "Go" button.

4. 
    
   Getting a Quote From Yahoo! Finance

   Just as if you were searching for a quote on Yahoo!® Finance, type in the ticker symbol for the stock you want a quote on. This will be the ticker symbol quote that will be imported into MS Excel. For this example we are using COST. Click the "Get Quote" button to bring up the quote for Costco.

5. 
    
   Importing the Quote

   On the dialog box, scroll down to the "Last Trade:" and check the check box that highlights "Last Trade", "Time", "Change", etc. Then at the bottom/right of the dialog box, click "Import". Another "Import Data" dialog box will appear, asking where you want to put the data. Under "Where do you want to put this data?", "Existing worksheet" should be selected with "=$B$4" in the combo box below it. If this is correct, click OK.

6. 
    
   Successfully Imported Data

   You will now have eight pieces of data imported into MS Excel, "Last Trade" being at the top of the list. For the Target Buy/Current Price analysis we only need last trade data. However, you cannot delete the other data if you are going to put additional quotes below the first. To mitigate this, hide rows B5:B11. To do this, highlight the rows B5 through B11 (highlight them from the actual number row on the left). Right click and choose "Hide". Your next line of data will be on row B12.

7. 
    

   You can now manually input your "target Buy price" in the next column. Then set up a conditional formatting formula for the "Current Price" cell to highlight it at the desired time, i.e. When the current price is less than or equal too the target price, signaling a buy to you the investor. To learn how to set up conditional formatting, check my article "How to Set Up Conditional Formatting in MS Excel" article (link to the article below in under resources).
   
   See the adjacent picture to see multiple companies' stock prices in the same spreadsheet with conditional formatting set up. To refresh the screen, click the "Refresh All" button on the External Data tool bar.

Tips & Warnings

• Use this spreadsheet to alert you to possible opportunities for investing or trading. Do your homework, value the company correctly to determine you target buy price. Set up the conditional formatting to visually queue you to the opportunity.
• Investing is inherently risky. All investors should seek advice from their financial advisors, brokers and accountants to make their own investment decisions.
• The data used in this eHow article, although current at the time of publishing, is for example only. The "Target Price" column of the example is inaccurate and was changed to show how conditional formatting reacts.

Resources

• Before You Invest ANY Money, READ THIS!
• How to Set Up Conditional Formatting in MS Excel

[cb4me]

on 6/10/2009 Very helpful. I will be trying this!