Stock become one of the most integral parts of our economic system. The global economy depends heavily on the stock market conditions. As Excel is used almost as preliminary means of handling financial records and calculations, having the updated value of the stock value of different companies is a must-have feature. In this article, we are going to demonstrate how you can get stock prices directly in Excel efficiently.
Download Practice Workbook
Download this practice workbook below.
3 Easy Ways to Get Stock Prices in Excel
For the demonstration, we are going to get the stock price in Excel, we use the following dataset. In the first column, we got the company’s name, in the second column we got the Stock symbols or Ticker symbols, and finally, we got the stock price in the third column. Well, how we manage to get this stock price showing in Excel will be discussed elaborately.
1. Combining STOCKHISTORY and TODAY Functions in Excel
- In order to get the live stock prices in Excel, we need to input the stock symbol or their ticker symbols in Excel.
- To do this, enter the symbol AAPL in cell D5 for the Apple corporation.
- Similar way, enter the symbol MSFT and SONY from Microsoft and Sony corporations respectively in the cells D6 and D7.
Then enter the following formula in the cell E5:
- Then drag the Fill Handle icon to cell E7 and then you will notice that the range of cells E5:E7 is now filled with prices of the companies mentioned in the range of cell B5:B7.
By following the above way, we can get current stock prices in Excel quite efficiently.
Breakdown of the Formula
👉 TODAY(): Returns today’s date.
👉 STOCKHISTORY(D5,TODAY(),,2,0,1): This function returns the history of stocks, from specific opening date to another specific closing date. If one single date is used, like in this example we used Today() to use today’s date, then returns only that day’s stock price mentioned in the first arguments (D5).
- You can only get the price information of the Stock data.
- Need to be connected online in order to fetch data.
- This add column option is only available in Excel 365 or the online version of Excel.
2. Get Stock Prices Utilizing Built-in Stocks Command
As the demands asked, Microsoft adds a separate command in the Data tab to get stock quotes directly. This Stock command will not only fetch real-time stock price but also can get stock data like Change (%), Beta, Top demanding stock, Historical stock price, etc.
- In the beginning, select the company’s name and go to the Data tab and from there, click on the Stocks icon.
- After clicking the Stock icon, you will notice that all of the company’s names now change to their official full form with the Stock symbol or the Ticker symbol with them.
- In some cases, the company may be enlisted multiple exchanges in different parts of the world at once. So Excel will open a sliding side menu.
- And from that side menu, we need to select the stock exchange we intend to use. We choose NYC here for example.
- Now the range of cells now filled with the company’s name with the appropriate ticker symbol.
- You also notice there is an Add-column icon on the top corner of the cell. Which will be used to get various stock information about different companies.
- Click on the add column sign, there will be a drop-down menu.
- From the menu, click on the Price.
- After clicking on Price, you will observe that there is a new column right side of the Company Name, with the updated value of all stocks presented.
- Right next, click on the add column sign again, there will be a drop-down menu.
- From the menu, click on Change.
- After clicking on Change, you will notice there is a new column just right of the Price column. Showing the percentage of change in prices in recent time.
- Furthermore, click on the add column once again.
- From the drop-down menu, select the Beta option.
- After clicking on the Beta option, there will be a new column right beside the Change(%) column.
- Not only this, but by clicking the stock on the left of each row in the company column, we can get detailed stock data.
- Moreover, if you want to refresh the stock data shown here, just go to the Data tab and click the Refresh All command. It will refresh the whole workbook Data.
Choose Different Stock Exchange
If you want to switch to a different stock exchange, you just need to follow the below steps.
- Select the cell of which companies exchange you want to alter, then right-click on the mouse and click on Data Type.
- Then click on Change.
- There will be a new slide options window will spawn on the right side,
- On the options menu, delete the ticker symbol and the identifier at the end of the Company Name
- Then click the search icon.
- After that, you will the Sony corporation enlisted in numerous exchanges worldwide, we chose the NYC exchange and click on Select.
- Now you will notice that the stock data is from NYC Stock Exchange now instead of London Stock Exchange.
Beta value denotes the volatility of a stock. Where the whole market’s beta value is 1. If some stocks’ beta value is above 1, then the stock is more volatile with respect to the market. And vice versa.
3. Using Stock Connector Add-in to Get Stock Prices
To get stock prices of various companies directly on your Excel worksheet, you can use some Add-ins in the Excel worksheet.
- At first, you need to input the company’s name in the range of cell B5:B7.
- Then from the Insert tab, click on the Get Add-ins icon on the Add-ins group.
- After that, you will be taken to the Office Add-ins store.
- In the store, search for the Stock.
- Then in the search result, look for the Stock Connector.
- Then click on the Add button on the corner of the Stock Connector add-in.
- The stock connector add-in is now available on your Excel sheet.
- To use this, you need to look for it in the Home tab, as shown in the image.
- Then copy the contents in the range of cells B5:B7 to cells C5:C7.
- Next, in the Home tab, click on the Stock Connector Launch icon.
- Then the Stock Connector will launch in the side panel.
- Then select the company name cell in the worksheet, of whose stock price you want to know.
- Click Connect after this.
- After clicking Connect, you will be asked where would be the output value will be placed, choose the location and the stock price will be placed there.
- Select cell C5 and click OK.
- After this, you will notice that the stock price of the Microsoft Corporation is now showing in cell C5.
- Repeat the same process for other companies.
- For some companies, the add-ins may ask you which stock exchange value of the selected company you want to show, because, some of the companies might be enlisted in multiple stock exchanges.
- For example, in our case, Apple Inc is enlisting multiple stock exchanges around the world.
- In this case, the below window appears. we selected the US stock exchange.
- Now we have the stock prices of different companies in the range of cells C5:C7.
- The values will updates every 30 seconds automatically in the cells.
- In the sliding panel, we can also see the percentage of their stock price increase.
To sum it up, the question “how to get stock prices in Excel“ can be answered in 3 principal ways. One is by using formulas and another one is to use the built-in Stocks command from the Data tab. Another way is to use Add-ins from the Office store. The stock command is most efficient and less time-consuming. The stock connector is also quite handy. On the other hand, formulas are more flexible. But they only can view stock prices, which is not very convenient and practical.
A workbook containing a dataset with all the methods done is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
- How to Import Stock Prices into Excel from Google Finance (3 Methods)
- Stock return analysis using histograms & 4 skewness of histograms
- How to Add Stock Data Type in Excel (2 Effective Methods)
- How to Calculate Correlation between Two Stocks in Excel (3 Methods)
- How to Download Historical Stock Data into Excel (with Easy Steps)