Lookup most frequently used

Hi! my app has a series of user-generated data (trade type, stock symbol, open/close dates, buy/sell prices, etc.) and I’m wondering if it’s possible to display the most frequently used stock symbol in a summary table? Also, is it possible to display the symbol with the highest calculated profit along with that profit amount?

I can display trade type amounts because there are only 5 to choose from. But since the stock symbol will be different for every user, I’m having trouble figuring out how to display most frequently used per user. Thanks!

Is this the Symbol_Input column in your trades table?

Yes and no :grinning: Yes in that each user fills that out, but no in that I want to display the Symbol_final as it shows all uppercase letters which is the conventional display.

Okay.
And you mentioned that you’d want to display these in a table - sort of like a “Top Ten Symbols” or something like that? I suppose there could ultimately be hundreds or even thousands of these, yes?
I have an idea in my head how this could work, but I just want to ensure I have a clear understanding first.

As for this one…

Which column is that value taken from?

Yes, a “Top 10” would be awesome! OriginallyI was just thinking of the “Top 1” but a list of the top few would be better for the user. Yes, there could be hundreds of symbols.

For the profit per symbol, I don’t have a specified column yet as I didn’t know how to set it up. Right now I have columns for the symbol and profit per trade. But not one for total profit for just one symbol.

Okay, this one is easy. All you need to do is create a self-referencing multi-relation column in your Trades table that links the symbol to itself…

… and then do a rollup through that relation, taking a sum of the profit

Screen Shot 2021-11-23 at 11.59.02 PM

That same relation can also be used to get a count of trades per symbol. But coercing that into a “Top Ten” table requires a little more work. It’s late here, so I’ll think on that one and come back to you with an option tomorrow (unless somebody else jumps in before then).

2 Likes

Thank you! I’ll try this out!

@Trading_5_Talents - okay, I have an option for you, but I have to warn you - it isn’t simple :stuck_out_tongue:

I’m quite sure @Jeff_Hager will come along and show how to do this with about 3 columns. That’s what he usually does :rofl:

Anyway - here it is…

2 Likes

Thank you! I’ll take a look into this weekend and report back. I appreciate your willingness to come up with a solution for this. :+1:

Wow. Still trying to wrap my head around this solution :exploding_head: :rofl:

So it looks like this would be an overall list of most frequent items, which is very useful. In addition to displaying number of trades, I suppose I could rollup the total profits from each ticker symbol?

I guess the follow up question would be is it possible to sort this based on month. For example, the user selects a month and now sees top symbols for that month only with trade numbers and total profits from that symbol.

I’m using elements from your solution along with @Robert_Petitto 's leaderboard video, and I can get as far as listing top ticker symbols with total profits, but those are lifetime profits, not a user-selected month display. That seems to be my next hurdle…

yeah, it’s not exactly one for the feint hearted :wink:
My gut tells me that there should be a simpler approach.

You mean filter by month, as opposed to sort by month, right?

You have 95% of what you need for that in the example I gave you. All you need to do is:

  • Add a user specific column to that Lookup table to hold the value of the selected month
  • Add a single value column to apply that to all rows
  • Create a template column that joins that single value column and the sv/symbol column
  • Make sure you have a similar template in your trades column

And then everything else will work as before, except that it will be filtered by the selected month.