- Go to your Bank history page and copy the entire history out of it, or as far back as you would like to go.
- 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.
- 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.
- 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.