How to Get Stock Prices in Excel (3 Easy Methods)

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.

https://www.exceldemy.com/track-stock-prices-in-excel/

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.

Get Stock Prices in Excel


1. Combining STOCKHISTORY and TODAY Functions in Excel

Using a simple formula can directly get the stock price in real-time in Excel. We will use STOCKHISTORY and TODAY functions in order to fetch live stock prices.

Steps

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

Combining STOCKHISTORY and TODAY Functions Get Stock Prices in Excel

  • Similar way, enter the symbol MSFT and SONY from Microsoft and Sony corporations respectively in the cells D6 and D7.

Combining STOCKHISTORY and TODAY Functions Get Stock Prices in Excel

Then enter the following formula in the cell E5:

=STOCKHISTORY(D5,TODAY(),,2,0,1)

Combining STOCKHISTORY and TODAY Functions Get Stock Prices in Excel

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

Combining STOCKHISTORY and TODAY Functions Get Stock Prices in Excel

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

Note:

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

Read More: How Do You Automatically Update Stock Prices in Excel (3 Easy Methods)


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.

Steps

  • In the beginning, select the company’s name and go to the Data tab and from there, click on the Stocks icon.

Utilizing Built-in Stocks Command to Get Stock Prices in Excel

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • Click on the add column sign, there will be a drop-down menu.
  • From the menu, click on the Price.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • Right next, click on the add column sign again, there will be a drop-down menu.
  • From the menu, click on Change.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • Furthermore, click on the add column once again.
  • From the drop-down menu, select the Beta option.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • After clicking on the Beta option, there will be a new column right beside the Change(%) column.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • Not only this, but by clicking the stock on the left of each row in the company column, we can get detailed stock data.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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.

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

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

After clicking the stock icon, you will notice that all of the companies names now change to their official full form with the stock symbol or the Ticker symbol with them.

  • Now you will notice that the stock data is from NYC Stock Exchange now instead of London Stock Exchange.

Note:

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.

Read More: How to Import Stock Prices into Excel from Yahoo Finance


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.

Steps

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

  • Then copy the contents in the range of cells B5:B7 to cells C5:C7.

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

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

Using Stock Connector Add-ins to Get Stock Prices in Excel

Read More: Get Stock Quote with Excel Add-in (With Easy Steps)


Conclusion

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.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo