How to Get Stock Quotes 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).
You will get a window like this:
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:
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.
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.
Step 4. Click Import button. You will get a window like this to choose the location where you want to insert the data.
Step 5. Click OK and your screen will look like this:
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.
Thus, you can refresh the details as and when you want and get the updated information.
Monitor Stock Prices alternative
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).
Step 2. Click the cell A1 and go to Data (in the main menu) and click Existing Connections (from the Get External Data group).
You will get a window like this:
Step 3. Select the last option from the list, MSN MoneyCentral Investor Stock Quotes and click Open. You will get a window like this:
Step 4. Click the Properties button to get a window like this:
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:
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:
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.
How to Get Stock Quotes using WEBSERVICE
There are different ways to get stock quotes in Excel. One of the alternatives is through the WEBSERVICE function and an online stock quote provider such as Yahoo Finance.
Here are the steps to get stock quotes in Excel using this method:
- Open a new Excel workbook and select a cell where you want to place the stock quote.
- In the cell, type the following formula:
=WEBSERVICE("https://finance.yahoo.com/quote/[symbol]")
Replace “[symbol]” with the actual stock symbol you want to get the quote for. For example, if you want to get the quote for Apple Inc., you would type “AAPL” without the quotation marks.
- Press Enter to execute the formula. The cell should now display a long string of text that includes the stock quote.
- To extract the stock quote from the text string, use the “MID” function. Type the following formula in a new cell:
=MID(cell with the WEBSERVICE formula, start position, number of characters)
Replace “cell with the WEBSERVICE formula” with the cell reference of the cell that contains the WEBSERVICE formula. Replace “start position” with the number of characters from the beginning of the string where the stock quote starts. Replace “number of characters” with the total number of characters in the stock quote.
For example, if the cell with the WEBSERVICE formula is A1 and the stock quote starts at character 50 and is 10 characters long, the formula would be:
=MID(A1,50,10)
This should extract the stock quote from the text string and display it in the cell.
Note that stock quotes may be delayed and that this method may not work for all stock quote providers. Additionally, this method does not include any historical data or charting capabilities.
Leave a Reply