Balancing the league - Excel expo

Discussion in 'Leaguegaming American Hockey League (LGAHL)' started by Too Much Twoco, Oct 5, 2018.

  1. Too Much Twoco

    Too Much Twoco Well-Known Member

    Joined:
    Feb 6, 2017
    Messages:
    246
    Discord:
    Too Much Twoco#2520
    Trophy Points:
    2,721
    Location:
    Virginia
    Ratings:
    +454 / 2 / -17
    Hey all,

    AHL bidding is in full swing and of course there are posts about people being snubbed and what not. However, a common thing I have picked up on across both CHL and AHL is that some managers are far more knowledgeable than others (assume it applies to NHL too). I know getting people to signup as management is a challenge for LGHL and I wonder if some type of "cheat sheet" exists for people to use? If there is one, then no need to read further.


    If not, I would propose that perhaps one be sent out once bidding lists are finalized? Whether it's in excel format or something else, I think it would benefit management to have an easily searchable document to look for past gamertag names of a player or seasons of AHL experience, etc. there is a BIG difference between selecting a single player and seeing his past gamertags versus searching a gamertag and locating it amongst everyone in LG.

    Now some will argue that if you take management you should do your homework. It's a competitive league and no one should hold your hand. However, if AHL talent is dropping to the CHL and AHL managers are putting out Chl quality rosters in the AHL, it results in awful AHL teams and stacked CHL teams, neither of which is good for A competitive environment. It would be up to management to ultimately decide what needs to be in this data sheet, but the hope would be:

    1) it limits learning curve for newer managers

    2) encourages more people to take management or be open to it

    3) ensures more level competition at each of the respective levels.

    BONUS: it reduces success rate of people who change tag before bidding to try and play with friends. Playing with friends is awesome but LG isn't club.

    I see lists come out with preseason rankings and most of the time they are pretty accurate. For a truly competitive league, it should be tougher to so easily predict who will and won't succeed.

    TLDR: read the 3 numbered points above. Giving management a way to analyze the enormous LG player pool could help achieve the 3 things above.
     
    • Winner Winner x 4
    • Like Like x 2
    • Creative Creative x 1
    No Streaming Account
  2. ItsTip777

    ItsTip777 Well-Known Member

    Joined:
    Nov 27, 2016
    Messages:
    172
    Trophy Points:
    3,491
    Location:
    unknown
    Ratings:
    +197 / 14 / -16
    If you are already at the level of AHL management and are putting out chl level teams you shouldn’t have been management in the first place. It really doesn’t take too long to go over the bid list. With that said lots of managers would be surprised how helpful either their upper league management or fellow ahl managers can be if they go out of their way to ask questions or for help.
     
    • Winner Winner x 3
    No Streaming Account
  3. Boogie x6

    Boogie x6 Well-Known Member

    Joined:
    Jan 10, 2017
    Messages:
    214
    Trophy Points:
    2,866
    Location:
    New York
    Ratings:
    +566 / 15 / -15
    I have a spreadsheet that I created that analyzes every player stat for NHL, AHL and CHL for the past 7 seasons. It has about 15,000 rows of data and advanced stats. It then grades every player on an A-F scale based on their performance each season and averages their career.

    It took me 5 hours total to create it. Now I’ve been doing Excel for about 6 years, but never had formal training. Every formula has been learned by Googling what I want to do and thinking a little bit.

    I forced myself to learn excel in another league-style thing similar to LG so I could be competetive.

    My advice: If you’re young, use LG management as your opportunity to teach yourself excel. It’s the most useful tool in my life as an engineer. You’ll quickly realize that excel can be used to organize almost anything you get involved with.
     
    • Like Like x 2
    • Winner Winner x 1
    • Informative Informative x 1
    • Creative Creative x 1
    No Streaming Account
  4. Hansum Horty

    Hansum Horty Well-Known Member

    Joined:
    Jun 2, 2013
    Messages:
    1,397
    Discord:
    hansum_horty#0
    Trophy Points:
    4,951
    Location:
    Ontario Canada
    Ratings:
    +831 / 54 / -127
    I’ll take a copy ;)
     
    • Like Like x 2
    • Funny Funny x 1
    No Streaming Account
  5. Too Much Twoco

    Too Much Twoco Well-Known Member

    Joined:
    Feb 6, 2017
    Messages:
    246
    Discord:
    Too Much Twoco#2520
    Trophy Points:
    2,721
    Location:
    Virginia
    Ratings:
    +454 / 2 / -17
    Super good advice. I literally just got a job promotion this week specifically because the people needed someone proficient in excel. It sped my promotion potential up 10 years, and that's not an exaggeration.
     
    • Like Like x 1
    • Useful Useful x 1
    No Streaming Account
  6. Too Much Twoco

    Too Much Twoco Well-Known Member

    Joined:
    Feb 6, 2017
    Messages:
    246
    Discord:
    Too Much Twoco#2520
    Trophy Points:
    2,721
    Location:
    Virginia
    Ratings:
    +454 / 2 / -17
    @BoogieHedgehog6 on your excel sheet, recommend that you do a weighted average for the last 7 seasons. The most recent seasons should have more weight than 7 seasons ago. Love me some excel lol
     
    • Winner Winner x 1
    No Streaming Account
  7. Boogie x6

    Boogie x6 Well-Known Member

    Joined:
    Jan 10, 2017
    Messages:
    214
    Trophy Points:
    2,866
    Location:
    New York
    Ratings:
    +566 / 15 / -15
    The average is more of a “general knowledge” to narrow down the 1500 guys on a bid list down to 50-100. I just use that to get rid of the ones I’m not interested in.

    I then break those 50-100 guys down further with everything in a “per game” format for the last few seasons, and using the advanced stats like goals against per game and own zone efficiency. I also created some of my own stats I think are helpful, like givesaway to assist ratio to see who forces the middle too much.

    I literally know more about each player than they know about themselves. People that say “numbers dont mean a thing” aren’t looking at the numbers right, I can usually tell where the weakness in their game is by looking at the numbers.

    Edit: it’s not flawless, but if you saw the stat breakdown its pretty obvious to tell someones play style without every playing with them. I’ll be bidding on many players completely blind this season, so we’ll see I guess
     
    • Like Like x 1
    • Informative Informative x 1
    No Streaming Account
  8. ItsTip777

    ItsTip777 Well-Known Member

    Joined:
    Nov 27, 2016
    Messages:
    172
    Trophy Points:
    3,491
    Location:
    unknown
    Ratings:
    +197 / 14 / -16
    @BoogieHedgehog6 I can second the point that being able to use excel further than just sorting lists is an extremely useful skill. It’s the basis of my entire college degree and can be an asset in almost every single workplace in one way or another
     
    • Like Like x 1
    No Streaming Account
  9. ItsTip777

    ItsTip777 Well-Known Member

    Joined:
    Nov 27, 2016
    Messages:
    172
    Trophy Points:
    3,491
    Location:
    unknown
    Ratings:
    +197 / 14 / -16
    If you take the time to make team/line adjusted stats it’s even more useful, granted this is a lengthier process and can sometimes require basic code and isn’t expected of people in a free league but still a thought
     
    No Streaming Account
  10. Hannah7Banana22

    Hannah7Banana22 Well-Known Member

    Joined:
    Sep 16, 2015
    Messages:
    613
    Trophy Points:
    6,371
    Location:
    USA
    Ratings:
    +1,417 / 191 / -360
    @brandovedo
     
    • Like Like x 1
    No Streaming Account
  11. Boogie x6

    Boogie x6 Well-Known Member

    Joined:
    Jan 10, 2017
    Messages:
    214
    Trophy Points:
    2,866
    Location:
    New York
    Ratings:
    +566 / 15 / -15
    @Schwifty Tip I thought about doing this, but I discovered something else that can help evaluate if a player carries their line or if they get carried. I’m not gonna give away the secret since my bidding starts Monday, but there is a way to do it from the basic stats provided.

    I love me some fellow excel nerds. I run the operations for my conpany and have 150+ sheets all linked together to automate most processes. It’s literally my job security
     
    • Like Like x 1
    No Streaming Account
  12. Boogie x6

    Boogie x6 Well-Known Member

    Joined:
    Jan 10, 2017
    Messages:
    214
    Trophy Points:
    2,866
    Location:
    New York
    Ratings:
    +566 / 15 / -15
    If anyone takes this seriously, here is my trick: anyone can quickly learn the basic formulas. They literally walk you through the setup.

    The key to advanced excel lies in learning how to use INDEX, MATCH and SMALL formulas. Then you can look up and pull out info from huge databases in a second. And find the “nth” match if there are duplicates. Learning how to use those 3 functions changed everything for me.
     
    No Streaming Account
  13. Hannah7Banana22

    Hannah7Banana22 Well-Known Member

    Joined:
    Sep 16, 2015
    Messages:
    613
    Trophy Points:
    6,371
    Location:
    USA
    Ratings:
    +1,417 / 191 / -360
    Look deeply into my stats .. what do they tell you? Do I have a Stanley cup trophy in my future ?
     
    No Streaming Account
  14. Boogie x6

    Boogie x6 Well-Known Member

    Joined:
    Jan 10, 2017
    Messages:
    214
    Trophy Points:
    2,866
    Location:
    New York
    Ratings:
    +566 / 15 / -15
    My crystal ball is only showing chat/forum mutes....strange
     
    • Funny Funny x 3
    • Like Like x 2
    No Streaming Account
  15. Too Much Twoco

    Too Much Twoco Well-Known Member

    Joined:
    Feb 6, 2017
    Messages:
    246
    Discord:
    Too Much Twoco#2520
    Trophy Points:
    2,721
    Location:
    Virginia
    Ratings:
    +454 / 2 / -17
    Index/Match and if functions are extremely useful.

    To refocus the conversation back to LG, I think it would be beneficial to have a basic sheet exported from the site. I would even go so far as to say that having the sheet be used as part of the management test would be a good idea. It seems the most logical way to try and help new managers.

    Then for those like Boogie who want to go deeper, they could use the excel data as a source file for their own advanced analytics
     
    No Streaming Account
  16. Crankov

    Crankov Well-Known Member

    Joined:
    Dec 1, 2013
    Messages:
    323
    Trophy Points:
    2,801
    Ratings:
    +373 / 22 / -61
    This is the equivalent to baseballs moneyball. You are a genius.
     
    No Streaming Account
  17. Tatawaka

    Tatawaka why do people claim me...

    Joined:
    May 16, 2017
    Messages:
    180
    Trophy Points:
    801
    Location:
    New Brunswick
    Ratings:
    +186 / 5 / -3
    May I ask how tou got the data?
    As someone who works with stats and databases for my job, this is what i’ve been looking to do for a while but I cannot find where to pull these stats from the website.
    I’d like to build custom stats but do not know where to start...
     
    • Like Like x 2
    No Streaming Account
  18. NotoriousFAT 00

    NotoriousFAT 00 Former LG media kingpin, Wine gum lover, LG dad.

    Joined:
    Apr 25, 2015
    Messages:
    1,524
    Discord:
    NotoriousFAT 00#5853
    Trophy Points:
    4,801
    Location:
    Winnipeg MB
    Ratings:
    +3,744 / 76 / -124
    I came here to read witty remarks and snide comments aimed at destroying the last lingering shreds of confidence LG players have remaining after a traumatic AHL bidding process, Not to get career building advice that enriches my life and furthers stocks my bank account!!! I'm writing a Yelp review after I leave here 2/10 will not read again.
     
    • Like Like x 1
    Offline
    notoriousfat00
  19. GrittySaves

    GrittySaves Well-Known Member

    Joined:
    Dec 10, 2013
    Messages:
    920
    Discord:
    dankdad88#0
    Trophy Points:
    3,226
    Location:
    Mid-Michigan
    Ratings:
    +838 / 68 / -115
    Bigger problem...positional changes in between bidding in each league...let's people take advantage and drop to CHL on purpose. Fairly certain even a BOG is going to try and do it...
     
    No Streaming Account
  20. Too Much Twoco

    Too Much Twoco Well-Known Member

    Joined:
    Feb 6, 2017
    Messages:
    246
    Discord:
    Too Much Twoco#2520
    Trophy Points:
    2,721
    Location:
    Virginia
    Ratings:
    +454 / 2 / -17
    I spoke on the issue of talented players "hiding" in the CHL during a discussion on random assignments. Basic point is that you could argue for a single cut off date to sign up for league. Any sign ups after that point either don't play or at best are limited ECU who can't be used in playoffs. It's a competitive league so figure out the signup date and take the 2 min to do it people. I

    I would argue that having an excel sheet or data analysis that management is trained on would help minimize impact of players changing position to get to CHL. Sure maybe there is a stud C who signs up as G and switches before CHL bidding. he wouldn't go for 500k if people see he has 6 years AHL experience and is good. Maybe he goes for 5 mil. Team gets a great player but now there is a cost associated with it that restricts roster flexibility.
     
    No Streaming Account