How To Monitor Stock Prices in Microsoft Excel

Microsoft Excel has a feature that can use websites as data sources. Information on the website can be displayed directly on the worksheet. This technique can be used to monitor stock prices within the application, giving you an accurate record that you can refer to and frequently update. You must have an Internet connection for this work.

  1. Open a new Excel file. Launch the program and prepare a worksheet by typing in the appropriate headings and labels. For example, you can designate sections of rows and columns for a particular company's stock data, then divide that further with labels such as 'Date', 'Points', 'Range', etc. How you format the worksheet's appearance is entirely up to your preference. The point is to simply make things easier to find and reference.
  2. Get online data. Select a cell in your prepared worksheet where you want stock prices and other data to display. Expand the Data menu and choose Import External Data. This option is found near the bottom of the list. From the submenu list that will appear choose the New Web Query option. This function will essentially allow you to link a particular website as a data source for your Excel file. On the dialog box that will appear, type in a URL on the Address input field. Naturally you would want a website that already monitors stock prices such as Yahoo! Finance, so type in "" if you want this site as your online data source. You'll see the dialog box's main window refresh and display that particular website. This dialog box acts like a browser which means that the links and input fields on the displayed website will work and navigate just as it would on Internet Explorer.
  3. Choose which data to include. Sites like Yahoo! Finance usually provide a search function for finding stock price information on specific companies. On the "Get Quotes" input field type in a company's stock symbol and click the button. Again, the main window will refresh and display the appropriate webpage for the company you searched. You will notice that some sections of the webpage are marked with arrows in yellow boxes. These are the sections that can be linked and displayed on your Excel worksheet. Click on a particular section's arrow. The symbol will change into a green box with a check, meaning that it has been selected and ready for import into Excel.
  4. Import the data. On Yahoo! Finance, the obvious section to choose would be 'Last Trade'. Click on the Import button at the bottom of the dialog box, verify that the right cell on the worksheet is selected, then click OK. The stock price information should now be displayed on the worksheet. You can repeat this process starting from Step 2 to add other stock information or get stock prices of other companies until you complete the worksheet with the relevant data. Don't forget to save the Excel file.
  5. Update the information. Open the Excel file every time you wish to update the stock prices. Select the appropriate cell, right-click on it, then choose Refresh Data on the context menu that will appear. Excel will link with the website data source then reflect updates on the worksheet.

You can enhance your stock price worksheet by displaying it as a chart. It's easier to evaluate trends through the visual representation of graphs.


Share this article!

Follow us!

Find more helpful articles: