Mr. Habit marzo 2024 5 '

Mutual fund portfolio tracker spreadsheet

An investment portfolio is nothing more than a set of assets that are selected to follow a strategy, but…

Did you know that the concept of an investment portfolio is something relatively recent?

Although it may seem like a long-established idea, it wasn’t always so.

The idea of an investment portfolio emerged in the late 1960s. As logical as it makes sense now, if you were referring to a diversified investment portfolio in those years, no one would know what it was.

It was Harry Markowitz, winner of the Nobel Prize in Economics, who is credited with developing modern portfolio theory.

Previously, investors focused on maximizing returns by selecting individual stocks. He expanded the approach to include diversification to reduce portfolio volatility and risk.

The idea evolved to create types of portfolios, by asset type, by investor profile, riskier, less risky or balanced portfolios, etc., but in the end, it’s all investment portfolios.

And there are as many investment portfolios as there are people. Yes, you probably follow a particular strategy or investment style (or not), but in the end your portfolio is unique.

That is, there may be many people with the same strategy, but absolutely everyone will have a different portfolio.

◇ Your investment portfolio is unique (or not).

No matter how much you use the same funds from strategy X or Y, whether you follow a permanent portfolio, a Bogle portfolio, the Golden Butterfly po or the Viakaizen portfolio, there are important variables that make all portfolios different from each other.

For 1️⃣ investment strategy there are ♾️ investment portfolios. Among them yours.

Only you know this (or should)

  • the amount you have invested,
  • the exact number of shares,
  • the day you started,
  • when you make transactions,
  • the price of each asset on the day of the transaction,
  • if and when you make recurring purchases, aka DCA (Dollar Cost Averaging),
  • if you adjust the distribution or weight of each asset periodically,
  • if you rebalance the portfolio when the percentage occupied by each fund moves away from the target allocation,
  • whether you sell the fund that has risen the most, or buy the one that has risen the least,
  • etc.

⇨ There are dozens of variables that make your portfolio unique and it becomes essential to have a tracking sheet that allows you to monitor it no matter what strategy you use, what mutual funds you have selected or when you made your first purchase, subscribed to new holdings or readjusted the weights of each asset.

That’s what this post is for. A post focused on the quintessential tool for tracking your portfolio: a spreadsheet to manage and track your mutual funds. The ultimate tool for mutual fund administration and portfolio management in a Google Spreadsheet

But as all that glitters is not gold, tracking spreadsheets often present some fairly common problems.

❌ Problems with templates for managing investment portfolios

If you’ve been looking for templates to track mutual funds, you’ve probably come across tracking sheets in Excel or Google Sheets that at first glance may seem useful.

You decide to try some of them, but it’s not uncommon to run into some drawbacks….

  • Where do you start? If there are instructions, they are not concise and clear.
    • They either look like the Bible,
    • or it looks like they are written by a Martian.
  • It is complicated to understand and comprehend to start using it and make it work quickly.
  • It’s not usable. Mr, that I’m used to the usability of Google services‼️
  • Poor design and styles, unattractive and visual.
  • The learning curve is high.
  • You have to enter dozens of variables.
  • Does not adapt to your strategy.
  • Limited. You can only track a few funds.
  • And if you want to increase the number of funds, the calculations stop working…
  • Or if you start modifying it, you start getting errors all over the place: #N/A, #DIV/0, etc.
  • Extreme.
    • Or it is too simple without any formula protection, error checking, no graphics.
    • Or it’s too complicated and difficult to use if you are not in the mind of the person who made it….
  • It doesn’t follow a logical or common sense pattern of use.
  • And if in the end you decide to modify it or make it yourself with the help of Google and the famous googlefinance function (needed to load the prices, names and data of the funds or stocks), you find out that it does not work with Ticker codes!

❗ At this point is when you start talking to yourself….

Let’s see, I just want to track my funds!

Don’t you think pasting the Ticker and writing down what I buy or sell would be enough to make the tracking sheet work?

The calculations on the back end don’t matter to me!

I just need to enter the code of each fund (Ticker) and the transactions AND the spreadsheet will do the rest!

⁉️ Is it that complicated to make things simple?

Check it out for yourself ⤵️

Go directly to the DEMO

✔️ What a good tracking sheet looks like

To monitor and manage your portfolio you need a template that allows you to track your investment funds, see quickly and in real time the current status of your portfolio, its evolution and profitability.

But it must also meet certain requirements that should come by default in any tool that allows you to manage your investment funds.

Basic requirements

  • Clear, short and concise instructions. Only 2️⃣ steps:
    • Enter the Ticker/s of your funds (only necessary the first time).
    • Enter the transactions you are making (when you buy or sell).
  • Ease of use and Google level design.
  • Help prompts and warning messages.
  • Error control to avoid entering incorrect data.
  • Unlimited funds from the initial 30 by default.
  • Ready to download and use. No configuration required.
  • No need to know formulas, or how Google Sheets or Excel works.
  • But there are no limitations either. If you like to tinker with formulas and Google Sheets, the sheet is open and unrestricted for any changes you want to make.

❓ Is there a spreadsheet for portfolios with mutual funds that meets these requirements and features?

✨ Yes, and with a few more extras

⭐ The template for managing your investment funds

With the Google Sheets format, this tool for tracking your investment funds solves all these problems you have just seen (and some more…).

  • A simple, but very functional spreadsheet.
  • To control your portfolio in real time.
  • Frictionless to use and working from minute one.
  • Useful and usable to facilitate the control of your funds.
  • Robust to accompany you throughout your life as an investor.

Features

Portfolio

From here you will be able to monitor your portfolio for complete tracking.

Just enter your fund code (Ticker) and it will automatically display its name and current price (NAV).

Portfolio

As you make transactions, it is automatically displayed:

  • Average price
  • Shares
  • Invested capital
  • Current valuation
  • Profitability in percent
  • Return in dollars
  • Current distribution and weight

All this data is automatic and is shown for each of the funds and for the entire portfolio.

Transactions

Record every transaction you make. This is where you can enter your purchases and sales.

Transactions

Funds distribution chart

Pie chart to control the distribution and weight of the funds. It also includes a summary to review the most relevant data on the evolution of your portfolio, its valuation and performance (annualized and absolute).

Distribution of mutual funds

Performance

Bar chart to track performance by fund and total returns.

Profitability

Invested capital vs. valued capital chart

Bar chart to compare the invested capital and its current valuation (real time data).

Invested vs. valued capital

Tabs

All these features and functionalities can be controlled with only 3 tabs.

Tabs

Instructions

Summary of how the tracking sheet works: Enter the Ticker and the transactions.

Instructions

Notes

Quick notes as a reminder about the main features of the tracking sheet.

Notes

Help, error prevention and control

In addition to the support notes scattered throughout the spreadsheet to help you navigate through it, you will find help and error control notices.

To prevent any errors (we are human), it is important to have a error control. This will avoid entering incorrect data, typing in a formula, deleting graphs, etc.

Anyway, and although it is not recommended, I remind you that you can also disable the controls.

Ticker Codes

Simply copy and paste the Ticker code of your funds. If the sheet detects any strange characters or you have typed the fund incorrectly, the sheet will alert you to correct it. Validation allows you to track any of the funds available on the Financial Times website.

Ticker code validation
Automatic charts

All charts are created from real-time data and the transactions you make.

Cells are protected for ease of operation.

Cell and chart protection
Transaction date (Calendar)

Select the date of the transaction (purchase or sale).

Transaction date validation, calendar selection
Transaction Date (Manual)

Validation of dates when you enter them manually.

Validation of transaction date format when typed manually
Number of shares

Data validation for tracking the number of shares of each transaction.

Validation of the number of shares
Transaction prices

Validation of the purchase or sale price data of each share.

Validation of each share price
Transaction total

Write protection and data validation to calculate the total amount of each transaction.

Validation of cells, calculations and totals
Reminders and notes

Notes spread over several columns to remind you of the type of data shown in each section.

Help notes

How to use it ⚙️

All you have to do is enter the Ticker code of your funds, your purchases (or sales) and that’s it.

The rest of the data, performance, valuation, charts, etc. are calculated automatically.

Here’s a 2️⃣ step mini tutorial with screenshots to get you started using the spreadheet quickly.

☝️ STEP 1. Enter the Ticker of your funds

You will only have to do this step the first time you open the template to tell the spreadsheet which mutual funds are in your portfolio.

Step 1

✌️ STEP 2 Enter transactions

When you buy (or sell), write it down on the sheet. Simply write down the date, the purchase or sale price and the number of shares in the transaction.

Step 2

And that’s it! You don’t have to do anything else. Just these 2 simple steps. The rest is taken care of by the template.

⚡ Who already uses it

Here you have the latest public purchases (from buymecoffee).

▶ How to get it

If you like the template (DEMO here) and you think it will be useful to track your portfolio and monitor the evolution and performance of your funds, you are one step away from download

This is the offer

It’s free? No. Look…

…Why you should buy it

It’s difficult for you to use it if I give it to you. And you know. Free is not valued. So I want to do you a favor: control your mutual funds from now on, with just one click.

⛔ Either this or continue wasting time looking for free templates that you won’t use. If it’s the case, you can now close the tab and keep trying.

But if you continue here, prepare the card.

For the other versions I could have presented you the template to manage your funds saying that it is valued at $499, but now you have an unrepeatable opportunity and, just for being you, it will be for $199…

This is very old-fashioned. I think that if you are looking for a sheet to follow your investments, you must not be very stupid. So it is better to tell you things clearly and talk to you like an adult.

✔️ The price you see will never go down, only up. That is, you will never get it from anyone after you paying less.

✔️ When the price goes up, which I usually do every so often, you will already have the sheet with you. The sooner you get it, the less you pay, it’s that easy.

PROPLUSBASIC
Design
Transactions
▸ Calculations and formulas



Portfolio
▸ Calculations and formulas



Charts/Graphs
Real-time data
Cell protection
$87.00$57.00$17.00
DownloadDownloadDownload

And if you invest from Europe, in this post you will find the template for European investment funds and its Spanish version.

Alternative with the getquin app

getquin


by Mr Habit

Here I share my best. My goal is to provide you with the best tool to manage and track your mutual funds, the same tool I use for myself. All content is informative based on my experience.

Un señor [aquí ego] que escribe sobre finanzas.