Page 1 of 2 1 2 LastLast
Results 1 to 15 of 25

Thread: Spreadsheet Geeks Concatenate!

  1. #1
    Location
    Thunder Bay, ON
    Rep Power
    18

    Dobber Sports Blue-Chipper

    Lightbulb Spreadsheet Geeks Concatenate!

    Sorry, bad spreadsheet humour.

    I wasn't sure if there was/are other threads dedicated to the use of spreadsheets in fantasy hockey, so if there isn't - here it is!

    I've been playing fantasy hockey for 20+ years, and making spreadsheets for about that long. I wouldn't say the spreadsheets have gotten much more complicated - but Google spreadsheets, and sites like Fantrax have taken them to another level for me.

    If you have no experience with spreadsheets, this may be a great starting point for you. Hopefully we can convince some of the other members to step forward and put some posts together with their own creations.


    Spreadsheets I've created:
    Spreadsheets for Entry Drafts in Dynasty Leagues, averaging draft lists from multiple sources
    Spreadsheets for drafting every league (using Fantasy Hockey Geek lists, with Dobber's projections).
    Spreadsheets for drafting in points leagues adjusting lists using position scarcity.
    A spreadsheet for monitoring the waiver wire daily/weekly, looking for players with the best average ice-time and/or best average power-play ice-time (using auto-imported data from hockey-reference).
    A spreadsheet for tracking prospects outside of the NHL, using NHL equivalency data to find value outside of the 3rd/4th round of the NHL draft (using auto-imported data from Elite Prospects and Cap Friendly).
    A spreadsheet to determine the strength of my team compared to the rest of my league (using Fantrax/Yahoo).

    Site List of free hockey information:
    Dobber Frozen Tools (Reports can be downloaded as CSV)
    Hockey Reference: Skater Stats, Goalie Stats, Team Stats
    Cap Friendly: Salary Data, Draft History, ELC information
    Elite Prospects: non-NHL Player Data
    Fantrax: League specific data, easily downloaded for importing into your spreadsheets
    Yahoo: League specific data, not easy - but manipulable

    Most everything I've learned has come from trial/error, or from resources on the web. I'd love to learn more - so if there is a function you've found that has changed your life - please share!

    If you have an idea for a spreadsheet, and don't know where to start - ASK AWAY!

    IMPORTHTML and VLOOKUP might just change your life!

    Quick Lessons/Examples
    Quick IMPORTHTML example

    Sample Spreadsheets
    Auto-updating data from Frozen Pool/Hockey-Reference/Elite Prospects
     

    Comish Dynasty 2021-22 (24 Team Roto Dynasty, Custom Salaries)
    Minnesota North Stars, 2nd Tier
    C - Aho, Rakell, Foligno, Trenin (Janmark, Steel, Kurashev)
    LW - Hall, Lucic, Sanford, Bellows (Vesalainen, Foote, Katchouk)
    RW - Seguin, Toffoli, Jaskin, Donskoi (Jarvis)
    D - Sergachev, Edler, Petterson, Forbort, Ceci, Valimaki (H.Fleury, Alexeyev, Salo)
    G - Price, Brossoit (Stolarz)

  2. #2
    Rep Power
    13

    Dobber Sports Padawan

    Default Re: Spreadsheet Geeks Concatenate!

    hello fellow spreadsheet enthusiast...
    I have used the same workbook for about 8 years or so. Like you, I have put a lot of work into it. It's more fun than work though, so all good.
    One thing I will say on this topic, getting external data is always a top priority every month, especially with a quality site like this one.
    But why do they sometimes randomly change the names?!?! Ugh, I'm sure you can relate to the frustration when a site inexplicably changes Mitch Marner to Mitchell Marner and then back to Mitch. My lookup then fails until I manually change the name to fix it. Shesterkin is another one. Sometimes Shestyorkin.
    Sven Bartschi is another, sometimes Baertschi.

    Also, as I'm sure you know, Index and Match are better to use instead of Vlookup. However, I must confess, I was taught to use Vlookup, I like it better, I find it easier because I can use it automatically without thinking about it. But a few times I've forced myself to use what the consensus choice is: Index and Match together. And I do admit, it's better. More powerful and flexible, e.g. being able to look up left or right rather than always needing to fetch from the right only with a vlookup. And vlookups on a large spreadsheet are much, much slower.

  3. #3
    kalel's Avatar
    kalel is offline
    Join Date
    Aug 2019
    Posts
    12
    Location
    Ontario
    Rep Power
    0

    Dobber Sports Rookie

    Default Re: Spreadsheet Geeks Concatenate!

    This is a fun little thread!

    I'm fairly new to the spreadsheets concept, having built and used my current iteration for the past two years.

    My favourites so far are Standardize (I standardize my values to compare across categories in my...surprise..category league in an attempt to compares apples to oranges), IFERROR...which I admit, I'm not entirely sure how it works, but it helps with my Indexing of players drafted. And SUMIF is great for my auction league which helps me add up the money spent by teams once a specific player is drafted!

    I'm still learning and plan on maybe one day making some type of mathematical model within my league to attempt and predict how much certain players will go for relative to their projected values in each category? Maybe more math than anything else, but I won't be able to do it without a spreadsheet!
    18 Teams Salary H2H Points
    14 Teams H2H Points


    "Violence is the last refuge of the incompetent"

  4. #4
    evans334's Avatar
    evans334 is offline
    Join Date
    Apr 2013
    Posts
    2,612
    Rep Power
    43

    Dobber Sports Veteran

    Default Re: Spreadsheet Geeks Concatenate!

    I'm starting to wander down the Microsoft Access path, it's a nice way to link multiple spreadsheets together and run queries or reports based on certain criteria.

    Definitely a bit overkill but I've been practicing on hockey stuff to brush up on my skills to apply for work related stuff.
    Evansville Icemen District-5 Ducks
    LOEGM - 12 GM H2H Keeper
    G A PT SOG STA STG TRUC FOW
    W GA SV SV% SO
    WHL - 24 GM H2H Salary
    G A +/- PIM SOG PPP HIT BLK FOW
    W GA SV SO
    View Roster View Roster

  5. #5
    Location
    Brampton, Ontario
    Rep Power
    40

    Dobber Sports Titan

    Default Re: Spreadsheet Geeks Concatenate!

    If someone knows the best way to continuously dump updated points for each player into excel, that would be great.
    (Working on a Mac if that makes a difference).
    Associate Editor for DobberHockey (Wednesdays). Click that Ramblings button on the the menu bar!
    (No I don't have a hockey problem...)

  6. #6
    Location
    Thunder Bay, ON
    Rep Power
    18

    Dobber Sports Blue-Chipper

    Default Re: Spreadsheet Geeks Concatenate!

    Quote Originally Posted by Eskimo Brother View Post
    If someone knows the best way to continuously dump updated points for each player into excel, that would be great.
    (Working on a Mac if that makes a difference).
    Walk me through this... Are you looking for totals? Looking for stats for an individual day?
     

    Comish Dynasty 2021-22 (24 Team Roto Dynasty, Custom Salaries)
    Minnesota North Stars, 2nd Tier
    C - Aho, Rakell, Foligno, Trenin (Janmark, Steel, Kurashev)
    LW - Hall, Lucic, Sanford, Bellows (Vesalainen, Foote, Katchouk)
    RW - Seguin, Toffoli, Jaskin, Donskoi (Jarvis)
    D - Sergachev, Edler, Petterson, Forbort, Ceci, Valimaki (H.Fleury, Alexeyev, Salo)
    G - Price, Brossoit (Stolarz)

  7. #7
    Location
    Brampton, Ontario
    Rep Power
    40

    Dobber Sports Titan

    Default Re: Spreadsheet Geeks Concatenate!

    Quote Originally Posted by jer_33 View Post
    Walk me through this... Are you looking for totals? Looking for stats for an individual day?
    Looking to have player points totals update in my spreadsheet automatically (whether it's daily, weekly, or immediately, doesn't matter).
    Associate Editor for DobberHockey (Wednesdays). Click that Ramblings button on the the menu bar!
    (No I don't have a hockey problem...)

  8. #8
    Location
    Thunder Bay, ON
    Rep Power
    18

    Dobber Sports Blue-Chipper

    Default Re: Spreadsheet Geeks Concatenate!

    Quick Lesson using Google Spreadsheets and the IMPORTHTML function:

    1. Open Google Spreadsheets (you'll need to sign-up/login with Google if you haven't already)
    2. Open a new blank spreadsheet (It'll start you with a single tab called 'Sheet 1')
    3. You cursor should be in cell A1, if it isn't just left click with your mouse in cell A1
    4. Copy this statement: =importhtml("https://www.hockey-reference.com/leagues/NHL_2020_skaters.html", "table", 1)
    5. Paste it in cell A1 and press enter
    6. Click where it says 'Untitled Spreadsheet' and give it a name
    7. You are now the proud owner of a spreadsheet that will automatically update the skater stats from Hockey-Reference everyday
    8. Bookmark the link, or just leave it open in your browser for daily visits.

    Here is an example that I made up to share, I don't normally publish spreadsheets - but you should be able to click 'Make a Copy' from the file menu and rename it anything you want.

    https://docs.google.com/spreadsheets...it?usp=sharing
     

    Comish Dynasty 2021-22 (24 Team Roto Dynasty, Custom Salaries)
    Minnesota North Stars, 2nd Tier
    C - Aho, Rakell, Foligno, Trenin (Janmark, Steel, Kurashev)
    LW - Hall, Lucic, Sanford, Bellows (Vesalainen, Foote, Katchouk)
    RW - Seguin, Toffoli, Jaskin, Donskoi (Jarvis)
    D - Sergachev, Edler, Petterson, Forbort, Ceci, Valimaki (H.Fleury, Alexeyev, Salo)
    G - Price, Brossoit (Stolarz)

  9. #9
    Location
    Thunder Bay, ON
    Rep Power
    18

    Dobber Sports Blue-Chipper

    Default Re: Spreadsheet Geeks Concatenate!

    Quote Originally Posted by Eskimo Brother View Post
    Looking to have player points totals update in my spreadsheet automatically (whether it's daily, weekly, or immediately, doesn't matter).
    In Excel you can do this using 'New Web Query', but there are more steps. May also depend on your version of Excel.

    Here's a quick tutorial.... http://probiztechnology.com/blog/201...es-into-excel/
     

    Comish Dynasty 2021-22 (24 Team Roto Dynasty, Custom Salaries)
    Minnesota North Stars, 2nd Tier
    C - Aho, Rakell, Foligno, Trenin (Janmark, Steel, Kurashev)
    LW - Hall, Lucic, Sanford, Bellows (Vesalainen, Foote, Katchouk)
    RW - Seguin, Toffoli, Jaskin, Donskoi (Jarvis)
    D - Sergachev, Edler, Petterson, Forbort, Ceci, Valimaki (H.Fleury, Alexeyev, Salo)
    G - Price, Brossoit (Stolarz)

  10. #10
    Location
    Brampton, Ontario
    Rep Power
    40

    Dobber Sports Titan

    Default Re: Spreadsheet Geeks Concatenate!

    Quote Originally Posted by jer_33 View Post
    In Excel you can do this using 'New Web Query', but there are more steps. May also depend on your version of Excel.

    Here's a quick tutorial.... http://probiztechnology.com/blog/201...es-into-excel/
    I tried a few of the quick tutorials and it seems my system isn't properly set up for all of it, and I was hoping there was an easier way to do it. I'll give the google sheets option a try. Thanks!
    Associate Editor for DobberHockey (Wednesdays). Click that Ramblings button on the the menu bar!
    (No I don't have a hockey problem...)

  11. #11
    Location
    Ontario
    Rep Power
    40

    Administrator

    Default Re: Spreadsheet Geeks Concatenate!

    Just imported Frozen Tools stats page into Excel.

    From Excel:
    Data -> New Query -> From Other Sources -> From Web
    URL: https://frozenpool.dobbersports.com/...pool_stats.php
    Select Table0 and click Load

    To Update:
    Click somewhere in your data
    Data -> Refresh All -> Refresh

    (Yes I shamelessly plugged my own website )


    Contact me for Frozen Tools bug reports and inquiries
    Follow Frozen Tools on Twitter @FrozenTools
    Follow me on Twitter @DH_EricDaoust

  12. #12
    Location
    Brampton, Ontario
    Rep Power
    40

    Dobber Sports Titan

    Default Re: Spreadsheet Geeks Concatenate!

    Thanks guys, I'm sure I'll get to sorting that out. However, it's only the tip of the iceberg of what I need.
    Ideally I'm trying to get points to specifically update inside my existing table to line up with the order players are in now. Though I guess I could probably do that with a lookup or something, can't I?
    Associate Editor for DobberHockey (Wednesdays). Click that Ramblings button on the the menu bar!
    (No I don't have a hockey problem...)

  13. #13
    Location
    Thunder Bay, ON
    Rep Power
    18

    Dobber Sports Blue-Chipper

    Default Re: Spreadsheet Geeks Concatenate!

    Awesome Eric, the website obviously allows you to do a lot of data manipulation on the site itself - but it is great to have it imported into a spreadsheet to be further sorted.

    If you want to import a FROZEN TOOLS report into Google Spreadsheets, just open a new tab and use the function: =importhtml("https://frozenpool.dobbersports.com/frozenpool_stats.php", "table", 0)

    I've also added it to the example spreadsheet listed in a previous post.
     

    Comish Dynasty 2021-22 (24 Team Roto Dynasty, Custom Salaries)
    Minnesota North Stars, 2nd Tier
    C - Aho, Rakell, Foligno, Trenin (Janmark, Steel, Kurashev)
    LW - Hall, Lucic, Sanford, Bellows (Vesalainen, Foote, Katchouk)
    RW - Seguin, Toffoli, Jaskin, Donskoi (Jarvis)
    D - Sergachev, Edler, Petterson, Forbort, Ceci, Valimaki (H.Fleury, Alexeyev, Salo)
    G - Price, Brossoit (Stolarz)

  14. #14
    Location
    Brampton, Ontario
    Rep Power
    40

    Dobber Sports Titan

    Default Re: Spreadsheet Geeks Concatenate!

    Quote Originally Posted by ericdaoust View Post
    Just imported Frozen Tools stats page into Excel.

    From Excel:
    Data -> New Query -> From Other Sources -> From Web
    URL: https://frozenpool.dobbersports.com/...pool_stats.php
    Select Table0 and click Load

    To Update:
    Click somewhere in your data
    Data -> Refresh All -> Refresh

    (Yes I shamelessly plugged my own website )
    My excel doesn't have the "New Query" option.
    Associate Editor for DobberHockey (Wednesdays). Click that Ramblings button on the the menu bar!
    (No I don't have a hockey problem...)

  15. #15
    Location
    Ontario
    Rep Power
    40

    Administrator

    Default Re: Spreadsheet Geeks Concatenate!

    I'm using 2016 at work right now.


    Contact me for Frozen Tools bug reports and inquiries
    Follow Frozen Tools on Twitter @FrozenTools
    Follow me on Twitter @DH_EricDaoust

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •