Portfolio tracking in Excel

Kokobun 

OK, that's a start, although it would help to see a more elaborate set of data. But let me do what I'd do if we were getting together in person: ask some more questions.

First, I see that you're using the built-in Excel functions to get current pricing data. I've experimented with that some, and would find it useful for some purposes, but basically don't find it all that useful for tracking my own transactions and history. Instead, I take advantage of the on-demand download of data I get from my brokerage (Fidelity, in my case). That download, which I retrieve pretty much daily, sometimes several times a day, happens to include purchase price and current price, along with such things as dividends and ex-div dates (the latter of which are missing from the Microsoft service at present, much to the dismay of other serious investors).

I happen to do a fair amount of Options investing, and can download the detailed option chain data to evaluate potential trades.

So first question: who is your broker (or who are your brokers) and does it (do they) provide downloads of your holdings? [I'd be very surprised if that's not available to you.]

Next observation, you only included a single buy and sell, on the same ticker. Here's where a more complete knowledge of your history would come in handy. It sounds as if you don't do a very high volume of trades, and as if, at any given point in time, you have a fairly limited portfolio ......(by the way, most experienced investors I know would recommend a longer term buy and hold, holding periods amounting to years if not decades).  In my own Options tracking workbook, because options often are shorter term vehicles, and a single strategy can have a series of transactions over a period of two years,  I actually have a separate page for tracking the history of each strategy with each company...  And then I use Excel's XIRR function to calculate the annualized ROI for each strategy. I'll attach a sample of that sheet. Just be aware that this is an options tracker, so there is a more detailed history than your single stock/ETF might be. You might have multiple rows if you have an ongoing history, even if only a series of dollar-cost-averaging purchases in the same equity. I include this mainly to show you that the XIRR function might be the more useful way to track how your investments have performed over the course of years.

I also have two summary spreadsheets, one for currently active investments; another for closed. Once I close a strategy altogether, I just move the individual sheet tracking that one over to the "Closed Strategies" workbook.

You could do something similar when you buy and sell MSFT, for example, once in 2020, and then again in 2021....the no-longer-active would be saved, but out of the way. And that's my second question: are you open to re-designing how you track these?

Anyway, it also occurs to me that there are ways using the more simple single spreadsheet (let's call it a database) where you track all of the stock and ETF transactions. You could just create a column for "Status" and mark each row "Open" or "Closed" -- only the "Open" would be included in any formula calculating "Gain today"; the "Closed" would have "Final Gain" calculated once and stored.

But if you don't mind, it actually would be helpful if you could post a more complete spreadsheet so I (or somebody else on this site) could show how we'd do it. Include at least enough so there are some still open, some closed, etc.

A third question: are you familiar with The Motley Fool? https://www.fool.com/ It's a very popular investing education service (with live investment recommendations as well). They've been around since the 1990s. I'm also active on their message boards, even with the same user name as I have here, mathetes (a Greek word for student or learner), although these days I mostly inhabit the Options boards. If you're not, I'd recommend looking into it. One thing you'll quickly realize is that their advice is much more in the direction of buy-and-hold for the longer term. But you'll also see examples of investment tracking spreadsheets that others have created. Personally, I like to create my own, because that way I really am intimate with how it works, and comfortable making changes when they're needed.