Jump to content
Sign in to follow this  
Mike MacGregor

Speeding up the CC/DB for the draft

Recommended Posts

A few people have emailed me about the speed of the CC/DB and wondered how to speed it up. To some extent we are limited by Excel. There is a lot packed in there.

 

Plus, calculations are set to Automatic (the default setting in Excel), so each time something changes, the formulas update everywhere. In the past, calculation in Draft Buddy was set to Manual for the purpose of increasing its speed. The downside on that is it was much more complex to make sure everything updated at the right time, and ergo, it was less reliable. That is why the clock is gone currently. If anyone remembers, that was the cause of issues just trying to copy and paste some data from one tab to another in Buddy, because of the Manual calculation. I really wanted to have us using Automatic calculation in the 2011 version.

 

All that said, I'd like to start brainstorming some ways to increase the speed of the CC/DB once you get to the point of doing the draft. These are not confirmed to work (yet!), but here are some thoughts which I'm testing but you might also want to test.

 

A. Delete stuff that isn't needed

 

In theory, by shrinking the file and having a lot fewer formulas should speed up the calculations on what remains. Understand once you go past this point you won't be able to Update Projections or Compile Cheatsheets, so this would be a last step prior to going to your draft - if we determine this works.

 

1. Make a second copy of your CC/DB first and foremost, so you have a copy with it "as-is" and a copy we can mess around with.

 

2. In terms of deleting things, at the point the positional tabs become fairly useless, and they have a lot of data, except for the fact the CC/DB looks up the team and bye weeks for the players from these tabs. So, try the following:

 

- Go to the QB tab, click on a cell in Column G.

- Press CTRL-SHIFT-END* to highlight all cells from the cell you selected in G to the lower-right end of the tab

- Select Edit > Delete > Delete Entire Columns

- This will wipe out all the data on the QB tab except the player name, team abbreviation, bye week and depth chart number

 

- Repeat for each position tab, except DEF start in Column F instead of G

- If your league does not use IDP, then just completely delete the DL, LB and DB tabs by right-clicking on them and choosing Delete

 

* CTRL-SHIFT-FN-[right ARROW] on a MacBook Pro - no END key on a MacBook Pro. Just learned this shortcut this morning.

 

Note on my Mac I was having some issues with Excel crashing because it didn't like me doing the mass deletes, particularly when I deleted whole tabs. Save often. Also try disabling macros when opening, which may help.

 

3. Next I deleted the following tabs:

 

- TD Distance

- player (this will no longer work with the position tabs cleared out of all the data that feeds the player tab)

- offense-notes (I'm using the other cheatsheets)

- overall-notes

 

4. I unhid and deleted the following tabs that weren't used for my particular league:

 

- idp

- idp depth

- idp-notes

-auction report

 

5. I hid the following tabs:

 

- #1 - #5: rules, scoring, custom scoring, owners, options, action

- position tabs

 

This should actually make anything faster, but it does look cleaner with not as many tabs to look at across the bottom.

 

6. Save the file, close it. I would go so far as to shutdown Excel. Restart it and re-open the dumbed down CC/DB.

 

Note: In addition to not being able to Update Projections or Compile Cheatsheets, you also will not be able to re-rank players any more, if you've deleted the alternate cheatsheets. Re-rank looks for the alternate cheatsheets (step 3) to keep the rankings consistent between both.

 

B. Set Calculation to Manual

 

My concern about this is the Draft Player and Undo Draft Pick buttons will work properly, but it is worth trying. To change the calculation, select Office Button > Excel Options > Calculation in Excel 2007 and 2010. On a Mac with Excel 2011, Excel > Preferences > Calculation. On older versions of Excel, Tools > Options > Calculation.

 

Take note in each case what key you need to press to Recalculate cells, as you'll probably want to do it periodically while drafting (not necessarily each pick). It is F9 on Windows.

 

I haven't tried this yet, but like I said at the top, it is worth a try.

 

Lets get the discussion going.

Share this post


Link to post
Share on other sites

Hello Mike,

Since we were discussing this yesterday I figured I would share my results for everyone to read.

 

I took a very hack approach to what Mike has suggested in column A. I simply deleted the players tab as well as each tab for each position. I also deleted the TD distance tab. My approach resulted in some other minor things becoming messed up (ex. team names not showing up next to players). Mike's instructions would remedy the issues I encountered from just going nuts and deleting stuff outright. I didn't run through any of the other steps Mike has listed under "A". Unfortunately with what I did I didn't notice much of a speed increase if any. I'm estimating that each time I click "Draft Player" Excel is taking about 7 seconds to calculate cells. It doesn't seem like much but when you're running a draft, as I am, and the picks are flying that 7 seconds really puts me behind.

 

It would be great if people could provide their results here if you experiment with speeding things up.

 

Mike, I assume that you've done everything possible from a coding perspective. I've googled this issue and found a number of suggestions on how to write certain formulas in VB that is specifically aimed at keeping excel moving quickly. I'm not sure if those would work in this case or at all but I just want to make sure you're aware of them.

Share this post


Link to post
Share on other sites

Yep, I know the way Excel records macros is not necessarily the most efficient. With the code evolving over years and years, some may have slipped through the cracks, but I think I've pretty much got it paired down as best I can. The other thing I have to balance out is the code has to work in all versions of Excel so that sometimes limits the changes I can make.

 

7 seconds is long. On my Mac I've already got 15+ rounds in a draft and hitting Draft Player updates the pick and gets ready for the next one in about 1-2 seconds.

Share this post


Link to post
Share on other sites

7 seconds is long. On my Mac I've already got 15+ rounds in a draft and hitting Draft Player updates the pick and gets ready for the next one in about 1-2 seconds.

 

 

That's interesting. Is it possible that the issue could be with my laptop? Are other users experiencing the same kind of slow down that I am?

Share this post


Link to post
Share on other sites

The computer speed will have a lot to do with it. Make sure no other spreadsheets are open in Excel. Try to minimize other stuff running if possible. Windows can and will bog down if the computer has been on for a long time, so sometimes a total shutdown and restart will help.

Share this post


Link to post
Share on other sites

The computer speed will have a lot to do with it. Make sure no other spreadsheets are open in Excel. Try to minimize other stuff running if possible. Windows can and will bog down if the computer has been on for a long time, so sometimes a total shutdown and restart will help.

 

I'll test it out tonight. Thanks.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×