If you are an Excel user, and you often track and manage stocks or ETFs or mutual fund portfolios, you need to be able to get real time stock prices in Excel.
Before now, Excel users could pull out stock data from Yahoo! Finance until Verizon ended this capability in 2018. Luckily in 2019, Microsoft released its Stock feature and STOCKHISTORY function that enables users get real time stock prices and other metrics for analysis into Excel spreadsheet. A fantastic move and huge relief I must say.
However, this feature is only available to office365 subscribers. So how can other millions of Excel users who do not have office365 subscription get real time stock prices in Excel?
Thankfully, there are a few ways to get real stock prices in Excel, even if you do not have office365. And one way is by using the commercial add-ins available in Microsoft store that can allow you get real time stock prices and historical data into Excel. Another way is by linking google sheet to Excel through web query.
The good news is that you can use these methods to get real time stock prices even with earlier versions of Excel – 2010, 2013, 2016 and 2019.
In this tutorial, I’ll show you how to get real time stock prices using these two methods:
- Via Add-ins
- Using Google Sheet
This tutorial covers the following topics:
- Method 1: Get Real Time Stock Prices in Excel via Microsoft Add-ins
- How to Get Real Time Stock Price in Excel using Stock Connector
- Method 2: Get Real Time Stock Prices in Excel using Google Sheet.
- Publish Google Sheet to the Web.
- Linking Google Sheet to Excel.
1. Method 1: Get Real Time Stock Prices in Excel via Microsoft Add-ins
One way to get real time stock prices in Excel if you don’t have Office365 is by going to Microsoft store to search for an add-in that can display stock prices in the worksheet.
You will find a number of add-ins that can display real time stock prices, but in this post, I will focus on one add-in Stock Connector.
If you do not already have the add-in button on your Excel ribbon, you can check out how to add the Developer tab to Excel ribbon to enable you manage add-ins.
Once you have added the Stock Connector add-in, you can access it from Excel ribbon and search for any stock price to be displayed in the sheet.
To add Stock Connector add-in,
1. Go to Insert Tab and click on My Add-ins
Go to Developer Tab and click on Add-ins
2. Click on Store button to search for Excel add-ins.
3. Type “Stock” in the search box and it will display a list of some free Excel add-ins.
4. Click on the Add button next to “Stock Connector“.
This will add the Store Connector icon to My-Add ins. If you click on My Add-ins, you will see the Stock Connector icon has been added to the list of add-ins you have.
5. Click on the icon to launch Stock Connector and the stock connector panel will appear on the right side of your worksheet.
2. How to Get Real Time Stock Prices in Excel using Stock Connector Add-in
To get a stock price form Stock Connector,
6. Type in the name of the company or ticker symbol of the company’s stock that you want to get the stock price in the “Enter a stock” field and click on the Connect button.
For example, as shown in the image below, to get the stock price for Microsoft, use the ticker symbol “MSFT”.
7. It will display a list of countries where that company’s stock is available for you to select. In this case, select US stock price.
8. This will display the price, change in price and updated time of the company’s stock. in the stock connector pane.
9. Next, a display window will appear. Now click on the cell where you want the stock price to be inserted, then click the ok button.
Here, we select cell C3.
This will insert the stock price into the selected cell as shown below.
Similarly, to insert the other information displayed in the panel, that is the percentage change in the stock price;
- Click on the percent value in the panel and a Select Data dialog box will pop up.
- Select the cell you want to insert the percent change and click ok.
You can see that the data displayed in the Stock Connector panel is the same as that in the excel sheet. This is because as it updates in the panel, it will also automatically update the Excel sheet.
- The Stock Connector add-in only gives you the stock price, percentage change in price and the timing of the company’s stock.
- The stock price and other information displayed in the panel can be set to update from every 10seconds and above. You can set it to whatever time interval suits you.
3. Method 2: Get Real Time Stock Prices in Excel Using Google Sheet
Another way to get real time stock prices in Excel is by importing the data from google sheet.
The syntax for GoogleFinance function is:
=GoogleFinance(“ticker-symbol”, “attribute”, “start-date”, “end-date”, “interval”)
- A ticker symbol in quotation marks or the cell reference.
- The attribute you want to show, e.g price, in quotation marks. (Optional)
- The start and end date preceded by DATE( ) function with the date in the parenthesis. (Optional)
- The frequency, either Daily or Weekly in quotation marks.
After creating a list of all the stock prices you want in google sheet, you can then publish the sheet as a web page and then import the data into Excel via the web query that will use the Google sheet as the data source.
This way, when the stock prices and other data are updated on the google sheet, which happens approximately every 5 minutes, it will also be updated in the Excel worksheet.
Here are the steps to follow:
1. Sign in to Google Account.
2. Go to the free Apps and click on Sheets.
3. In Sheets, click on Blank to open a new spreadsheet.
4. Enter a list of the company names and ticker symbols of the stocks and mutual funds that you want.
5. In an empty cell beside the ticker symbol, enter the formula:
These formulas will insert only the current stock price of the company since no other attributes was specified.
However, it is advised to use both the exchange symbol and ticker symbol in the formula to ensure you get accurate results from google finance. For example, use “NASDAQ:MSFT” instead of “MSFT.”
If the exchange symbol is not specified, GoogleFinance will choose any one for you using its best judgement.
In the parenthesis, you can add any information you want it to display, separated by a comma. For example, to list the closing stock price for Google, taken at a particular time daily in the year 2021, you use the formula:
=GOOGLEFINANCE(“GOOG”, “price”, DATE(2021,1,1), DATE(2021,12,31), “DAILY”)
6. Press Enter.
You can drag the formula down to copy in the other cells and get the stock price of the other companies.
When you have all the stock prices and other information you need in the google sheet, save the Google sheet file in Google Drive.
7. Click on File -> Make a copy
8. Then, enter a file name for the sheet in the Name field to save the file in My Drive folder. Then click ok.
4. Publish Google Sheet to the Web
The next thing is to publish the google spreadsheet file to the web. This will enable you connect the google sheet with Excel via the web query.
To do this, follow these steps:
9. Click on Share button at the top right corner of the sheet.
10. In the dialog box that pops up, click on Change to anyone with the link written in blue ink and click on Copy link.
11. A link will be displayed in the url box. Click on Copy link.
Now that the sheet is published on the web, this will enable anyone who ends up with this link to be able to view the worksheet. For security, the google sheet you publish should contain only the list of stock quotes. Do not add other personal data to the published sheet.
5. Linking Google Sheet to Excel
When a google sheet is published on the web, anyone who has the link can access its content and the sheet can be linked directly to excel through the web query.
So, to link google sheet to Excel, follow the steps below:
After you have copied the link from google sheet, you will:
12. First open and save a New Excel workbook where you want the google sheet to be copied to.
13. Go to Data tab -> New Query -> From Other Sources -> From Web.
14. In the From Web dialog box, paste the link in the url field.
15. Select and delete “edit?usp=sharing” and enter “export?format=xlsx” in the same place.
This is to link the google sheet to the an excel file.
16. Click Ok.
17. In the Access Web Content dialog box, click Connect.
This will display the Navigator panel and you will see that the google sheet(s) is listed there. You will also see a preview of the content of the sheet.
18. Click on Load to import it to a new worksheet.
You also have the option Load To which allows you to specify where you want to send the sheet to.
Also you have the Edit button that allows you make any changes in the sheet before importing it to Excel.
And now, you have the google sheet data in your Excel worksheet.
If you add new information to the google sheet, it will automatically update the Excel worksheet as well, as shown below.
19. And if you want data in the Excel sheet to update regularly, click on any cell in the table and go to Data tab –> Refresh All -> Connection Properties.
20. In the Connection Properties dialog box,
- Check the Refresh every —– minute box to set the refresh time.
- Check the Refresh data when opening the file box.
21. Click Ok.
Now, if you close the excel workbook and re-open it, you will see that it has been updated further with the additional data from google sheet.
Note: You can either copy the data with its formula to your already designed template on another sheet or use the same sheet and amend as required.
And that’s it!
I hope you found this tutorial useful. Don’t forget to share with your friends. Glad to hear your thoughts in the comment section.