spot_img

How To Monitor Stock Prices In Excel

If you are an investor who invests in stock, then you might be knowing how important is monitoring the stock prices. You might have to make wise and quick decisions based on the stock prices. There are a number of financial websites like Yahoo Finance, Forbes etc that you can view to monitor stock prices. The happy news is that you can even use Microsoft Excel to monitor the performance of your investments. You can set up your Excel in such a way that all the details you need for the stocks of your interest can be viewed and updated easily and quickly.

Monitor Stock Prices

Step 1. Open Excel and save your file as stockmonitor.xlsx. Click the cell A1 and go to Data (in the main menu) –> From Web (from the Get External Data group).

Monitor Stock Prices from web

You will get a window like this:

Monitor Stock Prices new query

The website displayed could be different. In the space to enter the address, enter the site which you usually use to monitor stock price (Here I use cnnmoney.com) and click the OK button.

Step 2. Enter the symbols you want to track separated by commas (if more than one) and click the Search button. Now you will get a screen like this if you have entered AAPL,GOOG in the search textbox:

Monitor Stock Prices markets

You could find arrows in yellow boxes at different locations.

Step 3. Click the specific arrow which is shown just next to the information you want.

Monitor Stock Prices results

When you move the cursor over the arrow in the yellow box, the specific yellow box becomes green box. Once you click the arrow, it becomes a tick mark.

Monitor Stock Prices multiquote results

Step 4. Click Import button. You will get a window like this to choose the location where you want to insert the data.

Monitor Stock Prices import data

Step 5. Click OK and your screen will look like this:

Monitor Stock Prices data imported

Step 6. Go to Data (in the main menu) and click Refresh All (from the Connections group), whenever you want to get the latest stock details.

Monitor Stock Prices refresh all

Thus, you can refresh the details as and when you want and get the updated information.

There is another simple method to monitor the stock price.

Step 1. Click Sheet2 in the same stockmonitor.xlsx file (from the bottom of the file).

Monitor Stock Prices sheet

Step 2. Click the cell A1 and go to Data (in the main menu) and click Existing Connections (from the Get External Data group).

Monitor Stock Prices existing connections

You will get a window like this:

Monitor Stock Prices blank connection

Step 3. Select the last option from the list, MSN MoneyCentral Investor Stock Quotes and click Open. You will get a window like this:

Monitor Stock Prices import new connection

Step 4. Click the Properties button to get a window like this:

Monitor Stock Prices range properties

Step 5. From the Refresh control group, select Refresh every check box and change 60 to 1 so that the data will get refreshed every minute. Click OK to get the window shown in Step 3. Click OK. You will get a window like this:

Monitor Stock Prices enter parameter value

Step 6. Enter the stock symbols you want to monitor. Separate them with commas if there are more than one symbol. Check both the Use this value/reference for future refreshes checkbox and Refresh automatically when cell value changes checkboxe. Click OK. Now your screen will look like this:

Monitor Stock Prices ready

Here, you do not have to refresh the data. It will get automatically refreshed every one minute and also when the any of the values changes. This method is useful if you want to get the updated data without you manually refreshing it. You just need to visit the Excel file in this case whenever you want to monitor the stock prices.

Template

You can download the Template here – Download
spot_img
Previous articleHow To Link Objects In Excel
Next articleGoogle Classroom Tutorial