PokerStars homepage
  • If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Tool to calculate the score and ranking provisional in tourneys

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Tool to calculate the score and ranking provisional in tourneys

    This Excel file contains the following spreadsheets: 1) "PSO Skill League": To see the score during the tourney and the score on preset positions. 2) "Opponents": To see the distances with the 50 first opponents of the league and to calculate the provisional distances during the tourneys. 3) "Stats": To see interesting stats about the own results. 4) "ITM": To calculate in every moment how many chips are needed to arrive at places paid. 5) "Tourneys": To see the results in past tourneys. Instructions Step 1: Download the Excel file The file size is arround 100 KB. I can't attach the file in the forum because the size exceed the limit. The download is free, you must wait 45 seconds and click on "Regular download". Click on the following link to download the file: Open Skill League Version ***deleted link JWK24*** Last upload: 05 Oct 2011 15:50 ET Version: 5.0 Download and decompresses the file with Winzip, IZArc, Winrar or your favourite decompressor. Premier Skill League Version *** deleted link JWK24*** Last upload: 03 Aug 2011 13:55 ET Version: 4.0 Requeriments To use this Excel file you need Microsoft Office 2007, 2008, 2009, 2010 or 2011. If you don't have this Office versions and you don't want install it, you can use the last version of the program OpenOffice, you can download it in the following link: http://openoffice.en.softonic.com/ (English) http://openoffice.softonic.com/ (Spanish) Step 2: Customize the external data This step is essential to calculate the score. This data shouldn't be changed manually, this data is automatically obtained by the external data. However, the first time that you download the Excel file the data will not be customized, it will show my own results (results of Thanat0s666), so is necessary to modify the query changing the user to obtain the external data scores of your user. The results shown on the PokerSchoolOnline website can have up to 8 pages, therefore have to modify 8 queries, so that the program displays all the results of the month. The Excel structure is as follows: There is a spreadsheet ("Tourneys") that collects information from all 8 results pages, then there are 8 spreadsheets ("P1", "P2", "P3", etc.) one for each results page. To do this, you must come in the spreadsheet "P1", then click the right mouse button over the first cell (A1) and click on "Edit query...". It will open a browser. Maximize for comfort. It will appear the query for my nickname (Thanatos666). To change this, you must move the vertical scroll bar at the top, where will appear the search bar, and then find your nick, where will appear your own results. Now you must select the table "Rank history", which shows the results of each tourney, by clicking the black arrow on a yellow background, it will change to a correct sign[IMG]//media.intellipoker.com/images_site/global/themes/pso/checked.png[/IMG] with green background. Finally, click on "Import" and from now the Excel always will obtain your own results. Then, enter to "P2", "P3", "P4", and the rest of the pages and repeat the same procedure. The only difference is that in the early days of the month only will exist the first page, the rest of pages will not exist as links, only will exist on the server, but can not access them from the first page, it will remain hidden. To access these hidden pages and modify the user must change the Web address, changing the word "Thanat0s666" by your username. For example, if your username is "Harry", then first of all delete the user "Thanat0s666": http://www.pokerschoolonline.com/lea...oard?username=Thanat0s666&hpage=1 Change it to "Harry" and press "Enter" to load the Web page: http://www.pokerschoolonline.com/lea...oard?username=Harry&hpage=1 Done this, click on the arrow to select the table, which probably will be empty, and click to "Import". Repeat the same procedure to the rest of pages. After doing this, you must save the Excel file for keep these queries and not have to do this procedure each time you open the file. To save the file you must click on the icon to save "", or click the hotkey "Ctrl+S" or click on the Office symbol in the upper left corner and then to "Save" Step 3: Create a folder to add it to the Trust Center Each time you open the Excel will appear the message "Security Warning. Macros have been disabled." This means that external data that obtains the leaderboard and the score is blocked by security. However, in this case security is not necessary since the PokerSchoolOnline website is 100% secure, so you can create a folder on your PC and add it to the Trust Center to not block the Excel data connections of files located in that folder. To do this, create a folder where you want and put a name, eg "PSO Skill League". In my own PC, for example, I created this route: C:\Documents and Settings\ Administrator\Desktop\Documents\PSO Skill League Then, to add this folder to the Trust Center, when the warning message appears, you must click on "Options" and then click below, where it says "Open the Trust Center". Click "Trusted Locations" and then "Add new location...". Now click "Browse..." and browse to the folder you've created and give "OK", close the Trust Center by clicking also "OK", close the security warning by clicking "Cancel" and close the file Excel without saving. Now open again the excel file and see how from now on will not appear the security warning, so every time you open the excel will automatically connect to the web and will get the external data. Updates Every time that the data are updated to the website, to update it in the Excel there are two ways: 1) Difficult mode: Open again the file. 2) Easy mode: Click on "Data" and after to "Refresh All". If you open the file only the spreadsheets "Leaderboard" and "P1" will be updated. The reason of this configuration is to accelerate the program loading. If you want update all the score pages it is necessary to click on "Data" and after to "Refresh All". If you want update automatically all the spreadsheets when you open the file, click on every result page, click the secondary mouse button, click on "Properties" and check the option "Update on open the file". Step 4: The decimal It's important to note that the PSO Skill League website use decimals in American format, for thousands separators uses commas and for decimals uses dots, while in Europe and some Latin American countries it is the opposite. If your Excel does not recognize dots as decimals, to solve this click on ">Start", ">Control Panel", ">Regional and Language Options" and click "Customize". You must change the "Decimal symbol" by a dot, and the "Symbol for separating thousands" by a comma. Finally, click "Apply" and then "OK", and from now the Excel will recognize the dots as decimals. If you write the number of entries in the cell "Entries" and in the cells "Total points" and "Score" appears the message "¡Value!" this means that you need change the "Regional and Language Options". Spreadsheet "PSO Skill League" Here you can see the score in the tourney of a specific or predetermined position with a margin of error less than 1 point. To calculate a concrete position, you must write the number of entries in the cell B3 ("Entries") and after the "Current/final position in the tourney" in the cell C10. The cell D6 ("Average score") is obtained automatically, you can't and mustn't modify this cell. The "Day of the month" is used to calculate the "Average score" and also is obtained automatically, you can't and mustn't modify this cell. However, for some countries is probably that some tourneys coincide with the new day and the number will change automatically. To solve this problem, I have created the cell H6 ("Concrete day of the month") where we can write the number of previous day for a correct calculation. The best is write decimal days, for example if you are playing the tourney of 20:00 ET, and is the 10th day of the month, in Spain would be the 02:00 CET, 11th day of the month, but in the league already have played 4 tourneys of the previous day, this means that the "Average score" will be incorrect. In this case, the best is write "10.5" in the cell. It is more important for the last day of the month, since in these countries would be the first day of the next month. When you don't use the concrete day, you must delete the number and let the cell empty, but the Excel will ignore the automatic number (cell F6) and will use number of the cell H6. The cell K6 ("Previous scores") is used when you played a previous tourney that still have not been updated, this means that the current points in the league will be incorrect. To solve this, you must write the previous scores in the cells K8, K9 and K10, the Excel will add up these previous scores with the points in the league and it will obtain the correct current points in the league. If you want use a concrete points you can use the cell K13 writting the concrete score. When you want use the automatic "Points in the league" you must let the cell K13 empty, but the Excel will use these "Concrete points" to calculate the preset positions and the calculations will be incorrect. The "Current position in the league" also is obtained automatically, but only shows the first 50 positions, when the current position is higher that the 50th place the cell will show "+50" to indicate that we are not between the first 50 players of the leaderboard. The "Places paid" is used to calculate the "Extra points". Below, you can see two tables with preset positions. When you write the entries and the current/final position, the Excel automatically will calculate the preset positions. These tables are very useful to see from which position you don't subtract points. Also are very useful to see from which position you can take risks. For example, if you have 1785 points in the league, this means that if you arrive to the ITM positions you will get arround 20 pts. You have a lot of tourneys to get this 20 pts, but, if you want win the league, in the begining of the tourney you can't call an All In with hands as A-10, A-J or K-Q, for example, because you would can lose a lot of points, if there are 10.000 entries and you finish at 8000th position, you will lose arround 19 pts, this is not acceptable. These tables uses conditional formats. The negative numbers are showed in red and the positive numbers are showed in green. The blue is used to automatic values. Spreadsheet "Opponents" Here you can see the distances with the 50 first opponents of the league and calculate the provisional distances. The nicknames are not precise because the initials of the country in the beggining of the nick. You can see the distances in the column L. To calculate the provisionals distances, you must write the current or final position of every player in the column H and the program automatically will calculate the points and the new score in the league. You will can see the difference between your own score and the score of your opponents in the column N and the new distances in the column M. Only you will not be able to see the new scores when the players arrive to the ITM, in this case will appear the word "Entry" and you will can see the "Entry Points" (EP) in the column J and K. If you want, you can sort the players to see the new leaderboard. You must select the range of cells from cell D3 to the last cell of column N that you want sort. For example, in the case of the image above, I should select from the cell D3 to the cell N12, click on "Sort&Filter", click ok "Custom sort", deselect "My data has headers" and select "Sort by Column M" (Sort on Values & Order Largest to Smallest), and now I will can see the new leaderboard. After doing this, you must press CTRL+Z (Undo) to sort correctly the table for the following uses, but the following list of rivals would remain disordered. The column G is used to calculate consecutive tourneys. If you have played the tourney of the 13:00 ET, for example, and now you are playing the tourney of the 17:30 ET, the most probable thing is that the previous 2 tourneys still have not been updated, this means that you will not be able to calculate the real distances. To solve this problem, you can use the column G writting the scores of the last tourney. You will have to select the new scores in the column I and press CTRL+C (copy). Then, click in the cell G3 with the secundary button, select "Paste Special", in "Paste" click to "Values" and finally click "OK". If someone arrived to the Entry Points, in this cases will appear the word "Entry" in the new score, then you must add up the previous score (column E) with the entry points. For example, if the player in the 4th position arrive to the Entry Points and gets 29 pts, you must add up the points of the cell E6 with these 29 pts. To do it automatically, you can write "=" and after select the cell E6, and write "+29", the complete formula would be "=E6+29", this method is more easy and precise. If you use this function, you will be able to see the previous scores of these players in the column F. Spreadsheet "Stats" Here you can see interesting stats about your results on the league. You can see how many tourneys have you played or not played, how many times have you arrived to the final table, in the money (ITM), or in other TOPs, how many times have you added or subtracted, the total points, the total added points and the total subtracted points. Below, you can see the percentage of risk in your hands, as from the times that you have arrived to the TOP 25%, the entries average, your average position, your current position in the league, your best and worst position on the month, your daily points average and a study about your hours playing. Spreadsheet "Chips" This spreadsheet is useful to know how many points we can win with the current stack. First of all is necessary write the number of entries in the first spreadsheet. After this, you can see the eliminations every 20 seconds, based on a study that I did during september. This study is very precise for the tourneys with 10.000 entries, but for the tourneys with less than 10.000 entries the margin of error is bigger. However, it is not a problem, to solve it I made the column "C" where we can correct the number of players. For example, if we are playing a tourney with 8.000 entries and in the minute 16 there are 4800 players, if the program indicate 4215, we can correct it and write "4800" in the cell "C50". Is very important correct the current number of players whenever we want use the tool. The second correction tool is the column "S", where we can write the current position in the table. There are 9 positions: "D" (Dealer), "SB" (Small Blind), "BB" (Big Blind), "UTG1" (Under The Gun 1), "UTG2", "UTG3", "MP1" (Middle Position 3), "MP2" and "CO" (Cut Off). Is necessary write the concrete word to use the automatic system, the positions will change automatically and it will calculate the big and small blinds in the cells "R" and "Q", based on the level of blind (cell "O"). The last correction tool is the column "W", where we can write the current stack, in the blue cells. For example, if we have 3650 chips in the minute 32 and we are in the dealer, it is necessary write "3650" in the cell "W98", write "D" in the cell "S98" and correct the number of current players in the cell "C98". After this, we can see a new precise calculation about how many points we can win in every minute, in the columns "K" and "L". To see how many time we can survive without playing any hands, see the column "X", where we can see the future stack, when the stack is negative it means that it is the moment when we can be eliminated. Anyway, to obtain a more precise result, after 20 or 30 hands it will be necessary write again the current stack, the current position and the current number of players. This tool will be improved during the current and next month, I need taste it during this month and obtain more data about the eliminations per minute to get a more precise tool. Spreadsheet "ITM" This is a very useful tool to calculate how many chips are needed in every moment of the tourney to arrive at ITM. This tool only is used for the Premier League. You must write your current stack in the cell H4, the level of blinds in the cell H7 and your current position in the table in cell J7. You can see the level of blinds in the Lobby or in the cells A2:E15. To know your position use the instructions of the cell K7. Normally, we arrive to ITM in the level 10 of blinds (200/400), before recieve the 600's blind. This means that we need win between 1.650 and 2.100 chips to arrive at places paid. With this tool you will can calculate your needed chips in every moment of the tourney. These calculations will be correct if you play a tight poker, exhausting your 20 seconds in every turn from level 4 of blinds, if you play faster you will need more chips. If both numbers are negative, this means that you don't need more chips, only make time and fold F.A.Q. I downloaded the file but the tables doesn't work, why? If you wrote the number of entries and your current or final position in the cells B3 and C9, but the tables doesn't work, the most probable thing is that your Reginal Configuration uses commas for the decimals, and this makes the program doesn't recognize the numbers of the external data. Follow the instructions of the Step 4 to solve it. Latest updatesVersion 5.0 (05 Oct 2011 16:50 ET): [Click here to see what's new]Version 4.0 (03 Aug 2011 13:55 ET): [Click here to see what's new]Version 3.0 (24 Jul 2011 12:30 ET): [Click here to see what's new]Version 2.2 (04 Jun 2011 23:00 ET): [Click here to see what's new]Version 2.1 (21 May 2011 22:20 ET): Finished the expansion of the spreadsheet "Stats", expanding from 62 to 186 tourneys. Added 2 new stats: the tourneys not played and the remaining tourneys. Corrected the stat "Hours playing". Corrected a little error of the formula of the spreadsheet "ITM". Improved the formula of the "Entry points", doing it more precise. • Version 2.0 (18 May 2011 11:54 ET): Applied a new average system to calculate the score. Improved the cells without values. • Version 1.2 (13 May 2011 15:48 ET): Solved a problem of the opponents tool. • Version 1.1 (13 May 2011 14:01 ET): Solved a little problem, 2 cells automatically were changing to spanish, now don't change. Also I changed the format of the first column of the spreadsheet "Tourneys", before appeared round numbers as a date, now are showed as a date. Future projects • New spreadsheet where we will can see the stats of the previous month and a cells that will compare the results of the previous month with the results of the current month. ---
    Last edited by JWK24; Thu Feb 16, 2012, 03:23 AM. Reason: New version 5.0

  • #2
    Finally I finished the program! Read the instructions and enjoy this big tool! =D

    Comment


    • #3
      WOW i must compliment you on this Thanat0s666, You must be a very smart guy, This tool looks very usefull and it will be interesting to see if it helps some of the better players in PSO gain more Pts from using it. I will be keeping a close eye on this to see if it could help me achieve a better final score. Keep up the good work mate umbup: De Hitman.

      Comment


      • #4
        Nice work. Too bad we'll still see people whining in here the first time the leaderboard doesn't update exactly on schedule. umbup: zzzzzzzzzzzzzzz

        Comment


        • #5
          Originally posted by ssuglia View Post
          Nice work. Too bad we'll still see people whining in here the first time the leaderboard doesn't update exactly on schedule. umbup: zzzzzzzzzzzzzzz
          Hmm. It's Friday. That'll happen tonight then

          Comment


          • #6
            Originally posted by Darkman61 View Post
            Hmm. It's Friday. That'll happen tonight then
            At least they're rotating Fridays now.

            Always doing it on the last Friday got kinda boring.

            zzzzzzzzzzzzzzzzz

            Comment


            • #7
              NICE thana i exited with this i have a lot of time waiting and now i can enjoy the program ty for you cooperatiionn umbup: pd your friend monox15

              Comment


              • #8
                gg

                good work. gracias

                Comment


                • #9
                  wowsers

                  one word GENIUS!

                  Comment


                  • #10
                    reply

                    has anyone tried downloading this? Anyone else have problems?

                    Comment


                    • #11
                      Originally posted by Stakehorse75 View Post
                      has anyone tried downloading this? Anyone else have problems?
                      What problem you have?

                      Comment


                      • #12
                        This thread should be made a ' sticky ' alongside Cyclists

                        3 Time Bracelet Winner


                        Comment


                        • #13
                          Originally posted by Thanat0s666 View Post
                          What problem you have?

                          I opened all files trying to find the spreadsheets that I need to change my name to., and couldn't find any files you had said were there.

                          Also, couldn't change any of the info in any files.
                          Last edited by Stakehorse75; Mon May 16, 2011, 04:15 PM.

                          Comment


                          • #14
                            Originally posted by Stakehorse75 View Post
                            I opened all files trying to find the spreadsheets that I need to change my name to., and couldn't find any files you had said were there.
                            This step is very easy, I'll show you in pictures (Click on thumbnails for maximize):




                            1- Click on the spreadsheet "P1". Now click with the secondary button on the cell A1
                            2- Click on "Edit Query"

                            It will open a browser, maximize it.



                            1- Search your nick in the search bar
                            2- Click on the black arrow on a yellow background, it will change to a correct sign on a green background.
                            3- Finally, click on "Import".

                            Then, enter to "P2", "P3", "P4", and the rest of the pages and repeat the same procedure. The only difference is that in the early days of the month only will exist the first page, the rest of pages will not exist as links, only will exist on the server, but can not access them from the first page, it will remain hidden. To access these hidden pages and modify the user must change the Web address, changing the word "Thanat0s666" by your username. First of all delete the user "Thanat0s666":

                            http://www.pokerschoolonline.com/lea...oard?username=Thanat0s666&hpage=1

                            Change it to "Stakehorse75" and press "Enter" to load the Web page:

                            http://www.pokerschoolonline.com/lea...oard?username=Stakehorse75&hpage=1

                            Done this, click on the arrow to select the table, which probably will be empty, and click to "Import". Repeat the same procedure to the rest of pages.

                            After doing this, you must save the Excel file for keep these queries and not have to do this procedure each time you open the file. To save the file you must click on the icon to save "", or click the hotkey "Ctrl+S" or click on the Office symbol in the upper left corner and then to "Save"

                            ---
                            Last edited by Thanat0s666; Mon May 16, 2011, 07:34 PM.

                            Comment


                            • #15
                              reply.

                              In case anyone wants to download this, you need to have microsoft office, or the microsoft excel program.Just figured it out.

                              Comment

                              Working...
                              X

                              X Cookies Information

                              We have placed cookies on your computer to improve your experience on our website. You can change your cookie settings at any time. Otherwise, we'll assume you're OK to continue.