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
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.