Share market transaction table


#1

Hi can anyone help, I have created a table with share market transactions. Date/Stock Code/Buy/Units/Price that sort of thing. My problem comes when I want to accumulate the units in the same stock code. So if each row is a transaction how do I look back to see if there are units of a particular stock already in existence? Very simple in Excel but I just cant seem to figure out a simple way in Airtables.


#2

Hi there

Not sure about your question. Could you clarify please?
Your records represent only “buy” transactions or “buy” & “sell” transactions?. (In this case I asume that the “Sell” column will have negative numbers, right?)
I ask this because if you have only “buy” records you will always have stock in existence, otherwise I need clarification what you intend with “stock already in existence”.
Rgds


#3

Yes the buy column is actually the transaction type so that could be buy or sell or dividend. I can deal with dividends elsewhere if necessary.


#4

Hi,

Check if this can work for you: https://airtable.com/invite/l?inviteId=inv4NPvA9ovued8pX&inviteToken=8b80ea55122eb7235754947e58c8546f9bde611eaef86f17007e9f28116fb7b3
You add a new transaction on Transaction Table, if Stock Code doesn’t exits, you can create it in the same field.
All the stock inventory will be updated in the Calc Table.

Hope it helps.

Rgds


#5

This is great. I do have another complication in that I also have customers but I think this will work. Am I able to see the calculations?
Many thanks.


#6

Yes, of course.
There are not to many calculations.
“Code” field is a link record to Stock Check Table.
“Total” is a formula “Units” * “Price” fields.
May be the one you need is the rollup formula in “In Stock” field which I attach below.

Any question, be my guest.
Rgds


#7

Hi would you mind if I shared my base which is just based on yours? I have a few issues.


#8

I can get all the information I want by using the group by function in a view but actually I want to use the data in a website so I need to have a field I thing for the API. All I want now is the holdings of each customer. Without duplicated codes.