Build a stock portfolio spreadsheet, track your investments

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 initially built around traditional investments, it can be easily modified to accommodate crypto investments.

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

Create a Sheetgo form

First, we will create a simple Sheetgo form to track purchased stocks. These forms can be made quickly from an existing spreadsheet. Afterwards, they will feed data right into that spreadsheet.

1️⃣ Click here to sign up for Sheetgo

2️⃣ Click the Form to Google Sheets card

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.

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 extensive, this process could become repetitive; in that case, you may need to create fake 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 quite powerful; it features numerous functions that 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. 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 that do most 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. Simply provide the ticker and specify the 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 can continue to adapt it to meet 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.

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

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

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.

Next steps

Right off the bat this workflow adds a lot of value. It allows you to spend less time tracking down stock information and more time analyzing your next move. That being said, it can be improved.

These are just some of the few things that could be added or modified:

• Modify the form to include purchases and sales. This would enable you to build a database of all your transactions.
• Add charts to include in your dashboard.
• Convert currencies to show all values in your local currency.

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.