I am trying to convert portion of pokerstake file to nearly same as pokertake platform

Hi, would like help converting file to be as close to the same as pokerstake platform for keeping records of stake to be exact when input in file
4-22-24 pokerstake
in book1 is a sample of 30 games when i donwnload from pokerstake platform that i convert to query
I then copy paste the games from query table to file 4-22-24 pokertake file.
i notice a few errors in rows 23, 24 due to having the same time where i made a deposit from 12.52 and during the same time i stake a player which duplicates the deposit.
in column M and N i would like to have that combine like it shows in this picture from the pokerstake site. showing the negative amts. and the positive, as shown in picture.
please feel free to ask any question.

For deposits paste the following into cell H7 and copy down:

Formula:

Please Login or Register  to view this content.

thank you mate. will copy and paste now.
nice that clear the duplicate. tydup.png
would i be able to enter all deposits and withdrawals from pokerstake sheet, then have them reflect on stake bankroll? or how is that going to work.
the reason i ask is because the csv file i donload i download as all results which includes deposit, withdrawals and stake amt. and winnings. everything

Again, we don't get notifications when you edit a post.
On 4/23/2024 at 3:50 PM we got the following message: "thank you mate. will copy and paste now."
Apparently the post was edited 23 minutes later. A better idea would be to wait the extra 23 minutes and make the whole post, so that you are not waiting 2 days for a response.
As to the question.
The date, transaction and amount columns can be filled using formulas like:

Formula:

Please Login or Register  to view this content.

Assuming that only site from which these transactions come is poker stake use the following to fill the Site column: =IF([@Date]="","","Poker Stake")

I was thinking if we can work a bit on book1 to clean a bit the file after i convert to query.
as you can see the games come with [ ] [ Monday Saver $50 [Hyper] ] that returned #N/A in file
and maybe have the - + combine in one column for when i paste to file

I am a bit confuse i been trying to figure out the entries for deposits and withdrawals and not sure how to do it, since both places have formulas for amt.
D column in pokerstake bankroll and pokerstake sheet in colunn H
i thought it would be all done from pokerstake sheet to reflect on stake bankroll sheet all transactions?

Do you mean that #N/A is returned in the category column of the 4-22-24 pokerstake (west 123).xlsb file?
If so, what do you want returned in the category column?
If not where is the #N/A being returned and what do you want there?
To combine the percentage and value columns in Book 1 put the following into another column (such as column K): =(LEFT([@percentage],1)&[@value])+0

In category column would like the game from list sheet
for row 8 hyper
row 9 speed racer
row 10 speed racer and so on
thank you, will apply the formula in book1 with the following formula. brb
after putting formula in K column the #'s change a bit from 4-22-24 pokerstake file
-156.72
133.94
18.23
13.43
from file
$157.72
$131.94
$15.23
$10.43

There isn't any consistency in for row 8 hyper, row 9 speed racer and row 10 speed racer.
hyper comes from inside the inner square brackets while speed racer is only part of what is inside the out square brackets.
Put the tournaments in the table on the List sheet, remove the duplicates and then fill in the categories.
As for the amounts I get the ones listed under "from file" in post #9.

thank you in this file I put 100 Games 4-25-24 pokerstake, to show for Example of how things work from deposits withdrawals, and canceled withdrawals. shows all deposit except the one for 100 for row 53 Apr 24 16:52
Book100 I put formula in column K and copy paste all in file 4-25-24 pokerstake
In file
wow Fantastic mate thank you. just saw this in stake bankroll sheet all transactions.png
note:
deposit in row 53 for $100 Apr 24 16:52 is not showing, and cell G79 i had to type word withdrawal from deposit that was showing.
in book100 rows 73, 74, 75 shows a bit different than in stake bankroll
I had withdrawal 157.72 then i canceled and withdrawal only -100 leaving 57.72 I re-stake then deposit $25 4/22/2024 20:20

Don't know how the formulas in A8:B8 and D8 on the stake bankroll sheet got messed up, however if that happens in the future select cells A6:D6 and double click the fill handle to reset them.
Perhaps modifying the formula in the category column on the poker stake sheet will work better: =IF([@Tournaments]="",[@Status],INDEX(Append1[Category],MATCH([@Tournaments],Append1[Tournaments],0)))

As stated in post #11: "Put the tournaments in the table on the List sheet, remove the duplicates and then fill in the categories."
I have copied the tournaments to the Append1 table and removed the duplicates. All you have to do is fill in the categories.
If you really want my opinion, I don't understand why you don't just use the data in book100 and set up a simple pivot table to display the information needed to decide who to stake.

thank you so much. i will fill categories. you think thats a better way? can you set up a good example of that with pivot table. i will input all games from 2024 to 4-25-24 file
i don't see the categorie to fill i will copy paste and delete duplicates
in this file i have all games i was trying to figure out all my deposits and withdrawals to to exact how much i have deposit and withdrawal 2024

Categories to fill are in cells B16:B30 on the List sheet of the 4-25-24 pokerstake file.
There is an example of a pivot table on the Pivot Table sheet of the Book100 file.

thank you mate, i think I redo and fill categories i will see file again i have put all games to date in post #16

okay after checking file Book100 games i it would work, having it that way can you set up the file with all games to current day so i can do lots of testing.after.png
after comparing file book100 to pokerstake
if we can have bookallgames set up to know breakdown of every deposit and withdrawals woudl be great not sure what i did in rows 4078:4095 a bit confusing for me about the amts.
please note:
that in bookall games i move the amt. from column I to G the Negatives i was trying to figure out how much i have deposit and how much i have withdrawal.
bookallgames i reversed to original so it won't be confusing me moving amt.

The formula that is supposed to be used to populate the Value3 column was missing from a lot of rows so I pasted it into cell K3 and copied down.
I put the withdrawal values back in column I and produced a pivot table to show the amounts deposited and withdrawn.

thank you mate, since my last post i been trying to know exact amts for all deposits and withdrawals not sure if this is correct this is what i have
D + 6233.14
W- 2921.45
L/P 3311.69 loosing would that be correct? will see file
can't beleive this we on the same page i was trying too figure that out but we both have different amts. wow
i use this file
can i delete columns not needed? like source.Name, my-shares, and percentage column from Bookallgames?

in sheet1 can we have pivot table for all players where i can see how much i have stake loss/profit amts bigest wins ?

Using a calculated item in the deposit/withdrawal pivot table yields the expected numbers: Act. Withdrawal=Withdrawal+'Withdrawal Canceled'
While the source.name and my-shares columns aren't needed, the percentage column is used in the calculation in the Combine percentage and value column.
Not sure what you expect in the for the other pivot table but set one up as a starting point.

Thank you mate.
okay will delete source & my-share leaving % column.
i want to figure out exact how much is a players been stake and if he is in profit or not as its using combine % sheet1 seems to show magnus lisberg -80.85 which seems correct will do more testing and see how that pivot table work in sheet1 will run to the bank and brb want to stake few other players for sunday 105 mystery brb thank you i will also deposit and giving us more of how it will work after i make deposit to pokarstake and do the entries which will be alot faster now
laterst file

Latest file with recent stake a lot faster to do to keep track please see downside i see is in the last row 4443 & 4442 is backward 4442 should be the last row like it shows in this picture.13.png

As this is an Excel table:
1. select cells A4443:J4443
2. move the cursor to the top of green selection box until you see a four headed arrow
3. press and hold the left mouse button
4. drag upward until the thick green line is between the rows for Pablo_Escobarr and DCB1316
5. release the left mouse button

Q: i was able to do that would that be an issue next time when i paste again the games. A: Seems as if the .csv file had the records out of order, so you'd have to ask whoever sent the .csv file.
Q: is there a way in pivot table to know how much i have stake so far just in that game. A: Yes, although I had to "Refresh All" for the pivot table to find the correct range on the Staking grom 01-01-2024 - 03-24 sheet.
As to guadis stats you'll need to show us a file that illustrates the issue.

thank you,
I get this 2 error when opening the file
after the errors table is gone, i can't continued to paste the stake games

I opened the file and got the "We found a problem with some..." message.
I selected yes and looked at the workbook.
For some reason Excel had converted the table on the Staking grom 01-01-2024 - 03-24 sheet into a range.
I converted the range to an Excel table and gave it the name: tbl_StakingGrom
I then changed the data source of the three pivot tables to tbl_StakingGrom.
Seems to be working.

thank you so much, your the best to have your support
I will see file and also enter all thes stakes from 4/26/24 and 4/27/24 ty
it seems of a bit of and issue. with the running balance not been in the last row. 61.13 should be row 4530 in this file as it shows in pokerstake61.13.png
instead its in row 4523
mate, have a question i am looking into guadis games in staaking grom it shows 48 games and on sheet 1 shows 20 games in this picture.
is that my actual games i have stake him for on 20?

Responding to post #29: there are 20 different event names associated with Guadis.
Note that some like [ Bounty Hunters Deepstack Turbo $8.55 ] are repeated multiple times.
The pivot table is going to combine the event names for seller name Guadis.

Here is the pivot table with dates.

thank you will look now.
as the day is over for me and not a very good day when i though it was going to be a good day is it possible to know how much i stake in a particular tournament and how much loss/profit i don't want this to repeat i know i stake way to many guys in this tournament. i will upload file with all games in a few minutes as i im done for the day
I am looking at guadis so is the amts. going to be combine for th same name of tournament ?
i don't undertand this pokerstake show one way and file shows different here is an example of both 33.png try searching by Apr 27, 2024 07:45

Over the past couple of days I have only worked on three other requests. At the present time I am working through the requests for the morning of April 25th. When I get caught up I'll come back to this again. Please do not add any other questions to what was edited in on 4/28/2024 until I or one of the other contributors has responded.

Not sure that I understand, however the time in the file is 10:45 AM.
If the issue is the difference between the times, and if pokerstack sent you the csv data then you would have to ask them.
If you want to find the date regardless of time do the following:
In the pivot table add the date field in the Rows area and then use date filter between 4/19/2024 and 4/28/2024.
If you just want to see the to the data on the 27th then change the between filter to 4/27/28 and 4/28/2024.

thank you mate.
yes i figure out that the issue is the time because of when i purchased the % it records the actual time thank ty i was able to find that out.
i would really like to know the best way to filter the data. i found erros on some players not sure if you can clear of the way by talking a bit. I just finished putting together a file which has All stake i mean all stakes from date 1 to 4/30/24 but i can't upload desposits shows over 20k in this file guadis dosen't show the amt. of 3.60 anywhere will upload
guadis don't show the 6.60 in cell J6 I don't think is minizing th 6.60 anywhere from daily hypersonic

I found the record in row 4490 of the Staking grom 01-01-2024 - 03-24 sheet.
I don't know what is expected in cell J6 on Sheet1 as the record shows a positive percentage and column J displays those percentages which are negative.

thank you, will look into that now.
J6 i think is suppose to be the amt. stake which is 6.60 and k6 51.71. - 6.60 i think this is how it shows in pokerstake

Then it would have to show on some column in row 4490 on the Staking grom 01-01-2024 - 03-24 sheet.
If J6 should show -6.60 then shouldn't J7 show -16.16?

so confused not sure why is not showing maybe i wasen't charge u mean? when i place the stake on him let me look into the pokerstake i mean i download from there as cvs file should of been there. looking into that now shows like this in pokerstake looking futher found it not sure what happen
i enter the missing games as it was 3 pages i had only 2 was missing 3rd part this is how it looks after
regarding your question on post#41 If J6 should show -6.60 then shouldn't J7 show -16.16?
i really don't know what should show in j7 very confuse of how this numbers work or where they should be.
j6 i though it would be -6.60 since i stake 6.60 for daily hypersonic $15 not sure about what should be in j7 i though would be the same -8.08 since thats how much it was stake its really really confusing for me i think it has to do because of all the times is there a way to simplify things and not be as confusing

I don't know pokerstake's accounting practices, however if you sort the dates ascending it may make a bit more sense.
The pivot table now makes it look like for the [ Daily Hypersonic $15 ] $6.60 was taken out of an account at 7:37 and then $51.71 was put into an account at 10:25.
Other than that I suggest asking pokerstake since they are the ones sending you the .csv file.

thank you so much. they don't send it to me i download from the pokerstake site. if you like please log in and see how that works.
let me explain how it kind of works, okay when i stake a games lets say for tomorrow it will record the actual time i buy the percentage. then when the game starts it put in the money as the player is winning if is bounty tounament money goes inthe account when the players KO's another player then i get my % of the bounty. and when my player i stake gets busted if his in the Money it would put in the money for its Performance Results in guadis was 51.71 when he busted out
please don't mind logging into the account
please also see this file with all games since the 1st date i started staking pivot shows different also would like to know how the deposits and withdrawals work see all here.
https://docs.google.com/spreadsheets...f=true&sd=true

The only instance of a deposit or withdrawal that I see is on Sheet1 cells B4:B5, and I don't know where those amounts came from.
Please explain what you want us to see in detail, including which sheet and which row(s).

in staking grom i in put all stake games from the day i started staking.
i suppose they come from all deposits in staking grom 01-2024 other than that i wouldn't know
is that not correct? i mean i don't know which amts ore correct now from results to deposits.
you have more expirence of how the accounting works thats why i was aking to log in to pokerstake account.

I don't have any experience with accounting.
I did find the deposits and withdrawals on the staking grom 01-2024 sheet by filtering the status column to only show deposits, withdrawals and withdrawals that have been canceled.
What is it that you want to do with them?

Back in post #20 you said: "...i been trying to know exact amts for all deposits and withdrawals not sure if this is correct this is what i have
D + 6233.14
W- 2921.45
L/P 3311.69 loosing would that be correct?"
On the Staking grom 01-01-2024 - 03-24sheet the amounts in the rows with withdrawal total -3859.65 and the amounts in the rows with withdrawal canceled total 938.20
When you add the two amounts you get -2921.45 which is what you said is the expected amount of the actual withdrawals.
If you want to see the amounts for withdrawals and withdrawals canceled then use the filter/sort button in cell A3 on Sheet1 and select those statuses.

thank you mate.
yes i was using filter to see how it all works ty. i have a question. okay a friend share a file right pretty much like the one we working on I still think ours is better thanks to you, but we can use some of the features from the workbook share. i would like to show it to you only that there is a problem.
its in google sheets. and when i download I think formulas get corrupted is there a way to have it work withtout gettting messed up on desktop? also its in Portuguese
this is the link i am not sure if you would be able to acess the file or be restricted please give it a shot. so you can see it working fully because the one i am about to upload is the one that gets corrupted when i download. will upload
https://docs.google.com/spreadsheets...it?usp=sharing
here is the link
please see sheet Sem straight X as is the only one i converted to english,
in the google sheet you can see how the vba works for ocultar an = hide exibir= show and limpar = clean. is pretty much the same as the one we have this will help complete our file only thing missing is commisioin. i added to is column V

I don't see anything in the commission column of either the google spreadsheet or the Excel spreadsheet, so I don't know what needs to be done.
Please include some manually calculated amounts in the commission column along with an explanation of how the amounts were obtained.
Due to being away from my computer over the weekend, I am behind on answering questions in other threads, so I may not have time to work on this for a while.

Here is the latest file I have that includes the Tournament sheet.

thank you so much mate. will work out of that one, but no luck yeah getting file from google converted so i can get it ready. as it gets corrupted. ty
question why would be reason for pivot table in all games sheet to show this way

I don't see that in the latest copy of the BookAllGames file, however I do see that the Number Format for the Grand Total column is set to General.
Changing the Number Format of the Grand Total column to Number may change the amount from -1.77636E-15 to zero, which I assume is what you want.

its in the 2024 all games i am checking not not sure why some times it does it and someties i dosen't show like that. thank you mate.
i am working in all the game i mean from day 1 again. not sure if you saw in one of file with all games it was showing gabs in value and in tournaments. names it was missing. not sure why when i download and convert to table it dosen't pick up some of the columns even if thery are in the cvs file i had to manually do it which take me a very long time.
have a question? is there a way to load the games from folder after i download as cvs to file without having to convert to table as I am having a few issues. when i convert to table, the file won't pick up the cvs amt. that are all in +
this is one issue i have when i convert to table the power query don't add games that are all + in the cvs so it shows blank in table example in picture and since is about 45k games is near impossible to manually do all and also 2 more issues where some time is the value that dont show or the event name

I don't see a file named 2024 all games, the closest thing I found was the BookAllGames file.
Did you try selecting the first cell under "value" and pressing the Ctrl, Shift and down arrow keys to select all amounts, then pressing the Ctrl and c keys to copy, then selecting the first cell under +$ and finally pressing the Ctrl and v keys to copy all the amounts?
I can't troubleshoot the 2 more issues without seeing a sample file that illustrates the problem.

mate i log out due to inactivity trying to figure this out due to been so into fixing this and i can't. i am trying to combine all this files in this link
https://drive.google.com/drive/folde...Mq?usp=sharing
i am dowloading in parts, can help to combine all folders.
in sample file there is gabs in value tournament event or percentage
can you please tell me the right way to do it?

1. Event name cuts in row #6302 this happens because the columns change a bit
2. percentage cuts in row #7302 this happens because cvs file show all value +
3. value cuts in row #4202 not sure.

...when i convert to table the power query...

I suggest starting by asking the person that wrote the power query code if they can help resolve the issues with event names.
I feel that you could ignore the percent and just assume that if it isn't there that it is + $
The value 2 column is not doing anything meaningful anyway since the sections are not in date/time order. Note that for row 1101 the date is 9/11 and for row 1102 the date is 9/13.
If the file was put in date/Time order then the value 2 column could be filled using a formula:

Formula:

Please Login or Register  to view this content.

thank you so much. power query code? not sure about if it wasen't you is on both of us and i didn't write no code. okay i manage to get a a file that has everything i mean all stakes the tricky part is that i downloaded hundreds of files in parts. 2022-2023 all files then all 2024 by it self then i did all files from 2022 to 2024 i don't know which is my real staking amt loss/profitt in each player so many different amounts. i download just 2024 and then combine with 2022-2023 and don't match my all file all in one file from all staking. i filled all gaps in this file https://docs.google.com/spreadsheets...f=true&sd=true
i will put another link 2024 combine with 2022 & 2023 so you can see what i mean. working on it now
This link here has everything above i think is only 2024
https://docs.google.com/spreadsheets...f=true&sd=true

I don't recognize the power query code; I didn't write it.
Adding a pivot table to the file brings the size up to 3.448 Mb zipped.
The Manage Attachments shows that zipped files up to 9.77 Mb can be uploaded, however I get a message saying uploading failed (frustrating).
Please tell us specifically what information you want gleaned from this file.
I don't know anything about loading a file to google or if a pivot table will work there.
However, once we know what you want, I'll look into attempting to upload the file to google when I get a chance (tomorrow at the earliest).

Mate, which code you and me have been the only ones work on this yeah i feel the same but, good news tho we finally going to have the numbers right.
because all files don't match which i don't know what is the exact number for anything.
wait don't upload yeah see this two files one it was sent to me from pokerstake but it only has upto 5/4/2024 i ask for the most up to date which will take a few days for me to get it. i was comparing to the one i put together. which don't match. in pokerstake, shows deposits of 20628.58 and withdrawals of 27382.45 which don't make sense i don't think i am in profit. please take a look. soon will have the whole thing figure out.
https://drive.google.com/file/d/1IYZ...ew?usp=sharing
https://docs.google.com/spreadsheets...f=true&sd=true

Looks to me like between 1/1/2022 and 5/7/2024 there are Deposits of $21,063.58; Withdrawals of $27,382.45 although $18,153.15 there are Withdrawal Canceled, so it would seem the actual withdrawals are $9,229.30.
If I understand correctly that would mean a loss of $11,834.28

Please tell us specifically what you want to modify.

This is message I get when selecting the link: "Sorry, the file you have requested does not exist. Make sure that you have the correct URL and the file exists."

https://drive.google.com/file/d/1IYZ...ew?usp=sharing
sorry i get the same message. Have question the formulas from google sheet and excel offline file are the same?
look at this file i want to put together but for that i need your advice as your the one knows well how things work. like that ill know what i can try to do and not. okay i have this file which i want to combine with ours but not sure about how to go as, I would like to figure out the schedule so like that i know how to set it up. I would like to use the list sheet to put all games for the week, lets say and then just copy paste and automatictly to appear in the tournaments in i also have a different way to do this thats why i would like to talk before actually working on it. this is the 2 files i am working on right now.
you have to see Sem Straight X tab for the changes, As I converted everything to english also codigo tab was hidden.
https://docs.google.com/spreadsheets...it?usp=sharing
in this file i was putting everything together as you can see columns O:Y
thats why i need your experience to help put everything togther before we even start to avoid issues. please let me know i like the funtions alot from the google sheet.
but i love ours which has everything that the other don't.

Please set the record straight, the pokerstake file is your file, I have no ownership.
As a participant in this volunteer forum, I help members with issues, without asking for or excepting anything in return, which includes not having any financial interest in the files.
As to the request it is too complex. Please state specifically which sheet in the pokerstake file needs modification.
Then go one column at a time stating what needs to be put in that column, giving manually calculated results so that contributors know what the results of the formulas/code they propose should be.
For example, column P on the tournaments sheet (I am guessing that's the one you want to work on) has the heading column 1, although above that and not part of the table is Investor % Purchased. There are no amounts in that column to give any indication of what a formula should yield.

can you please look at this file would like to know if it can be possible before i even request was needed in th file.
I added the poker sheet that was share to me. its the Sem Straight X
I would like that sheet to be the Schedule sheet whe i paste all my tournaments that i will play after download from pokerstake in cvs which is this file shedule1 here
then i would like that to translate to main sheet Tournaments. for that all other stats need to be the same. from column R:AB
then i can just copy paste new day would that be even possible? to do?

At present the formula for column R on the Tournaments sheet is: =[@[Sold % ]]*[@[%*MU]]
From which column or columns on the Sem Straight X sheet should the values to compute cost + fee come?

thank you, just paste nice can we get the rest to column Y
i am having a bit of trouble with row 6 as i paste formula in R6 then formula in k6 stops working for tournament buy-in in column J
can row 6 be deleted? delete option is inactive.

Row 6 is for column headers which cannot be deleted.
Formulas should be pasted into the table starting with row 7.

okay so that mean i need to move the header down from 5 row to 6 will do now thank you.