Need Help, Calculate Poker, all Stake Profit by Catagory.

Here is an updated version of the file with the input table moved.
It appears that all formulas on the bankroll sheet work.
Please make changes any to the bankroll sheet as you are able and then post some specific issues with which you need help.

thank you, mate
but looks so different now. also I don't see any balance in the total bankroll.
why is it better this way? I was so use to the other maybe I feel a bit different.
would it be, better having all the totals in a whole different workbook? maybe like that I can see it and have both open at the same time ?
mate is this something we can workon? would want a breakdown of every site, maybe have the main two sheets completely separate from the sites sheets. input and bankroll in one workbook and all others in one workbook. would like to hear your your thoughts on this.

To get Total Bankroll paste the following into cell B9: =(SUM(Input!P2:P2000)+SUM(X12:X5992)+SUM(AB12:AB5992))
Using two workbooks will make things harder.
My thought is to get the bankroll sheet arranged and working the way you want and then ask about help with the Site sheets.

Cells B8:C8 are merged, the formula is in cell B8 and should read:

Formula:

Please Login or Register  to view this content.

Don't know which 'old version' is being referred to.
Here is the file I have with the formula applied.
If the file has been changed then please upload as screenshots are not helpful.

Why are we discussing that file in post #10? I thought that you had moved on to the file in post #9.
As to post #11, have you made an attempt to move the Cash section? If you move the cash section and it messes something up, then you can press the Ctrl and z keys to reverse that action.
You need to start attempting to do some of this yourself and then let us know if you run into an issue you can not resolve.

In the ribbon at the top of the sheet there should be an undo arrow.
Don't press the left pointing arrow itself but rather the dropdown arrow.
Read the list of actions that can be undone and see if deleting the sheet is on there.
If so highlight all actions down to deleting the sheet and click the mouse.
Another option would be to copy the deleted sheet from an earlier version, select the new sheet tab (+) and then in paste the sheet into the current version.
You also could exit the file without saving which should bring back the deleted sheet although it will also not save any changes that you have made since the last time the workbook was saved.

thank you, mate your a life saver. let me look. for the solution. Yes I also did a google and sugested not to saved, the file but that wouldn't work for me. ill look into it. ty
mate, I also notice that some of the Catagorys stop working, I think when the table was move.

Mate, please see updates. help me make this run smooth.

Please tell us specifically what you need help with i.e. which cell on which sheet and unless the calculation is obvious tell us what the manually calculated value the cell should display and why.

happy morning mate, I would like to start with the player list not working in all games.
after the table was moved, I notice some of the catagorys not working.
1. Flip & Go
2. Satellites
3. Spin & Go
4. Sit & Go
5. Cash Games
Also column E on the Bankroll is showing #REF! not sure if that column can be deleted. I try deleting and had to reverse with Ctrl and Z because the values on column D delete.

Mate, Also columns A,B,C,D,E on all Sites or hidden for all transactions
gg, party, pokerstars, PS.es, 888, Acr, Winamax, Redstar, Global, and Betonline

I hope that you understand that the purpose of the FREE forums on this site is to help with specific issues and not to produce entire workbooks. There are probably contributors on the Commercial Services forum here that would do that.
If you want to go at this one issue at a time, that is your choice, however it may take a considerable amount of time.
As to the player list not working in all games, what is the value that should appear in cell Y2?

It would have been more useful to give the dollar amount that cell Y2 should display.
Nevertheless, this formula seems to work:

Formula:

Please Login or Register  to view this content.

Why do you say the flip & go category isn't working?
Have you included an example of a flip & go game in the all games column with which to test?

Thank You for asking a specific question.
1. Conver the Cash Games range to an Excel table tbl_CashGames
2. For the cash sessions: =COUNT(tbl_CashGames[@Date])
3. For hours played: =SUM(tbl_CashGames[Hrs])
4. For current month hours: =SUMIFS(tbl_CashGames[Hrs],tbl_CashGames[Date], ">="&EOMONTH(TODAY(),-1)+1, tbl_CashGames[Date],"<="&EOMONTH(TODAY(),0))
5. For total cash profit: =SUM(tbl_CashGames[Profit])
6. For cash ROI: =-AM6/AM5

Your welcome mate, thank you. will see changes.

Column E in Bankroll sheet. showing #REF!, why is it displaying #REF! I can't delete the column.
it would delete D amount.

Column E displays #REF! because the formula inside that populates it is =#REF!-#REF!
I don't remember what column E is supposed to subtract nor from what; however, it can be deleted as shown in the attached file.
Should we assume that there are 10 sites, GG through BetOnline?
As to how to include other sites my thought would be that each site would have its own input sheet, using the All Games sheet as the template. However, due to the size this workbook has become and the number of rows being added daily, it would require a lot of memory. So with that said I feel the question in post #26 is something that other contributors here who have more experience in doing projects on the scale of which I feel you are asking about would be better equipped on which to comment.

You're the only one that can decide whether or not this project is worth the time and effort you will need to put into completing.
I will ask this, how did you keep up with the information before? What is the essential information you need to keep up with?

You're right mate but is that I spend lots of time and I see many corrections that need to be made. and on some we need to repead to fixed. like the bankroll total is not showing and things like that.
I didn't the website were I stake records some of it. All that I have in the workbook to be able to analize and do better in staking. keep track of players, stake where they winning an where I am more profiable.

The three pivot tables in the attached file display the stake of each player, the amount of profit each player has generated broken down to games and the amount of profit generated by each game.
The first pivot table is sorted in descending order based on the amount of total stake.
Note that the total stake is displayed using a calculated field: ='$ Stake ' +'¥ Stake' *0.14
The second and third pivot tables are sorted in descending order based on the amount of profit.

Thank you, Mate I apreciate you doing this for me. will download now, and see the changes. Wow mate that looks very nice and organize your awsome
cheers but I fee down, but this makes me really feel better. That looks neat, Mate.
how would something like this work with all other sites?
Have a question why on the Pivot tables it won't show all games. Example Allinwin has 85 games in All Games tab, but in pivot tables only shows 33 games.
Just firugre it out why the Number of games was off., I also had to correct and remove all spaces in the All games column B, to give the correct amounts in the Sum of profit in the 3rd pivot table.

I would like to ask, why 3 pivot tables? would like to know the stragedy to it. Not sure if one would give all the results and do the same? in a single one.
Also it would give me more of an Idea of how to used them.

Mate, correct me if im wrong. but I think we can eliminate two columns on the All games S and T , the amounts given its the amount of the actual
buying for the entry of the tournament, which I don't pay I only buy a share by the tournament having the amount on the name I can see how much was the total buyin. unless is been use to give the total profit by stake. because in the pivot table 1 it shows the amt. for player speed_racer $2,077 but what I have bought its $493.39 I am not sure if we can remove from the all games.

Hi, Happy to see, you. I feel a little better now. Change its good! have different plan, because things weren't working out.

We will succeed in the long run. Together thru the good and bad. Rain or Shine!!! All up in it to Win it mate. We gonna go for
all the Marbles. I need you in my Team tho. You In it to Win it too!

Glad that you made post #33 as I hadn't seen the edit to post #32 because we only get notifications of new posts.
As to showing 85 games for Allinwin7: Include the Date Time field in pivot table 2.
As to why 3 pivot tables: It seems to me that each pivot table produces an output related to one of the stated needs in post #30.
Pivot tables 2 and 3 could probably be combined as shown on the Combined Pivot Tables 1 and 2 sheet. To me it seems harder understand the intent but that's just me.
See pivot table 1 and also the new pivot table to see if the values in the Sum of Buy-in field are what you want before deleting columns S:T on the All Games sheet.
A benefit of using pivot tables is that all of the data could likely be placed in the data model providing all the sites put their data in the same format. A data model can accommodate many times the number of records as a normal Excel sheet.
Let us know if you have any questions.

Im glad you saw it okay. cool whatever woks best. you know best at how all this things work better, when dealing with lots of data, and how to sort to be analize. thank you. So you mean now I can input from all the other 9 sites? in the same All Games sheet? will download see the changes.
Yes the Value amounts seem to be correct. So I can now delete the two columns in all games? S and T? or are they needed for the pivot tables?
What I really would like, is a suggested stake amount buying in the workbook, to strickly follow based on the Bankrolll money to be stake, and not go to wild when staking, because thats where I loose my bankroll something that would give me an Idea of how much percentange to stake base on what I have or what share should I be buying base on the money in bakroll, If bankroll grows buyin share increase if bankroll drops down, I decrease my buying share. that would really help. because I loose control and bankroll gets wiped out. I am looking into how to figure it out, also how many players should I be staking at once. or which tournaments should I stake micro, low, medium, or high due to variation of the game. I am seeing its maybe in the micro lows where I should be.

If you will add some sample input from the other 9 sites then we can attempt to put it into the data model produce some pivot tables like the ones in the attached file.
Yes, columns S:T can be removed as they don't feed into the pivot tables. The player list table could also be removed because pivot table 2 (Pivot Tables sheet) can be sorted and collapsed to display the same output.

Let me work with this before you add any others.

Here is what I have so far:
1. Added tbl_TournamentTotals to the data model
2. Produced three pivot tables
Note that the slicer above the middle pivot table is connected to all three pivot tables.
Please add a sample of the data from another site to the Input 2 sheet so that I can try show you the steps needed to append the data model.

Thank you mate, Okay, Let me see the changes, and will work on inputing other site torneys.

Trying to set this up so that you can append the Input Table (All Games) with the new data and not have 10 different input sheets.
I am finding some issues in the Share column (G) like entries such as 1%$.
Use the filter/sort button in the Share column header (cell G1) and see what I mean.
Once those have been cleaned up/corrected, then upload the file again.
Edit: is the Share column even necessary?
If the pivot tables are providing the needed information, then it appears that only the only necessary columns are those shown in the PartyPoker records as profit could be calculated.

Hi, mate happy morning
You mean apend the table all games into input2. yes the share is the % I buy I would like to have that. will check now.
all stats in table, I would like to have.

Mate, I corrected the % in the column

Where do i need to do the inputing? input or input 2?

Give me some time to work on this before you add anything else.

This is the best I could to make it easy to add new information to the pivot tables.
You can add to either the input table or the party poker table.
Note that it doesn't matter what site the records actually come from.
Once you have updated either of the tables then select the tbl_Output query, right click and select refresh.
Note that you may have to add columns to the slicer at the top of the Pivot Tables sheet.
The advanced editor code is:

Please Login or Register  to view this content.

Note that I have tried to set this up so that you should not need to make any changes to the code nor make changes to either the query or data model.
Let us know if you have any questions.

Thank you, Mate will see all the changes, and how everything works due to having to tables for gg and party poker, but why two tables to input to either one? will download and see. But the party poker table dosen't have all the stats that we have in the input table.
wouldn't know how everything else would work with all the other sites. It was a big change from the original we work on before to this new version.
Can we make it only one input? if it don't matter where the results come from?

Can you tell me where I go to see where I need to input that code for changes? I found the Advanced Editor tbl_input but I woun'nd know what to do there.

Mate, the table output to refresh would be the Pivot table? but why not auto refresh its that not possible?

Thinking about this a bit more, it will probably be better to just add records to the party poker table if the data from the other sites also only contains the All Games, Date Time, Buy-in, Prize, Site and Player columns.
You can input new data into the Input table if you are doing it manually and want the other columns to calculate, although the pivot tables don't need any other data other than profit which has already been added to the party poker table.
As to changing the code, you could ask some of the other contributors for help as there are many of them that know more about power query and power pivot than I do. I mainly deal with formulas.

Thank you, mate your not the only one I feel the same or worst brain weary and all too.
I though the same its best to used maybe the Party Poker too. But I want all the stats that are in Input. to be in either one.
Yes lets think a bit more of how can this be better. mean while I want you took take a look at this workbook this one should be easy to acomplish since mostly require formulas what your best at. this is for tracking my play live.

It was hard to find to upload. mate, I have my desktop full of copys of sheets we been working on hundrends bunch I have make folders few times to put them in since we been working on this and not 1 is complete they are all half or some near and some not. I see them in my sleep
please tell me what you think about this one. I try to remove all other tabs. This would only be for live tournaments and cash I play when I go to either of
the Casinos in the Catagorys The Commerce, The Bycycle, The Gardens, Lucky Lady and Hustler. but since they have series that come to either casino
I put Mian Events. Like right now they Have a big event at The Bycycle and since I don't know how to make it or combine it with the Bycycle I put in the Main Events Catagory but I would of like it to be in the same with the Bicycle.
bicy.png

Thats the Schedule for the Series. How could that be combine with The Bicycle Catagory I mean by having it go to that catagory but have the daily tournaments apart from the Series. Mate don't think to hard right now your wearout just look and the workbook

I saw the file attached to post #54.
Are there any issues/questions that need addressing?

Hi, mate I was asking about the one in post #52
yes post #54 is still incomplete regarding all other sites, and which template would I be using hopefully only 1 for inputing.
I wanted see if we can make all stats in post#52 work accordinly

I wanted see if we can make all stats in post#52 work accordinly

Please ask for help with specific issues.

Thats the Schedule for the Series. How could that be combine with The Bicycle Catagory I mean by having it go to that catagory but have the daily tournaments apart from the Series.

I am guessing that the above is what you want. I am beginning to feel that rather than populating the Catagory column with an increasingly complicated formula, it would be better to use drop downs.
I suggest that you list all of the categories that correspond to specific casino/all games combinations.

Mate, I just got home from The Gardens, Best Play in LA. this is everything that happen today at the Gardens. im doing the inputs i will upload. now

Okay, Mate so you can understand better and we don't get confused. Its only 6 Casinos here where I play is
1. The commerce
2. The Bycycle
3. The Gardens
4. Hollywod Park
5. Lucky Lady
6. Hustler
The Cash where I can played cash for any that I am in. but I only have one Catagory. can it be broken down to Casino to where I choose in which casino I am and give total of individual per casino. Same for Satellites it can be in any casino, also Main events it can be where ever they have a series right now it would be the bicycle but next it can be the Gardens would like to have a breakdown from main events separate from regular tournaments, like the ones I play today.

Mate, Tell how would the Dropdown work. accordinly to see if it will work. The good thing here is that I can only be in 1 casino at a time or table either tournament or Sit & go, cash, Satellite or playing a Series main event tournament. which would be any in the picture for the series at the Bicycle like that have right now.

The duration formula (again) is: =L18-TIMEVALUE(RIGHT(K18,5))
The $/Hr formula is: =Z18*(R18+1)/(M18*24)
I don't see a cell that indicates Tournaments played at the Bicycle only Turbo Hypers. You need to provide specific cell references.
Also, as requested before, please do not ask additional questions until the first one has been answered.

Hi, Mate At the gardens, It shows the old formulas for Hypers played I didn't change the names so you could of see what formulas where in the cells. I will change and re-upload with the correct names.

I made changes to the catagorys

Mate, can it be a better formula to column M and Y for duration/$/Hr wouldn't want to have value! showing in the column down.
Can you write a formula for Rebuy to calculate $130 + $130 for buying would be total of -$260 in profit total
also the Total starting from Total Bankroll and down to row 8 not displaying anything.

I also have entries in the cash section mate.

Not sure if the amt. is correct for column y row 19

To get the monthly profits working, the values in the Date Time from text to numbers which are formatted [$-en-US]mmmm d, hh:mm and font size is set to 8 so as to not have to change the width of column K.
I can remember previously advising that date times should be entered with the date, a space and then the time i.e. 3/16/23 11:47
As to cell I8, there don't appear to be any withdrawals so the formula just sums the deposit column.
As to cell Q7, please remind how ITM% should be manually calculated, so that we can attempt to write a formula to match.
As to cell Y19, if the profit in 30 minutes is $325 then the $/hr would be twice that ($650).
Look at the rest of the cells and let us know if you have any questions.

Mate in the $/hr if it is 30 minutes why is it $650 shouldn't it be less its not 1 hour is 1/2 hr? can we get Total bankroll formula ?

No my bad this seem correct. disregard mate

Mate, I don't see the time working I enter 3/17/23 7:16 enter and i get this 3/17/2023
Okay I change the format now it works but I will change the font size easier to just drag the filled down

Mate, here is the updated workbook.
I re-upload this workbook, which I made changes to. I try my Best to figure the totals in columns G, H, and I
I know you help me a bunch sorry I mean a Ton, and i really apreciate all of it. I know you try to simplify everything for me to be able to make changes or to workaround it which I Try my best, but I no Nothing i mean Nada about excel but I am learning a bit, if it wasen't for you I would give up on this long ago. and I thank you again for all your support. I cant figure out the current Month sit & go profit it give me the same as tourney I try changing the name from tournament to sit&go its not working, either is satellite. I did fixed some issues which I am very proud well I think, You need to approve them its about the whole transaction and running balance. But it may need some adjustments maybe. total Bankroll its the acutual cashed out from 3/16/23 @ the Gardens. in the cash i do see Session not recorded. Mate its getting a bit late. will upload so you can see all changes.

Mate, we can always go back to the previous version. since this is a copy, if you think is best.

The issue with most of the formulas was that they were looking in the Date Time column for a Category.
I corrected the formulas in cells T3, T10, I12:I13 and I15.
I feel that from there you can make changes to the other formulas in the Sit & Go and Satellite areas.

Mate, Im having issues with the the formulas in thoses cells you mention in #72 T3, T10, T3 is not displaying the amt. revenue, I try cloning the formulas in the gardens but its not working. Satellite also not displaying the amt. for Revenue and cash I add the time but session not recording 1 session. Just to many things that are to hard for me to figure out, I cant mate.

Mate, I update workbook. Please see changes, I need help with the yellow highlight fills. I made in the cells.

In the attached file the following have been updated: I3:I6, Q4, T4:T8, W3 and Z3:AB14.
Note that I guessed at T7 as I don't know what was in column F of the original file and no manually calculated value was given to help us with writing a formula that would replicate that value.
As to the other yellow cells, we need to know the expected values (manually calculated) based on the data in tbl_TournamentTotals.
tbl_TournamentTotals should only have rows that are filled with data. Empty rows in tbl_TournamentTotals will cause some calculations to fail (i.e. the SUMPRODUCT formulas in I3:I5).
As to post #76, that formula has become too complex. Please refer to the suggestion in post #58.

thank you Mate, Okay I will Check T7. I will work on that right now. Okay I will remove the empty rows. Yes it is complex do to been so long and many catagorys no longer are needed. okay will download and check the changes.
Mate in column O row 18 the complex formula is there? is that needed? For cash How come it won't show in column O row 19?

Mate, the Sit & Go amt. for profit was already minus in the profit. prize was $451 profit $326 but then it was taken out again for cell T6 $125
T6 column profit should be $326

Change the formula for cell T4 to read:

Formula:

Please Login or Register  to view this content.

There is no "Cash" category, only a "Cash" All Games, so the formula for "Cash" differs.
If you put the word "Cash" into cell O19 then you could change the formula in cell I5 to read:

Formula:

Please Login or Register  to view this content.

Note that I have the 2019 version and #SPILL errors appear in the later versions, so you may have to reach out to other contributors if that occurrence persists.

Here is the file with the formula applied in cell I5 and "Cash" in cell O19.

Mate, Is the Staking workbook not possible to combine all sites?

Mate in the Profit column I 7 how can it be only tournaments profit? nothing about cash. since the bottom stats will be ROI, Buyin, Roi, $/hr and $/tournament only for tournaments.

Did you see picture about the Cash Games Section, only showing 1 for session

Playing live its going to generate money for staking, you also in it. I don't know anything of excel and takes alot of my time. I am also doing school which I will finished by the end of this year maybe sooner and we can go wider on everything.

As to combining all sites, I don't know if it is possible or not. Please show an example of what you want with manually populated results.
As to cell I7: =SUMIFS(tbl_TournamentTotals[Profit],tbl_TournamentTotals[Category],"Tournament")
As to the cash games section showing 1, the only category "Cash" that I see is in row 19.
As to posts #85:#86 I am sure that it is a generous offer however I am not interested.

thank you, mate. I will okay. will download and see changes.
mate, can we get the Cash Games results here.

cccc.png

Mate, I input 1 tournament for Lucky Lady, can you tell me how do I configure Lucky Lady and all the others Casinos to get the tournaments amounts. also Profit I7 not adding lucky lady tournament. here is workbook.

Addressed issues in above.

Mate, But in Lucky Lady Catagory is not showing the entri i have in Row 21.

her.png

Select cell Q3:Q6 and press the Ctrl and c keys.
Select cell O10 and press the Ctrl and v keys.
The same will work for the other casinos.

Mate, I try copying but it shows the tournaments from the gardens? it repeats every casino should have its own tournaments will input a few for others 1 for each of the others that i don't have in the all games

I fixed issue the name was not been completely highlight due to having two columns in the casino name but its calculating now

Mate, can you write formula for Bounty amount to calculated as money won for column U,
can you look at Satellites its not working as the other catagorys.

think the formula is showing date
=-SUMIFS(tbl_TournamentTotals[Buy-in],tbl_TournamentTotals[Date Time],"satellite")

Mate, the Current month cash profit i17 is displaying the totals of Column AA

My suggestion for satellites is to put a drop down next to the word Satellite.
The drop down source is from =Info!$F$3:$F$8
The formulas in that area are then modified to include the casino name i.e. Satellites played is: =COUNTIFS(tbl_TournamentTotals[Category],"satellite",tbl_TournamentTotals[Casino],T9)
Follow that same procedure for the Main Events area.
Cell I17 should now be showing the correct amount.
I don't understand how the Bounty column is calculated. Please explain in detail how the values in U23:U25 were derived.

Mate, Can you add Main events to have the same as satellites the dropdown to separate the tournaments that are main event.
Also the Prize money not showing in total revenue for satellites

can you write formula for Bounty amount to calculated as money won for column U

Where is the amount supposed to be displayed?
As to post #104, The drop down source is from =Info!$F$3:$F$8. I feel that you can do that. Also there is no prize for satellite given in the latest file.
The formulas for cash yearly stats are basically the same as those for tournament yearly stats. I feel that you can write those.
I filled in the formulas for Tourney Profit by Casino. You can use those formulas to write those for those for the Cash Profit by Casino.

Mate, I added some cash games but when I input the buying or prize it calculates in Tourney Yearly stats for March column z5
it should reflect in column AE for the cash yearly stats along with games played and rebuys.

Still not sure that I understand about bounty, however if you want to list both prize and bounty amounts in the Tourney Revenue cells then here is an example for the Hustler casino in cell Q11:

Formula:

Please Login or Register  to view this content.

I don't understand what post #108 means. The cells in the Casino column (O) already use the sites on the info sheet as a source for the drop downs.
Fixed the Tourney Yearly Stats area.

The Profit column formula could be: =IF(R18="","",SUM(T18:U18)-R18*(S18+1))
As for the drop-down list, assuming that the latest version of the workbook is the one in post #54, if you are adding a casino column to the input sheet then yes, set up a list of casinos and use data validation drop-downs. If you want to filter the pivot tables by casino then I suggest using a slicer.

Mate, Here is the updated copy
Column I row 21 satellite profit not showing the profit.
Can you tell me whats the formula for the Largest wins tournament and, Cash wins, would be.
and if you have some time, I would like to get started on the Finishes stats along with the Yearly Profit Charts. Thank you mate

let me know about the bounties.

As to post #113, where do you want to add a drop down?
As to post #114, it might help if you would tell us what cell(s) should reflect the bounty and what the value should be based on the data in the bounty column.
As to post #115, the formula was written to go into the Profits column as modeled in the file attached to post #112.
As to cell I21, the formula is summing column tbl_TournamentTotals[Mood] instead of column tbl_TournamentTotals[Profit].
As to the rest of post #116, I feel it is better to wait until the rest of the issues listed above have been resolved before starting to work on something new.

Mate, I reupload and made the change for I21 working now. I will give you and example of how bounty works.

Mate, here is and example in this workbook of how it works. under column G for KO/bounties you can see in some he cashes $ + bounty $
and in others just Bounty money.

Should profit be added to bounty in order to determine largest wins?

I tried to a couple of ways to write a formula for the largest wins columns using the functions available in the 2019 version.
Finally had to add a column, Prize+Bounty (AC), to tbl_TournamentTotals and reference the new column in the largest wins columns.

Mate,
The bounty money should have been added to profit. Instead of showing -200 in AA row 28
maybe the column was not needed then.
Prize and bounty. should add up in profit. the way prize reflect in everything else so as bounty should. its just a prize same as column T
I don't think column AC would be needed since bounty should reflect on Profit column along with prize if is won one.