Summary/aggregation of records of a linked table

When I use Aritable I either get the solution straight away or spend days knocking my head against the wall just to find out I’m overcomplicating things. I’m currently in the latter situation, so please bear with me :wink:

I’m designing a Crypto Portfolio Tracker on Airtable.

I have 2 tables:

COINS

TRANSACTIONS

I plan to add every transaction I make (buy or sell) to the TRANSACTIONS table. If amount is positive it’s a BUY, if it’s negative it’s a SELL.

So far, so good.

Now I’m trying to create a PORTFOLIO table summarising/aggregating of all my transactions to reflect my current portfolio.

This PORTFOLIO table should aggregate (sum or average) all records with the same {Ticker} to give me information about how much of each coin I have.

So a PORTFOLIO table would look like:

Ticker | Total Amount | Cost Basis | Current Price | P/L

Where:

Ticker - {Ticker} from the Transactions table. The transactions table will have multiple entries with the same ticker, but I’d like to show just one of each with calculated values.

Total Amount - Calculation (sum) of every {Amount}. that has the same {Ticker}.

Cost Basis - Average of all the {Price} on the transaction able.

Current Price - I will update that manually (or later integrating some sort of API).

P/L - Profit and loss, formula using Cost Basis, Total Amount and Current Price.

Really appreciate any hints that would point me to the right direction.

Thanks!

Is there a reason why you need a separate [Portfolio] table? Since [Transactions] are already linked to [Coins], and your portfolio table would be summarizing transactions based on the ticker, which is the primary field of [Coins], then you could do all your summarizing from within the [Coins] table using Rollup-type fields.