I am working on a simple stock tracker app where the user can enter their trades and it would show the summary of their position. When the user enters the first transaction of a given stock (Apple) then I need to create a row in a positions table for that stock (Apple) where I am going to summarize all of the trades. Is there a way to do this in glide or I need to use something like Zapier?
If you want to create a related record then I can advise using a custom form, but I want to know more about your data structure here.
Do you have a Users table, a Stocks table, a Trades table and a Positions table? How are the Trades and Positions table related?
@ThinhDinh thanks for the reply.
I have one or more APPL trades in the transactions table:
stock_symbol, type, count, transaction_price, transaction_date, total_cost, current_value
APPL, BUY, 10, $60, 2022-01-07, $600, $700
AAPL, BUY 20, $65, 2022-01-08. $1300, $1400
Then in the positions table the AAPL line would be auto-created when the first AAPL transaction entered and it would be basically sum up your AAPL trades:
stock_symbol, total_count, total_cost, current_value
AAPL, 30, $1900, $2100
I am not sure if this needs an extra table or somehow I can create it from the transactions table though. I am open to all suggestions
Looks like you need to record the user’s email/ID here as well, if you’re having multiple users using the app.
Let’s say it will look like this:
email, stock_symbol, type, count, transaction_price, transaction_date, total_cost, current_value
firstname.lastname@example.org, APPL, BUY, 10, $60, 2022-01-07, $600, $700
email@example.com, AAPL, BUY, 20, $65, 2022-01-08. $1300, $1400
Then you create a template column in the same sheet joining the email and the stock_symbol. It should look like:
firstname.lastname@example.org - APPL.
Then create a multiple relation using that template column to itself, so you get all the transactions from the same email and the same stock.
Finally, you can use 3 sum rollups to calculate the total count, cost and current value.
Thanks for the reply. So it seems like I will not need the Positions table at all because the template string with the email@example.com-APPL and the relation will be able to compute the total cost, total count and current value.
I have one last question though. How do I display this data to the user so each stock position is shown only once?
I assume you have a rowID for each transaction.
Then, from the relation I mentioned here.
You add a single value > return first rowID from the relation, you will have the rowID of the first transaction that matches.
Then filter if rowID is first rowID from the single value column above.
This is gold! Thank you for your help.
That is one clean way to have a “unique” list without creating another table
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.