Get stock quotes – Excelchat

Excel is widely used for calculations, analysis and storing different types of data.  There is one other feature that is especially useful for monitoring the stock market. This article will guide us through the step-by-step procedure on how to pull real-time stock quotes from the web into an Excel worksheet.

Figure 1.  Final result:  Get Stock Quotes

How to get stock quotes

Here are the steps:

• Search online with the keywords “msn money markets”  

Figure 2.  Searching online

• Click the website for “Stock market today: News, data and summary – MSN Money” and we will be directed to the website

Figure 3.  Stock market update

• Copy the URL: https://www.msn.com/en-us/money/markets by pressing Ctrl + C
• Open an Excel workbook
• Click Data tab > From Web under Get External Data group

Figure 4.  Get External Data option in menu

• The New Web Query dialog box will appear. Paste the URL in the Address text box by pressing Ctrl + V.  Click Go.

Figure 5.  New Web Query dialog box

• Wait for the page to load.  Then we click the arrow just above the logo for MSN and it will turn into a green check.  Click Import.  

Figure 6.  Importing data

• The Import Data dialog box will appear.  Click Properties.

Figure 7.  Import Data dialog box

• In the External Data Range Properties dialog box, do the following:
  • Select the check box for Refresh every and enter “1” in the text box for minutes
  • Tick Overwrite existing cells with new data, clear unused cells

Figure 8.  External Data Range Properties dialog box

• Click OK.

The worksheet will be loaded with information from the website, but without any formatting as shown below.

Figure 9.  Output: Importing data from the web

• We want to create a summary in Sheet2.  Suppose we want to get the stock quotes of five stocks as listed below.  In cell C4, we enter the formula

=Sheet1!A153

Figure 10.  Stock Quotes Summary

Figure 11.  Navigating through the raw data

The SENSEX stock quote is located at cell A153 of Sheet1, hence the formula.  For the other stocks, we need to enter the corresponding cell references by navigating Sheet1.  

As a result, the quote per stock is now linked to the actual value in Sheet1.  

Figure 12.  Output: Get Stock Quotes

Every one minute, the values will refresh and our table will be updated.  

Figure 13.  Real-time Stock Quotes Update

By adding a built-in connection in our worksheet, we can easily obtain live stock quotes from the web into Excel.  

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.