Stocks in Excel

Markets go up, markets go down, but one thing is always the case: it’s fun to track your stocks. With the help of Excel’s built-in functions, you can monitor stocks in Excel easily. Connect directly to the data and pull every possible financial metric you may need.

How to Review Stocks in Excel

Let’s connect to stock data to review financial metrics and analyze the information.

Start by typing a stock symbol in a cell in your spreadsheet. This is the shortened, one to four character symbols that represent publicly traded companies. Use this page to search for stock symbols if you don’t know the one you have in mind.

Then, switch to the Data tab on Excel’s ribbon. With the stock symbol cell selected, click on Stocks. Excel will convert the active cell to one that is connected to stock data. Notice the columns icon on the right side of the stock cell.

Click on this cell and you’re treated to a huge list of data you can pull in about the stock. That includes simple metrics like the price as well as more complex financial numbers like beta and P/E ratio, for example. For my example, I’m going to add the 52 week high, the highest stock price in a one year range.

Now, let’s repeat the same step and add a new column, the current price, labeled Price. Remember: this data is connected and will update over time.

With this data in the spreadsheet, you can begin to work with it like normal numeric data. For my example, I’ll write a simple formula to compare the 52 week high versus the current price. It’s a simple subtraction formula to find how far off the 52 week high my stock is, shown in the example below.

Make sure to spend time exploring all of the available metrics in this option. Keep adding more columns to show a complete set of stock screening information.