2019-20 Google Sheets

Anything goes here OT stuff is OK too!
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

2019-20 Google Sheets

Post by Shoalzie »

The re-design/re-organizing of the sheets is at the 90% stage. I kept some of the stuff I liked about the current sheets and I made some improvements in places that needed it most. I've been targeting the end of this month or early May to have those in your possession in time for the draft and the full offseason.

Your current sheets are officially obsolete. I've updated the contract information to reflect the '19-20 season and I'll continue updating that information as players sign new deals. I'll be updating GP so waiver and slide statuses will be changing from this season. Just stick to managing your team on Fantrax for now.

I spent a lot of this season getting ready for this offseason and giving you a better version of these sheets. They're not meant to be a lot of work for you...just a tool to use in managing your roster and staying organized and engaged with the rest of the league. One of the big improvements will be the ability to auto-fill your lineups if you don't make any changes from the previous week. Plus, the LTIR and relief process will be done with simple check boxes.

I'll post a preview of the sheets in a week or two. I'll also post some form of tutorial shortly after I give you your new sheets. They'll still be heavy on information but much easier to use.

Assuming this next version of the sheets has less issues than this past year, then I'll roll into '20-21 with the same design and you can basically have the new sheets as soon as next season ends.


LINK: https://drive.google.com/drive/folders/ ... YYBUH45l67
DETROIT RED WINGS | ROSTER | FANTRAX
User avatar
dave1959
PostsCOLON 5138
JoinedCOLON Wed Oct 02, 2013 8:43 pm
LocationCOLON Ontario

Re: 2019-20 Google Sheets

Post by dave1959 »

you are the master of Google Sheets!!
Chuck Norris
PostsCOLON 4954
JoinedCOLON Mon May 03, 2010 7:17 pm

Re: 2019-20 Google Sheets

Post by Chuck Norris »

Well done Scott. Thank you for all the time you put in for our league!!!
User avatar
cliff11
PostsCOLON 2644
JoinedCOLON Sat May 08, 2010 12:39 pm

Re: 2019-20 Google Sheets

Post by cliff11 »

awesome man I'm sure they will be sweet
User avatar
Florida Chris
PostsCOLON 2589
JoinedCOLON Tue May 04, 2010 7:06 am

Re: 2019-20 Google Sheets

Post by Florida Chris »

:D
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

Re: 2019-20 Google Sheets

Post by Shoalzie »

Here's your teaser for what's to come in a couple weeks:

https://drive.google.com/open?id=12f_VL ... YYBUH45l67

I'm just sharing the folder for all the sheets for '19-20 but the only one there is for my team. The rest of your sheets will be there when I create those.

Here's notes on what you'll see:
1. "Draft Results" sheet is new for this year. I've got the previous 4 draft years plus 2019 showing the players your franchise selected. The logo next to the player shows their current team affiliation. The 2019 draft results will fill in as we do the draft. I have both the entry and waiver draft results listed.

2. The "Roster" sheet is redone (and improved). I've got all your players categorized by "Contracts" (signed players), "Reserve List" (unsigned prospects) and "Free Agents" (previously signed players without a current contract). "Contracts" are split up by position. That sheet will update with each new week and will reflect any roster moves you might make.

3. I'm only displaying three weekly sheets right now but I'll have all 21 regular season weeks and the playoff rounds included.

4. You'll see the new checkbox shortcuts on the weekly sheets. On "Wk 1", the box in cell A1 is checked and it auto-fills the lineup from "Preseason".

5. The other new checkbox feature is in regards to LTIR and relief. You'll notice I'm using Mike Green as my example under IR. I've checked the box next to him so he appears in "LTIR". In my lineup, I've checked the box next to Jeff Carter to serve as my example for "Relief". He'll show up as relief under the LTIR boxes.

6. New waiver icons are being used. Orange circle with a W means the player must clear waivers. The purple circle with a down arrow means waiver exempt. I don't have a current slide-eligible contract on my team but that icon is now a blue circle with an S.

7. I've added a new set of icons for player status for players under contract. This status comes from CapFriendly. A player that is active in the NHL has a grey shield (resembles the NHL logo). A player in the minor leagues has a green star. I don't have a player that applies to this but a player in Europe or junior hockey has an orange star. For the case of Mike Green, he has the red cross meaning he's on IR. These icons don't control anything, they're just extra information.

8. All players under "MINORS" are any player under contract that you don't have in your lineup. Players that need to clear waivers or are sliding contracts will appear in the appropriate lists. You can see my "Wk 2" sheet that illustrates a sheet that's not filled out.

9. The "RESERVE LIST" and "FREE AGENTS" are the same lists as what you'll see on the "Roster" sheet. Any player without a contract will appear under either category. "Affiliate Prospects" are any unsigned prospect that has their rights owned by an NHL team. "Unaffiliated Prospects" are any unsigned prospect that is undrafted or isn't affiliated with an NHL team. The free agent lists are split up by RFA and UFA. As players sign new contracts, those names will move back into your options to use in your lineup. You can only fill out your lineup with signed players.

It's a lot of information but you'll have all summer to ask questions and to work with your new sheets. I'm thinking you'll get your team sheets sometime before our draft on June 1. I'll be tinkering with my sheets and when I'm done with that, I'll begin creating everyone else's sheets and sending them out to you.
DETROIT RED WINGS | ROSTER | FANTRAX
User avatar
Matthew
PostsCOLON 13682
JoinedCOLON Wed Oct 08, 2014 3:29 pm

Re: 2019-20 Google Sheets

Post by Matthew »

Dude, if you keep developing this, you'll be able to sell this setup to new fantasy hockey league or start your own site. It's pretty cool.

Eventually adding something that shows the number of players a team can retain cap on, with those players listed and the amount listed would be cool. I wouldn't mind if we added that to our own league (in a very limited way).

For example: only being able to do it for the current season or upcoming season one is playing in, and only for nhl players that have signed UFA contracts. That would give older players a bit more value in trades.

But yeah, those google sheets look great, and even easier to use.
ANAHEIM DUCKS | FANTRAX |
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

Re: 2019-20 Google Sheets

Post by Shoalzie »

Cap retention would seem simple to do if we're going off the actual contracts.

For instance, if was I trade to Malkin to someone and they want to just take on 75% of his salary for the remainder of his contract and I'd keep 25% for the remainder of the deal...once that current deal expires, then the cap retention goes away. The acquiring team could actually flip that player later at that reduced cap hit or even retain some of that remaining salary.

With the fact that "dead money" contracts are going away in the next few years (ie--Zetterberg, Franzen, Hossa), teams with less salary would need to find a way to hold onto some cap money to stay above the floor and teams up against the cap could acquire a larger range of players.

It's mutually beneficial and not hard to do. We just need to think of some firm rules. On Google, I could think of a few ways we could apply those rules.

That sounds like a summertime conversation within the CC.
DETROIT RED WINGS | ROSTER | FANTRAX
User avatar
Matthew
PostsCOLON 13682
JoinedCOLON Wed Oct 08, 2014 3:29 pm

Re: 2019-20 Google Sheets

Post by Matthew »

Imo, we shouldn't be able to retain for more than 1 or 2 seasons on a contract, in the same way we cannot trade picks far out. It would help us not cripple teams. I'd also say teams cannot acquire too many and teams cannot trade too many of these contracts so as not to make teams too dominant, or make teams too weak.
ANAHEIM DUCKS | FANTRAX |
User avatar
Shep
PostsCOLON 13901
JoinedCOLON Tue May 04, 2010 2:04 pm

Re: 2019-20 Google Sheets

Post by Shep »

Matthew wroteCOLONImo, we shouldn't be able to retain for more than 1 or 2 seasons on a contract, in the same way we cannot trade picks far out. It would help us not cripple teams. I'd also say teams cannot acquire too many and teams cannot trade too many of these contracts so as not to make teams too dominant, or make teams too weak.
We could make a rule that cap retention similar to how we limit draft picks far out.

Essentially, it's summer 2019, you can only retain cap on players who's contract ends before July 1st, 2021. So basically, players with 2 years left on their contract or less.
Inaugural GM
[STL] 2009 - 2016
[PHI] 2019 -
User avatar
Shep
PostsCOLON 13901
JoinedCOLON Tue May 04, 2010 2:04 pm

Re: 2019-20 Google Sheets

Post by Shep »

BTW, the sheet looks awesome Shoalzie.
Inaugural GM
[STL] 2009 - 2016
[PHI] 2019 -
User avatar
Loki
PostsCOLON 1938
JoinedCOLON Mon Sep 26, 2016 4:38 pm
LocationCOLON Melbourne, AUS

Re: 2019-20 Google Sheets

Post by Loki »

New sheets look amazing, you outdo yourself every year dude :O
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

Re: 2019-20 Google Sheets

Post by Shoalzie »

I've finalized the design and the formulas for the sheets. I've duplicated them for each team. You can view all the sheets in that folder where I posted the link.

I've got to go through each set of sheets to make sure links to other database sheets are established. It'll take a couple days to work through all of it but sometime next week, I'll send them out to you so you can start working with them.

I'll be updating contracts throughout the offseason as well as adding the information for the 2019 draftees.
DETROIT RED WINGS | ROSTER | FANTRAX
User avatar
kimmer
PostsCOLON 18090
JoinedCOLON Thu Sep 02, 2010 2:25 pm
LocationCOLON Ontario, Canada

Re: 2019-20 Google Sheets

Post by kimmer »

Thanks bro
Chuck Norris
PostsCOLON 4954
JoinedCOLON Mon May 03, 2010 7:17 pm

Re: 2019-20 Google Sheets

Post by Chuck Norris »

You're a fucking gem Scott!
User avatar
Shep
PostsCOLON 13901
JoinedCOLON Tue May 04, 2010 2:04 pm

Re: 2019-20 Google Sheets

Post by Shep »

Shoalzie wroteCOLONI've finalized the design and the formulas for the sheets. I've duplicated them for each team. You can view all the sheets in that folder where I posted the link.

I've got to go through each set of sheets to make sure links to other database sheets are established. It'll take a couple days to work through all of it but sometime next week, I'll send them out to you so you can start working with them.

I'll be updating contracts throughout the offseason as well as adding the information for the 2019 draftees.
I've noticed I'm missing "at least" one player that was on my old sheet but not my new sheet.

My numbers show that there are three missing, but I am just looking to see who the other two are.

Alexander Khovanov is the one I know for sure.
Inaugural GM
[STL] 2009 - 2016
[PHI] 2019 -
User avatar
Shep
PostsCOLON 13901
JoinedCOLON Tue May 04, 2010 2:04 pm

Re: 2019-20 Google Sheets

Post by Shep »

Shep wroteCOLON
Shoalzie wroteCOLONI've finalized the design and the formulas for the sheets. I've duplicated them for each team. You can view all the sheets in that folder where I posted the link.

I've got to go through each set of sheets to make sure links to other database sheets are established. It'll take a couple days to work through all of it but sometime next week, I'll send them out to you so you can start working with them.

I'll be updating contracts throughout the offseason as well as adding the information for the 2019 draftees.
I've noticed I'm missing "at least" one player that was on my old sheet but not my new sheet.

My numbers show that there are three missing, but I am just looking to see who the other two are.

Alexander Khovanov is the one I know for sure.
The three are:

Alexander Khovanov
Bobby Butler
Lukas Dostal
Inaugural GM
[STL] 2009 - 2016
[PHI] 2019 -
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

Re: 2019-20 Google Sheets

Post by Shoalzie »

I do have all your sheets linked up to the database and I'll send our your individual shared link in the next day or so.

If you do have a name missing on your sheet, it's based on information that I have entered. Your entire roster is there but certain players don't fall under "CONTRACTS", "RESERVE" or "FREE AGENTS" is a certain value is missing. I will go team-to-team and get those players updated so they'll show up on your sheet. Most likely, those are players that would fall under the "FREE AGENT" category and just don't have "UFA" or "RFA" status.

As an example, I was looking at Cliff's sheet. He's got 75 players on his roster but only 70 were showing up under the categories. I had to add some information for those other players to appear.

I will get that corrected...everything else is working as I expected.


Once all you have access to your sheets, I'll put together some kind of cheat sheet/tutorial and I'm obviously be available for questions.
DETROIT RED WINGS | ROSTER | FANTRAX
User avatar
Shep
PostsCOLON 13901
JoinedCOLON Tue May 04, 2010 2:04 pm

Re: 2019-20 Google Sheets

Post by Shep »

Shoalzie wroteCOLONI do have all your sheets linked up to the database and I'll send our your individual shared link in the next day or so.

If you do have a name missing on your sheet, it's based on information that I have entered. Your entire roster is there but certain players don't fall under "CONTRACTS", "RESERVE" or "FREE AGENTS" is a certain value is missing. I will go team-to-team and get those players updated so they'll show up on your sheet. Most likely, those are players that would fall under the "FREE AGENT" category and just don't have "UFA" or "RFA" status.

As an example, I was looking at Cliff's sheet. He's got 75 players on his roster but only 70 were showing up under the categories. I had to add some information for those other players to appear.

I will get that corrected...everything else is working as I expected.


Once all you have access to your sheets, I'll put together some kind of cheat sheet/tutorial and I'm obviously be available for questions.
Okay I just wanted to bring it up as I am not as familiar with my players and didn't want to accidentally lose one in the waiver draft or something because of a bookkeeping issue.
Inaugural GM
[STL] 2009 - 2016
[PHI] 2019 -
User avatar
Shoalzie
PostsCOLON 12673
JoinedCOLON Mon May 03, 2010 7:28 pm
LocationCOLON Portland, MI
CONTACTCOLON

Re: 2019-20 Google Sheets

Post by Shoalzie »

I saw that issue yesterday. Everyone is probably going to have a player or two or three not show up. But they are on your roster. I have matched your Fantrax rosters.

If you look at your sheet in cell Q8 next to "Players:". That number is the correct number of players on your current roster. You just might not see everyone of them appear under any of the lists.
DETROIT RED WINGS | ROSTER | FANTRAX
BUTTON_POST_REPLY

Return to