Forum Discussion
Portfolio tracking in Excel
What you're describing is (or should be) fairly simple. But having said that, you could help us help you by posting a copy of what you have created so far. That's because any specific solution will be very much a function of what you've created, how you've laid the data out, etc.
You might also give a more complete description of how actively you're trading stocks--are you basically a long term, buy-and-hold investor, a day-trader, or something in between? Do you buy and sell options as well as stocks? ETFs? Mutual funds? Do you incorporate tallies of long- or short-term capital gains for tax purposes? Etc.
Do you enter the data manually on each trade? Or do you get a download from the brokerage with which you do business?
How experienced are you with Excel? And what version of Excel are you working with?
Thanks for your reply. I have attached a copy of my spreadsheet so you can look. I added a couple sample stock trades just to show what little formulas are already in place. It is not a very complicated spreadsheet so far.
I only trade stocks every 2-3 weeks and sometimes hold certain stocks for months and I will be putting each trade in manually since there are not too many. I trade mostly in stocks but have a couple ETF's. It would be great to incorporate more information about gains for taxes but what I am more focused on is creating a (semi) simple spreadsheet to track the trades I have made in the past and to keep a tally on returns and other pretty basic information.
I have made a couple complicated spreadsheets with large functions, but I would not call myself a power user at all, and I use Excel on Mac.
Thanks for your help.
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.
Hi again,
Thank you for your reply. I am sorry for the very delayed response.
I have been using the built in Excel functions for pricing data because for me, it is the easiest way since I am just an amateur investor. I am trying to create a simple spreadsheet that can track the basics on my investments and past trades because I could not find a software that I liked to track everything for me. And, Excel has greater potential for customization and more complex functions can be built in later. I am impressed by the spreadsheet that you attached and maybe if I need more powerful features in the future, I can implement them.
I am definitely open on re-designing the spreadsheet I have made thus far. I think that adding another sheet would make things easier to track for sure. I have attached my spreadsheet again but have input a few more rows of stock trades. I am open to whatever changes you suggest.
Lastly, I have heard of The Motley Fool but have never spent much time on their site. I recently started investing just to learn more about the stock market and will read up on The Motley Fool as they sound like a valuable resource.
Thanks again for all your help.
Short of a total change to things, I'm suggesting in the attached revision that you track any one position as a single row, the purchase and the sale on the same line. This would get tricky only when you start selling off positions in quantities that differ from the quantities you purchased. If, for example, you had purchased 10 of BABA one one day and then sold 6, retaining 4. There are various ways that could still be handled, which we can leave for the future. So long as you just keep acquiring and selling in the same quantities, this will work.
I then track current prices only for those that are active; the holdings that have been sold are marked as "Closed" with final profit or loss figures calculated and locked in. No need to keep looking at current price (unless you want to; but I'm assuming you make your decisions to purchase based on other inputs).