Build a stock portfolio spreadsheet, track your investments

Staying on top of your investments can be a real challenge, particularly if you manage them through many brokers. In this post we will cover how to track your stock portfolio using spreadsheets and Sheetgo. 

The end result will simplify your process and hopefully your life. This is what you can expect from the end result:

  • Sheetgo forms to create and update the database.
  • A centralized database in a spreadsheet.
  • A dashboard to get insights on your current stocks.

While this system was built around traditional investments, it can easily be modified for crypto investments.

Make a copy of this spreadsheet and follow along. Let’s get into it!

Transform your data into actionable insights

Create a Sheetgo form

First we will build a simple Sheetgo form to keep track of purchased stocks. These forms can be created in no time from an existing spreadsheet. Afterwards they will feed data right into that spreadsheet.

1️⃣ Create a new sheetgo workflow

2️⃣ Select Create form

Stock portfolio spreadsheet 1

3️⃣ Under file, select the spreadsheet and, under tab, choose Response Sheet. Then Click Finish and save.

Stock portfolio spreadsheet 2

That’s it! You’ve created your form. Apart from being created easily, Sheetgo forms offer a simple way to input data from your mobile devices.

This is what your workflow should look like up to now.

Stock portfolio spreadsheet 3

Work on your database

Now it’s time to fill out the spreadsheet, this database should be a good reflection of your investments. 

Just use the form you created and submit it for each stock you hold. If your portfolio is too big this process could get repetitive, in that case you could just fake the entries. This means populating the Response Sheet tab directly in your spreadsheet.

After you’ve built your initial database you have to fill in the form every time you buy new stocks. This will keep your database up to date.

An overview of the rest of the spreadsheet

This spreadsheet is really quite powerful, it has a lot of functions that will automate your work.

The first time you open the copied spreadsheet, you will get a warning message (“ Some formulas are trying to send and receive data from external parties”). This is the GOOGLEFINANCE function trying to retrieve data, just click Allow access for it to work.

Once you’ve populated it, the Portfolio Holdings tab allows you to:

  • Keep track of how much you’ve spent and what your portfolio is worth.
  • Get a quick snapshot of how your investments are performing.
  • View the price trends of all your shares at a glance.
  • See which stocks pay out dividends.

Like any good spreadsheet, there are a few functions doing a lot of the heavy lifting. These are just a  few honorable mentions:

  • GOOGLEFINANCE: the backbone of the spreadsheet, it retrieves financial data from the Google Finance service. All you have to do is provide the ticker and specify which data you need.
  • SPARKLINE: it creates graphs within cells in your spreadsheet, great for seeing trends and how a value changes over time.
  • ARRAYFORMULA: enables you to use functions across ranges. A must have tool to build spreadsheets that adapt to newly created rows. For this stock portfolio tracker, it automatically applies all formulas to any new shares.

This spreadsheet is a great starting point for portfolio tracking, you could keep adapting it to fit your specific needs.

Craft charts and display them in a dashboard

Finally let’s build a dashboard to get an overview of your stocks. For this we will leverage another Sheetgo function that lets you group all charts in a spreadsheet.

As much as we love spreadsheets we know they can be difficult to work with on the move. And much like forms, Sheetgo Dashboards are a great way to stay connected with your investments from a mobile device.

As if that wasn’t enough, dashboards can be shared easily. This could come in handy if you are managing other people’s accounts and have to give them visibility.

For this guide, we will use existing charts within the spreadsheet.

1️⃣ Go to the workflow you’ve already created, click on the “+” on the left side-bar.

Stock portfolio spreadsheet 4

2️⃣ Select the charts you want to include in your dashboard. For this example, we will select them all.

Stock portfolio spreadsheet 5

3️⃣ That’s it! Now just sit back and (hopefully) watch those profits grow.

Stock portfolio spreadsheet 6

If you build any charts of your own, simply repeat these steps to include them in your dashboard.

You can also add text to give context for all your charts,  you could even divide them into subcategories.

Never lose sight of your investments

That’s how you can track your stock portfolio with spreadsheets, and how you can make it so much better with Sheetgo. Hopefully this will help you on your trading journey.

Shoutout to Utsav from our solutions team who built this and shared it in our community.

inventory-spreadsheet
Enhance your productivity:

Automate your

financial

management

You may also like…