Joseph Hogue, CFA – Complete Portfolio and Stock Comparison Spreadsheet

Track your entire portfolio, see the gaps in your investments and compare two stocks instantly with my Portfolio Tracker spreadsheet.

How to Track Your Stocks in One Simple Spreadsheet

I want to show you step-by-step the spreadsheet I developed to track my investments so even if you don’t download this one, you’ll know what’s possible when building your own. The spreadsheet has been an amazing tool in not only organizing my investments but in analysis and planning. I couldn’t live without it.

Of course, the heart of any stock portfolio tracker is the ability to put in your stocks and see your portfolio return. You’ll want the basic columns like stock name and ticker symbol but also a column for the company’s industry and sector. This is going to be very important when it comes to seeing the big picture in your stocks.

Portfolio Spreadsheet Tracker
With just the ticker symbol, how many shares you bought and the price paid, this spreadsheet will show you your return for each stock as well as the portfolio. It also automatically fills in the stock sector and industry for each company.

But I wanted more for my portfolio tracker so it also automatically fills in important information about the stock like company size, dividend yield and how risky it is. Finally, the spreadsheet shows me how much of my portfolio I have in each stock and warns me if it’s too much. This is an investing mistake most investors don’t even think about until they have half their money in a company…that just declared bankruptcy and wiped out their savings.

Automatically Compare Stocks to Buy with the Click of the Mouse
Most portfolio tracking spreadsheets stop there, by listing out your stocks and adding up returns…what good is that? I can get that on any investing platform.

Creating a stock tracker that’s going to help you be a better investor means adding features for analysis.

One of the most popular types of videos on the YouTube channel are comparisons, videos where I compare two stocks against each other, so I knew this would be a great feature to include in the spreadsheet.

Put in two ticker symbols and the spreadsheet will automatically calculate 10 financial ratios on the stocks, everything from the PE ratio to profitability and dividend yield. It’s a great way to make sure you’re investing in only the best companies.

But just because one company is better than another, doesn’t mean it’s the best. That’s why I included another column to calculate the average for each of those 10 financial ratios for the company’s sector. You’ll not only be able to compare the two stocks against each other but against other competitors in their sector.

Find the Risk in Your Portfolio before it Means Losses

While I love the stock comparison tab in the spreadsheet, the portfolio overview tab is easily my favorite. This is where you’re going to be able to see those risks and opportunities in your investments, before it’s too late.

This tab brings in all the information from the Portfolio tab; all the stocks, bonds and other investments you have in your portfolio and put it in ways you can analyze. The spreadsheet first shows you how much of your money you have in the major asset classes like stocks, bonds, real estate and even alternative assets like Bitcoin.

This is a real wakeup call for a lot of investors that have almost everything in stocks. That’s fine for when the market is rising but when that next bear crash comes, you better have some money in bonds and real estate and this spreadsheet will help make sure you do just that.

It also shows you your portfolio breakdown by sector of the economy. This is another wakeup for many investors because most people have way too much of their portfolio in tech stocks. You’ll be able to see exactly how much money you have in stocks of each sector, compare it against the sector weight in the stock market, and make the adjustments you need.

A Spreadsheet that Tells You Exactly How to Reach Your Retirement Goals

I didn’t want just a stock investing spreadsheet though. What’s the use of making money on your investments if you have no idea what you want to do with that money? Money for the sake of money is pointless!

So I added an investing goals tab to the spreadsheet to help me track my portfolio value and retirement goals. I can enter my age, when I want to retire and how much I invest a month and the spreadsheet is going to work some serious magic

Retirement Planning Spreadsheet
First the spreadsheet adds up all the investments in my current portfolio. That’s basic enough but it also takes the historical returns on those investments, my age and retirement goal and tells me exactly how much I’ll have by the time I retire and a monthly income I can expect.

This isn’t just a generic calculation using the long-term stock market return but a customized estimate based on your investments.

If you’re a numbers nerd like me, I love to plan. Probably at least once a month, I’ll sit down to think about how much I need from my investments and how to get there. That’s why I also built in this retirement income calculator.

Estimate your monthly expenses and your tax bracket in retirement and the spreadsheet will calculate how much income you need after taxes and social security. Then it’s going to show you exactly how much you need in investments to make that much money per month.

How to Portfolio Tracker Spreadsheet Works

Lots of features means lots of work went into creating the spreadsheet. Besides the calculations it makes on Excel, it starts by downloading information automatically from the internet.

The spreadsheet automatically downloads,

• Information on each stock in your portfolio including company size, beta, dividend yield, one-year high and low
• The sector and industry for each of your stocks
• 10 financial ratios for any stock in the market as well as the sector averages
• Average returns for stocks and bonds

This is all done through what’s called an API which is computer speak for a code that tells the spreadsheet to find the most current information from a website and put it in a cell.

Anyone can create a stock portfolio spreadsheet but it’s a LOT of coding. I spent three months working 10-20 hours a week developing this one and then testing it out. Then you have to worry about the constant glitches and fixing it. For example, with my spreadsheet, the website where it pulls the information started charging for the API access. That meant I had to pay for a monthly subscription and fix the spreadsheet.

It’s a long process but absolutely critical in tracking your portfolio and making sure it meets your needs. Even after 20 years of investing, I saw risks in my portfolio I had never seen before.

That’s why I wanted to make my spreadsheet available to everyone. Not everyone has the time to create their own portfolio tracker but everyone definitely needs a tool to help them invest.

Bond -Stock Trading course

Learn about Bond -Stock Trading:
Bond trading definition
Bond trading is one way of making profit from fluctuations in the value of corporate or government bonds.
Many view it as an essential part of a diversified trading portfolio, alongside stocks and cash.

A bond is a financial instrument that works by allowing individuals to loan cash to institutions such as governments or companies.
The institution will pay a defined interest rate on the investment for the duration of the bond, and then give the original sum back at the end of the loan’s term.

A stock trader or equity trader or share trader is a person or company involved in trading equity securities.
Stock traders may be an agent, hedger, arbitrageur, speculator, stockbroker.
Such equity trading in large publicly traded companies may be through a stock exchange.
Stock shares in smaller public companies may be bought and sold in over-the-counter (OTC) markets.

Stock traders can trade on their own account, called proprietary trading, or through an agent authorized to buy and sell on the owner’s behalf.
Trading through an agent is usually through a stockbroker. Agents are paid a commission for performing the trade.

Major stock exchanges have market makers who help limit price variation (volatility) by buying and selling a particular company’s shares on their own behalf and also on behalf of other clients.

More Course: BOND – STOCK

Outstanding Course:Trading Techniques Digital by Steven Dux