AFFL23 Update #002: League Contract Database

Hi guys, the new contracts spreadsheet is finally done…..mostly. I’m still working out some bugs as I go along, making notes of things I’ll need to fix later, and doing the data entry for all existing contracts (I’m about 20% in on that)……

This thing has been a beast to develop.  To start, my original idea kept evolving and growing in scope as I built it, and this created issues where there were things I was trying to make it do that were beyond my skillset, so a lot of time was spent googling formulas and guides on how to use them.  Also, because it kept evolving, it created a situation where the original idea, the original framework of the spreadsheet I was building, kept growing and getting added onto, and by the time I was ‘almost done’ it looked like I had built a car from 10 different models…..so I actually started over.  From scratch.  I thought the rebuild would be much quicker, because now I had a plan, knew all the formulas and techniques I needed, and knew where all the roadblocks to avoid were………..still took me almost a month for that rebuild, and still found an (almost insurmountable) problem with my whole design once it was finished and I was test running it.

You guys know how I am by now, I started with a good idea, and just kept adjusting and adjusting and adjusting until it almost drove me mad, lol.  It started out fun, but by 2 weeks in it was getting frustrating….8 or 9 weeks later, and I am so damn happy it’s done.  yeah, really that long, working on it almost every day (sometimes for just an hour or so, but some days for 4 or 5).  This thing went all Moby Dick on me.

Anyway…more info on the Data Sheet in a minute, but here’s where we stand:

• The 2023 League Contract Data Sheet is up and running, and is posted with links on the AFFL League Page on the Contract Info Page (and also below)…I have to do this differently now because of the coding, but more on that below
• I’m still doing the data entry, so if I haven’t gotten to your team yet it will be soon….I expect to have the data entry finished in the next 48-72 hours
• As soon as I’m done with the data entry, I’ll start working on updating the Team Data Sheets
  • I’m going to use the existing one (linked above) so all the links on the AF website will still work the same for that one
  • This I’ll have set up much more quickly…about a week.  All I have to do is use the existing pages as templates, move some columns around on each page and then just import the data straight from the main sheet
  • Once the Team sheets are up and running, you’ll be able to see your team data much better than the way i have it displayed in the League Data Sheet
• As soon as I’m done with those, I’ll then update MyFantasyLeague to the new season, update all salaries, cycle the year, etc.  Once that’s done, the 2023 salary cap can be established (need the 2023 salaries for the formula) 
• By then, the Comp Commt should have all contract rules finalized
• so, fingers crossed, all of the above by about 2 weeks from today
• sorry for the delay, but that still gives teams a month to manage their contracts/roster, and we’ve got wiggle room in the calendar to extend it if we need to

Now, onto the League Data Sheet….

• First, for technical reasons, I have to display/share it with ya’ll differently than I have been….
  • anyone that’s interested in spreadsheets and wants to talk shop on the technical issues, just email me
• So, I cannot embed it on a webpage any more, so I can only link it to you
• Also, I have to link each dataset independently (Contracts by Player, Contracts by team, etc) instead of being able to bounce around from tab to tab in the spreadsheet….(I’m working on a fix for this for the future)
• (No worries, the Team Sheet will not have these limitations, and for most of you will probably be the preferred place to see your info)
• Also, I can share an uneditable copy of the whole sheet with you if that’s easier for you to use.  You have to have a gmail account to access it, just let me know if that works better for you (if you know your way around google sheets, it may be easier for you to navigate that way)
• Links to the League Data Sheet ‘Views’ are below, and also linked in buttons on the League page and Contract Info Page
  • Contracts by Team
  • Contracts by Player
  • Contracts by Years Remaining
• Now that it’s finally done and I’m getting data into it and getting to use it….here’s what I think so far….
  • First, if I do say so myself, it’s f-ing beautiful!  Not just the part you all see, but the hidden dashboard part, it’s just awesome.  (anyone into spreadsheets and interested in looking at the guts of it, I’ll give you a blank copy of the master to play with, let me know)
  • It has made data entry so very much easier on me than it was before….what used to take me 5 minutes now takes me like 60 seconds…..honestly the gap may be a little bigger than that, like 6 or 7 minutes vs maybe 45 seconds.  For making contract changes like….a trade for example…used to involve several copy/pastes, and inserting lines and resorting and then editing different statuses, now I just scroll to the contract, hit a dropdown box, change the team, check traded, and bam, done, lol, f-ing awesome
  • I’ve got it all set up with automated formulas and ‘prompts’ based on our rules…..one of the problems I’ve had through the first 2 years is making sure I follow all of our contract rules (like not forgetting when a signing bonus is required, or limits on restructures, etc), and making sure I label everything with consistent wording (like the type of base salary calculation used, or the extension rule used to set up the extension,) etc…..anyway, I have it set up sorta’ so that the data won’t enter if I do it ‘wrong’ based on the other conditions of the contract….really, it’s sorta’ more awesome on the back end than the front
  • That’s all important because it creates 100% accuracy and 100% consistency…..and, it makes it so that anyone could manage the sheet, and long-term, the league would do well to have several of you be able to manage them….I’d like to see the league develop into a sort of co-op over time but that’s a subject for a different email
  • Now, it has some downsides too……first, is the problem with the way I can display it….but I’m working on that
  • also, it’s big….I mean, it’s BIG and HEAVY with formatting so it’s a pain in the ass to load.  the part viewed is only about a third of the sheet (but the whole thing has to load) and there’s anywhere from 4-15 lines of formatting on more than 50% of the cells.  Once it loads though, you’re fine
  • All in all, though, the accuracy it’s going to bring is the main thing.  I’ve got it set up basically as a database, and all the info will stream to the team pages where it’ll be a little more user friendly

As your going through it, here are some notes on a few things things:

• All data on this sheet is ‘cycled’ to the 2023 season…..the 2022 Team Data Sheet is still available at that link for you to cross check your data, but be aware that the data on there IS NOT cycled to 2023 (still shows 2022 data….so if it’s 2 years remaining, the new one will show 1 year, etc)
• “Total Value” equals Base times Length (for extensions it equals BxL + BxL)..signing bonuses and some other bonuses, depending on contract) DO NOT count towards this number, some do…Total Value, is just Base times Length
• “Total Due” is the total amount the player is owed – everything – minus the total already paid – hence, these numbers sometimes, but don’t always, match
• “Total Paid” equals all salaries paid before April 1 of the current year, plus any bonuses (except separation bonuses) + 50% of current year salary……….after Aug 31, that automatically changes to show 100% of current salary paid
• For the Cut Costs, the opposite is true, prior to Aug 31 your cost will show 50%, and after that will show zero (because its already paid)
• “GF’d”……that’s the abbreviation I’m using for “GRANDFATHERED”….anywhere you see it it will be next to a dollar value…IT IS NOT A CHARGE, but rather it indicates where there WOULD be a charge under new rules (it applies mostly to bonuses and guaranteed money) but if you see it, it means the charge has been GRANDFATHERED for that contract
• Typos, etc….
  • There was a WHOLE LOT of data transcription involved, so….be on the lookout for the following potential mistakes:
  • basic typos, name misspellings, things like that (generally, there’s only a risk of this for player names, as well as potentially wrong positions entered
  • I built ‘warning lights’ – for lack of a better word for them – into the spreadsheet to test value amounts balancing, so I’m not too concerned with number amounts being entered wrong (although it’s possible with contracts, especially any that are not basic…extensions, restructured ones, etc…….again, not too worried about this, but double check any numbers that look odd 
  • because I was importing large sets of data into the spreadsheet to then transpose (because of the layout that was quicker than a copy paste) sometimes 20+ columns across…..the one set of potential errors I’m most concerned about is in the yearly salary amount data……example, a contract may have had payments in 2021, 2022, and 2023, and I may have misentered them as ’22, 23, and ’24….or maybe it was a 4 year, and I entered it as 3……so please double check your contracts for this………again, this is the only place I’m really concerned that I may have made an error
  • Eligibilities…..this covers whether players are eligible for Restructure, Roster Bonus, Franchise Tag, or Extension………because I was entering data on existing contracts, and some of the new rules (beginning this year) for these eligibilities are different, I tried to keep the eligibilities based on the old rules, and/or to err on the side of leaving a player eligible…….so, if there’s any in question, get with me and we’ll figure it out
  • Taxi players…..I have not updated taxi info yet, EXCEPT for players that are currently under contract and were put onto taxi B spots last year..…all of these players should be in with their contract, and their contract should be labeled “TAXI” with their salary deferred….please check that yours are tagged as TAXI if this applies  (Taxi A players return to your regular roster so it does not apply
  • There’s a spot (you’ll see it) where contracts that have been traded are tagged as “TRADED”……it’s not important, just for added info on the contract, and I’m sure I missed some of these

That covers just about everything, I think.  Once all of the data entry is complete, I’ll notate that on the League Telex and then get to work updating the Team Data Sheets.  As always, any questions…..

-Crash