# Fun with spreadsheets: calculating your win% from your bank history

1. Go to your Bank history page and copy the entire history out of it, or as far back as you would like to go.
2. Paste the data into a spreadsheet tab. I’m doing this with Google Sheets, but Microsoft Excel is virtually identical; I haven’t tested these formulae in Excel, so you may need to tweak them if they don’t work.
3. Create Named Ranges for your bank history data; I used “HistoryDescriptions” for the Descriptions column of the bank history data, so you’ll see that used in the formulas below.
4. The Date values copied from the Bank History page are unfortunately not easy to convert from their string format to a known DateTime format that spreadsheets can interpret; I had to do some extra work to do that conversion. That conversions is complicated enough to deserve its own article. I created a Named Range for the converted dates, called “HistoryConvertedDates”.

Here’s an example of what you’ll end up with when finished:

A B C D E F G H I
1 All Time Total Sun Mon Tue Wed Thu Fri Sat
2 Registered 2789 433 371 323 331 410 561 360
3 Unregistered 249 39 30 32 25 47 45 31
4 Refunded 2 0 0 1 1 0 0 0
5 ITM 904 129 131 114 114 127 183 106
6 ITM% 35.62% 32.74% 38.42% 39.31% 37.38% 34.99% 35.47% 32.22%
7 Last 7 Days
8 Tournaments Registered 57 10 22 0 2 1 20 2
9 Tournaments Unregistered 13 0 8 0 0 0 5 0
10 Tournaments Refunded 0 0 0 0 0 0 0 0
11 Tournaments ITM 23 4 10 0 2 0 4 3
12 Tournaments ITM% 52.27% 40.00% 71.43% 0.00% 100.00% 0.00% 26.67% 150.00%

(You’ll notice that my calculated Tournament ITM% for Sat within the last 7 days is 150%; this is likely due to one game starting on Friday just before midnight, and finishing (and paying out to me) on Saturday. This is good enough for my purposes.)

The formula to use in the cells are as follows. I’ll just give the first two columns; the columns for Mon-Sat are identical to Sunday, but with the number of the week day incremented by 1 for each successive weekday:

A B C
1 All Time Total Sun
2 Registered =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Registered to mtt”))) =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Registered to mtt”), WEEKDAY(HistoryConvertedDates) = 1))
3 Unregistered =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Unregistered to mtt”))) =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Unregistered to mtt”), WEEKDAY(HistoryConvertedDates) = 1))
4 Refunded =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Cancelled mtt refund”))) =COUNTA(IF(ISERROR(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Cancelled mtt refund”), WEEKDAY(HistoryConvertedDates) = 1)), , FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Cancelled mtt refund”), WEEKDAY(HistoryConvertedDates) = 1)))
5 ITM =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Prize from mtt”))) =COUNTA(FILTER(HistoryDescriptions, REGEXMATCH(HistoryDescriptions, “Prize from mtt”), WEEKDAY(HistoryConvertedDates) = 1))
6 ITM% =B5/(B2-(B3+B4)) =C5/(C2-(C3+C4))

The above formulas give the stats for the full data set; to do the “Last 7 Days” version of the above, we simply add a filter clause, to filter out dates <= the Date 7 days from Today’s Date:

Add this to the end of your FILTER function calls:

`, HistoryConvertedDates >= (TODAY() - 7)`

Similarly, rather than TODAY()-7, you can subtract 30, 60, 180, 365, or any other number of days, to go as far back as you wish. Or you can use a different value than TODAY() as your starting date, if that’s appropriate for the data you’ve selected.