Hi Mike!
In this case, it’s best to create a table to run this calculation where you can specify a start TradeTime and end TradeTime then calculate the % of trades won.
I created an example for you here: Community #2633 - Grist
I have a table of Trades with TradeTime and a column called Won? to specify if the trade was won or not.
I have a summary table, grouped by TradeTime to show how many trades at that time, how many wins and then win %
Then I have a table called Win Calculations which is what you’ll want to create.
You have two columns to enter data in; TradeTime START and TradeTime END. This is where you’d enter those TradeTimes for your window to calculate the win rate.
You do not need to add the columns Trades in Trade Window and Wins in Trade Window. I added these to help explain the formula in the Win % column.
The formula in the Win % column is:
all_trades = Trades.all
window = []
wins = []
# Find all trades in trade window
for r in all_trades:
if $TradeTime_END >= r.TradeTime >= $TradeTime_START:
window.append(r)
# Find all trades that were won in trade window
for r in all_trades:
if $TradeTime_END >= r.TradeTime >= $TradeTime_START and r.Won_ == True:
wins.append(r)
# Calculate % wins
return len(wins)/len(window)
We will break this down into chunks.
all_trades = Trades.all
window = []
wins = []
First, we find all records in the Trades table and assign this list of records to the variable all_trades.
Next, we create an empty list and assign this empty list to the variable window. We create a second empty list and assign this list to the variable wins. We will add records to these empty lists based on criteria given in the next steps
for r in all_trades:
if $TradeTime_END >= r.TradeTime >= $TradeTime_START:
window.append(r)
We need to find all trades that occur within the trade window. For each record in our list from the Trades table (assigned to the variable all_trades in the first step), we check to see if the TradeTime for that record is less than or equal to the time selected as our TradeTime END but greater than or equal to the time selected as our TradeTime START. If it falls in this given window, we will add it to the list assigned to the variable window.
After this for loop runs, we’ll have a list of all records where the TradeTime falls within the given Start and End window.
The Trades in Trade Window column shows the number of records in this list. For my example, 23 records falls within this window. We can confirm this by selecting those counts in the summary table and see that there are 23 records for the 4 TradeTimes that fall within that range.
for r in all_trades:
if $TradeTime_END >= r.TradeTime >= $TradeTime_START and r.Won_ == True:
wins.append(r)
Now, we need to find all trades that were won within the trade window. We do the same calculation to see if the record’s TradeTime falls within the given window but now we also check to see if the trade was won by adding and r.Won_ == True. If it falls in the given window and it was won, we will add it to the list assigned to the variable won.
The Wins in Trade Window column shows the number of records in this list. For my example, 2 trades were won that fall within this window. We can confirm this by selecting those counts in the summary table and see that there are 2 wins for the 4 TradeTimes that fall within that range.
return len(wins)/len(window)
Last, we do our calculation to find win percents. len() counts the number of records in a list. So we find the number of records in our wins list (2) and our window list (23) then divide the two, giving us a Win % of 9%.
Please let me know if you have any follow up questions!
Thanks,
Natalie
