LIVE Stock Portfolio Dashboard in Excel in 30 Minutes!

Looking to master your stock portfolio management in Excel? In this easy-to-follow guide, "LIVE Stock Portfolio Dashboard in Excel in 30 Minutes!", we cover everything you need to know to set up a real-time stock portfolio tracker. From creating a transaction log and calculating returns to breaking down sector and market cap allocations with beautiful pie charts, this tutorial is your go-to resource for all things investing and Excel. Whether you're a novice or seasoned investor, this video will equip you with the tools to calculate gains, losses, dividends, and more, all in a live, up-to-date Excel dashboard.

👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,"") at the end of the IF statement from that same formula.

👨‍💼 My Freelance Financial Modeling Services:

Chapters:
0:00 – Overview of the Live Stock Dashboard in Excel
0:39 – Create a Transaction Log
2:47 – Begin Constructing Portfolio Dashboard
4:11 – Retrieve the Sector of Each Stock in the Portfolio
4:46 – Find the Market Cap Size of Each Company
6:22 – Calculate the Percentage Allocated to Each Share
8:22 – Retrieve the Beta of Each Stock
9:13 – Calculate Today’s Return on Each Share
10:31 – Calculate Average Purchase Price Per Share
14:22 - Calculate Average Sale Price Per Share
15:24 – Calculate Total Dividends Received
16:45 – Calculate Realized Gain or Loss on Each Stock
17:37 – Calculate Total Gain or Loss on Each Stock
18:30 – Calculate Unrealized Gain or Loss on Each Stock
19:52 – Calculate Total Portfolio Gains and Losses
22:45 – Conditionally Format Gains & Losses
23:44 – Create Market Cap Size Pie Chart
26:33 – Create Sector Allocation Pie Chart
27:29 – Create Individual Securities Holdings Pie Chart

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

[RyanOConnellCFA]

👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes, "") at the end of the IF statement from that same formula.

[mariiguo]

Thank you Ryan!! Amazing video, I followed your steps to build the tracker and it is so so helpful! I was having real challenges trying to come up with one on my own. So glad I found your channel :D

[MsDave]

I am trying to get into stocks and this has been very insightful

[jasonrichman]

Ryan,

Sharp to obtain the combo charter. :) Great video! . I love excel and how it can be utilized for personal finance. Take your dashboard, customize it by adding a scenario analysis, and incorporating the use of the data analysis tookpak (solver, correlation, covariance, and descriptive statistics) needless to say, it makes for easy use to manage an individual or family portfolio.

[willacare]

Amazing Video! I may have missed it, but what happens if I reinvest the dividends instead of taking the cash value?

[sfredheim]

Great video! I have been using a similar sheet for my portfolio for quite awhile now. However, there is a few problems with your model. The most obvious and problematic one is in a scenario where you have sold a stock, and then bought it again later. The purchase price from your first order would then be included when calculating price per share.

EDIT:

Didn’t watch the whole video when commenting, so I see that you wanted to include realised gains / loss in the return for each stock.

[mbilly]

i like you video and includes the transaction sheet for buy and sell transactions. A thumb up for you video👍
probably the equivalent of the google sheet would nicely complement (if it is not available)

[etienne]

Great video again. 2 remarks. With the IFERROR when I use the, ””) I get a value error if no sale has taken place. Better to use, ) which is blank. Secondly, average sale price is dividing by total buy shares and not sell shares. Now as a brain teaser I am struggling with, how to deal with stock splits. In that case you need to know if a split e.g. took place before or after you sell some shares. Any ideas how to solve that? Thanks!

[jasonlee]

Hi Ryan, I just purchased the template and am happy with it :) Just wondering how you would add transaction fees to the template to correctly calculate returns? Thanks

[donmachen]

Great video Ryan. Thanks you. How can this be used for keeping track of option trades (single options and spreads) and stocks in the same Excel sheet? Thank you.

[ChrisPBacon-jsfe]

Great video! Any idea on how to deal with stock splits? For example, Google split 20:1 in 2022. In my transactions this is represented as two consecutive sell/buy transactions. However, the value of the purchases and sales pre-split are obviously not the same as post.

[gabrielbrendel]

Two suggestions:
- Filter for different strategies or different portfolios.
- Option tracking

I have a problem with both.

Awesome simple dashboard tho

[MarkBarker-co]

very helpful thanks. How would you include trading fees, and the effects of FX at time of trade/current/time of sell?

[bidyutchakraborty]

Hi Ryan. Liked your Excel sheet. Can you tell how do you take care of stock splits, automatically. I'm bit inquisitive about it.

Sorry! I realised that you have already given your thought about stock splits in some of the answer of the comments. Thanks

[macshock]

This is awesome!!! Tks!!! Now what if you want to calculate the performance in % over time (TWR)? Say you need to store monthly performance of this changing portfolio.

[jserenson]

I am working through the exercise and have a question. I originally made the Dashboard Individual Holdings a table. I could not get the name to work without spill errors and could not figure it out. I finally did some internet searches on spill arrays and It looks like the Spill formulas like UNIQUE will not work in a table. So I had to remove the table and just format the cells to look like yours. So the Stock Portfolio Dashboard is not a table just regular cells.. Unless I am missing something. There is room up to row 18. What if you have more than 30 active investments. Do we have to add more rows and force the Lower portion the portfolio summary off the screen. ?? Just wondering what will happen as we cannot use a table there.

[alejandrovelez]

Hi Ryan, thanks for the video. Very useful. How would you go about tracking a NAV for the portfolio such that you can see the performance over any specific time period? Thanks

[idkqukh]

I have a question. With the part of the video talking about Realized and Unrealized Gains and losses, do you have to have shares sold on the ledger for this formula on excel to work? I got as far as this section ok as far as following along and making my own copy but every time I buy shares, that total gains remains blank while when I sell a stock, it is a negative number.

[tanmay-sinha]

Hi, great video. But what if I want to compare my portfolio returns vs an index, how do I do that? Given that i have invested at irregular intervals since the start.

[lopponep]

Great video! Thank you! Is this possible to do also in Google Sheets?