WotLK DPS spreadsheet - Page 122

Haldhur · 07/10/09, 3:26 PM

Originally Posted by VRoscioli

Can you be more specific? What isn't working?

when i push the test rotation:

Shandara · 07/10/09, 3:35 PM

I had noticed your bug report, but I have been unable to reproduce it myself. I'm puzzled why it would error on that particular line.

EDIT:
I've uploaded v91beta here. Please try it out and let me know what you think or could be done differently.

For comparisons, on my computer (E8500 dual-core 3.16 ghz, 4gb RAM). The full check takes 160seconds for ~900 items. At 190ms per item. Naturally that includes many items that are obsolete.

If I select just 1 or 2 slots the check is done in 5-6 seconds.

The re-gemming feature on the Gear tab is also used by the Gear Planner when dealing with sockets/+hit on items. Let me know if you think certain gemming decisions it makes are bad.

• Tsook · 07/10/09, 5:47 PM

Originally Posted by Shandara

You can deselect all items that you don't want by removing their drop locations.

Deselecting the following:
* Non-raid Gear
* Pre-WotLK

Should get rid of all the blues and Heroic Dungeon drops.

Yeah, you're right, of course. My example was bad. I should have said something along the lines of "I have Lotrafen but I don't have Twisted Visage, so it doesn't need to bother trying Twisted Visage."

But considering it now that I've had a chance to wake up, what I was thinking about is really a different use case than what the Gear Planner is for now, which is generally "planning upgrade paths," not necessarily "finding the optimal subset of gear given a set of gear as input." It's just this latest feature has got me thinking: if you've already coded up the ability to have the spreadsheet swap things around and re-gem appropriately, going that one step further to have it try multiple combinations could largely obviate the need for the "swap in this hit piece, swap out that other hit piece and replace it with a set piece to keep the bonus" kind of work we have to do quite often when evaluating potential upgrades.

Unless one gems for a majority of one's hit, the intelligent regemming you've implemented won't be able to really effectively deal with the kind of situation where you'd have to swap an entire other hit item out to make the item in question an upgrade.

Of course, the number of combinations has exponential growth with the number of items selected for an optimizer, so I wouldn't be surprised if you felt it was not likely to be possible in reasonable runtime using excel scripting.

Shandara · 07/10/09, 6:00 PM

While this would be possible to do, it wouldn't be, as you say, very fast.

I've pretty much reached the limit of how fast I can make Excel recalculate the sheet's DPS. Even with rigorous limiting of what cells/sheets get actually calculated (this gave a HUGE boost in speed in the v91beta), there are still a lot of formulas to go through on each change.

VBA is in itself fairly fast for a real-time interpreted scripting language, but it's the interfacing with Excel cells/formulas that incurs the big penalties.

jmichaelp · 07/11/09, 1:07 AM

Originally Posted by Iru

When you change talents are you changing specs or just moving points around in a tree? Newer versions of the spreadsheet default to rotation tests rather than distribution frequencies so changing specs means you MUST change the shot rotation to see true DPS, the spreadsheet will no longer guess for you.

Also, you don't have 'Randomise Procs on Rotation Test' set to yes by accident do you? While the rotation test approach is generally considered more accurate, the random procs method will result in different DPS numbers each time you run the test, even if you haven't changed anything.

Hi, thanks for the reply. I'm changing specs, so it seems that changing the rotation might be the problem. If the case is that I have to go change the shot rotation every time I change spec, then the spreadsheet becomes really unwieldy & I'll probably stop using it.

Hmmm... I don't see 'Randomise Procs on Rotation Test' anywhere lol so I probably didn't change it from it's original setting.

While I appreciate the work being done on this spreadsheet, it seems that missing out on developments in the past couple of versions may have left me behind the learning curve. The spreadsheet may be becoming too complicated & without guidance from documentation I don't know how I'll be able to use it any more.

Shandara · 07/11/09, 6:10 AM

The sheet should automatically re-run the test if you make changes to the spec. In the latest few versions that has been the default. It's even smart enough not to use shots any more that you untalented.

PanvA · 07/11/09, 7:27 AM

My feedback for 91beta
I'm missing an option to turn the automatic regem off, it can be somewhat annoying when i'm trying to put some gear together and it keeps toying with the gemming. Then what would be really great is tolerated missing hit rating, with my current gear im at 99.96%, which is ok for me, i dont feel the need to cap the remaining .04% but the sheet wont let me change the default yellow mixed hit gem with agility one. Also, as pointed out before, if the recalc DPS function at Gear Planner worked only for gear of which source we have selected.

Other than that im really happy with the spreadsheet, its an awesome tool. And i really feel like that gemming feature is great "kick off" for something i'd really like to see in the future, which is a tab with all the gear in cells and checkboxes next to them, which would, if checked, mean that the gear is in your possession, so we would check 3 helmets, 2 weapons, etc, and then the option to calculate the best combination of those followed by proper gemming.

Shandara · 07/11/09, 8:11 AM

Originally Posted by PanvA

My feedback for 91beta
I'm missing an option to turn the automatic regem off, it can be somewhat annoying when i'm trying to put some gear together and it keeps toying with the gemming. Then what would be really great is tolerated missing hit rating, with my current gear im at 99.96%, which is ok for me, i dont feel the need to cap the remaining .04% but the sheet wont let me change the default yellow mixed hit gem with agility one. Also, as pointed out before, if the recalc DPS function at Gear Planner worked only for gear of which source we have selected.

Other than that im really happy with the spreadsheet, its an awesome tool. And i really feel like that gemming feature is great "kick off" for something i'd really like to see in the future, which is a tab with all the gear in cells and checkboxes next to them, which would, if checked, mean that the gear is in your possession, so we would check 3 helmets, 2 weapons, etc, and then the option to calculate the best combination of those followed by proper gemming.

The automatic gemming is something I turned off rather quick too! It's probably better if you have to press the button to make it do it, rather than have it automatic on every change.

As for having a Rawr-like optimisation feature. I think it's doable if I severely limit the amount of items used in the calculations (otherwise it would take very long).

Cranch · 07/11/09, 2:49 PM

There appears to be a bug with 91beta. Load my gear (Cranch,Perenolde,U.S.). Note I have 265 hit. Change the boot enchant from Icewalker to Superior Agility. Note I now have 269 hit (even though I subtracted 12 hit by removing the enchant.) +16 hit is coming from somewhere.

*edit* Ah, it did change my existing gem slots. I had assumed that the automatic regemming applied only to the Gear Planner, not when you were changing gear yourself in the Gear tab.

I definitely want to turn off automatic regemming for the Gear tab.

Anindor · 07/11/09, 3:21 PM

I think that's part of the automatic re-gemming to stay at hit cap that was recently added. Check if it put a hit gem on you.

Iru · 07/11/09, 5:40 PM

Originally Posted by Shandara

The sheet should automatically re-run the test if you make changes to the spec. In the latest few versions that has been the default. It's even smart enough not to use shots any more that you untalented.

Does it also load a completely new shot rotation when changing the talent build, say from SV to MM? I think that's what the original poster is doing, and in that case they will see a significant DPS loss because the spreadsheet isn't adding Chimera Shot to the rotation if it was originally built for an SV talent spec, or adding Explosive Shot & Black Arrow if going from MM to SV. This is also true when using the alternate spec mechanism on the talent in 90b; the shot table remains constant between the two specs.

Bug report: in 90b, while fiddling with the spreadsheet testing some of this I discovered an amusing bug. If I add Chimera Shot to the shot rotation but do not have the talent for it, the spreadsheet still thinks Serpents Sting will be refreshed, essentially nullifying its damage. This prevents someone from simply loading as many shots as they can and then relying on the spreadsheet to ignore those for which they do not have the talents.

To OP: I suggest using two profiles. Load your current gear, spec, glyphs & shot rotation into one, save it, and then change the profile name on the Gear tab and save the second profile. You can then change the spec, glyphs & rotation as required, re-calculate, and use the overview tab to compare the two builds. The profiles section on that tab will show your overall DPS from the first profile, and the hunter statistics section will show your current DPS. BTW, the spreadsheet has ALWAYS required you to change the shot list in the shot rotation tab, even before the rotation test became the default.

Kharthus · 07/12/09, 12:37 AM

Originally Posted by Shandara

I had noticed your bug report, but I have been unable to reproduce it myself. I'm puzzled why it would error on that particular line.

EDIT:
I've uploaded v91beta here. Please try it out and let me know what you think or could be done differently.

For comparisons, on my computer (E8500 dual-core 3.16 ghz, 4gb RAM). The full check takes 160seconds for ~900 items. At 190ms per item. Naturally that includes many items that are obsolete.

If I select just 1 or 2 slots the check is done in 5-6 seconds.

The re-gemming feature on the Gear tab is also used by the Gear Planner when dealing with sockets/+hit on items. Let me know if you think certain gemming decisions it makes are bad.

Couple comments:

1. The new gear planner is very slow. I would much rather have a fairly accurate ranking of gear that runs instantly than a very accurate ranking of gear that takes 2-3 mins to run.

2. The ranking using the auto-gemming is very bad currently. It will remove all my JC gems and replace them with agi gems. It will also remove gems that allow my meta gem to remain active. This causes the gear planner rankings to get totally out of whack.

3. The auto-gemming on the gear page doesn't seem to take socket bonuses into account and you can swap gear around and return to your original gear and you will end up with a suboptimal gem setup. Personally I don't want the gear page auto-swapping gems for me so I would love to turn this feature off.

Argg0 · 07/12/09, 4:01 AM

114 AP (LW enchant) isn't supported by the armory import on Beta (I can however put the 130 enchant manually).

The thing with the gems is pretty annoying... it won't let me gem the way I want (I wanna gem ArP), and it tries to gem while it removes all gear and while it loads each part of the gear from armory.

Edit: While testing BM.

Talents+Procs -1,11 -0,03%

I can't figure why is this being negative. What talent/proc could lower my DPS?

(also, lol at me for having so much ArP on my gear that makes ArP > AP for BM)

Shandara · 07/12/09, 9:17 AM

New beta version, here.

Improved the gear planner further, it's (again) much faster. Doing all 900 items in the sheet took less than a minute now. 30 seconds for the usual sub-set I took (no pre-WotLK, non-raid or PvP gear, about 400 items).

Also now includes a gear optimizer, which uses the routines I made to determine the best possible set. You can select up to 5 items for each slot (and an enchant that's used for all of them) and it will give you the set that has the highest DPS out of them.

Naturally, it doesn't try ALL the combinations (which for 5 items for each slot is in the order of a few billion), but it has a fairly simple algorithm.

It did however, give me a set for the items I have in my bank that's about 100 dps higher than what I had.

EDIT: small re-upload, adding Dragon's Eyes support to the regemmer.
EDIT#2: some small bugfixes

Cranch · 07/12/09, 12:45 PM

A couple of quick observations on the latest beta:

1. In the Gear Planner tab, the Ranged Weapons list isn't sorted.

2. There should be a button on the gear optimizer to load the gear and enchants from a selected profile as one of the 5 entries.

3. The optimizer ran for 8 out of 20, and then just stopped. It's not clear where its output is.

Daymaker · 07/12/09, 1:03 PM

Something must surely be wrong here, I downloaded that latest version, plugged in my stuff and recalculated dps, and it took 740 seconds for 425 items, 1743ms per item.

Running excel 2007 on w7 RC, core 2 quad 6600 and 4gb ram.

Shandara · 07/12/09, 1:10 PM

Originally Posted by Cranch

A couple of quick observations on the latest beta:

1. In the Gear Planner tab, the Ranged Weapons list isn't sorted.

2. There should be a button on the gear optimizer to load the gear and enchants from a selected profile as one of the 5 entries.

3. The optimizer ran for 8 out of 20, and then just stopped. It's not clear where its output is.

#1 is fixed already in my current development build.
#2 I've also added an option to use the current set of Gear, but this is a good suggestion.

I forgot to add a message about point 3. If the optimizer reaches a point of no further improvements early, it'll just stop. Output is in the Gear tab, where you'll find the optimized set of items.

@Daymaker:
740 seconds is way too long. It's never taken more than 2mins even in the early stages with all the debug code in. I'm not sure what is causing the delay for you. I have made a few re-uploads today, which might confuse your browser. Can you try clearing your browser cache and redownload, then try again?

Argg0 · 07/12/09, 1:49 PM

I'm getting a runtime '1004' while trying to calc atributes.

 If test Then Call testRotation

    baseDPS = Worksheets("gear").Range("TotalGearDps").value
    BasePetDPS = Worksheets("Pet").Range("PetTotalDPS").value
    
    'Set Assume 100% to No
    prevHit = Worksheets("Gear Planner").Range("Assume100Hit").value
    Worksheets("Gear Planner").Range("Assume100Hit").value = "No"
    
    'Disable automatic rotation recalculation if it's on
    Worksheets("Shot Rotation").Cells(1, 1).value = "Break"

Error is on the prevHit = ...
(at least it's on yellow)

Did that using my armory gear and talents and enchanting Fur Lining AP on wrist.

Shandara · 07/12/09, 1:53 PM

Originally Posted by Argg0

Error is on the prevHit = ...
(at least it's on yellow)

Did that using my armory gear and talents and enchanting Fur Lining AP on wrist.

Good catch. The assume 100% setting is removed from the beta for the moment, but I forgot to take it out of the calculations. The "calculate attributes" button is not used for the Gear Planner values either.

PanvA · 07/12/09, 2:28 PM

New beta feedback:
All items checked dps recalculation (893 items) took me 203seconds, 227ms per item. - 3,14 DualCore, 4GB RAM, Win7 b7100 - Office 2007

it seems to be more accurate and i didnt find any glitch in there, except for the Waist slot:
Currently i have equipped [Death-warmed Belt], which keeps me almost hit-capped, but still it calculated other belts much better than this one. For example [Belt of the Betrayed] got +48.63 delta dps, which is wrong, when i equipped the supposedly better belt i ended up 30dps lower and 1.3% under hit cap, so i tried to gem it with the best hit gem, instead of agility one, and ended up not 48 but only 10dps better. So where did the 48 came from.

Is there a way to see which elements and settings is calculated with?

Also, thanks for implementing the optimizer function, but same question applies here, what does it calculate with, which gems, does it take [Dragon's Eye] in consideration and would it be possible to have 2 sets on enchants on some items? For example for gloves there are 3 viable enchants, the engineering one, 20hit and last 20 agi. Same applies for feet slot (agi, hit)

Daymaker · 07/12/09, 2:30 PM

I redownloaded and now it took 1309 seconds for 411 items, 3186ms per! I think it doesn't like me :/.

Shandara · 07/12/09, 2:41 PM

Originally Posted by PanvA

New beta feedback:
All items checked dps recalculation (893 items) took me 203seconds, 227ms per item. - 3,14 DualCore, 4GB RAM, Win7 b7100 - Office 2007

it seems to be more accurate and i didnt find any glitch in there, except for the Waist slot:
Currently i have equipped [Death-warmed Belt], which keeps me almost hit-capped, but still it calculated other belts much better than this one. For example [Belt of the Betrayed] got +48.63 delta dps, which is wrong, when i equipped the supposedly better belt i ended up 30dps lower and 1.3% under hit cap, so i tried to gem it with the best hit gem, instead of agility one, and ended up not 48 but only 10dps better. So where did the 48 came from.

Try hitting the re-gem button on the Gear tab once you change the belt and see what it comes up as a gemming overall. The gemmer used by the Gear Planner tries to stay hit-capped whenever possible, so it's likely using a completely different gemming than you are trying to do.

PanvA · 07/12/09, 2:56 PM

Originally Posted by Shandara

Try hitting the re-gem button on the Gear tab once you change the belt and see what it comes up as a gemming overall. The gemmer used by the Gear Planner tries to stay hit-capped whenever possible, so it's likely using a completely different gemming than you are trying to do.

Tried that, ended up only 6 dps better. Anything else i should try?

• Allara · 07/12/09, 3:52 PM

I just pulled down the beta and tried it out. For me, 411 items were checked in 37.64 seconds (92ms per item). Dual core 3 Ghz, no hyperthreading, Excel 2007 with latest service pack.

The calculations themselves also seem spot-on for me. I had already manually plotted an upgrade path which involves a great deal of gear swapping to rearrange my hit (involving 4 slots). The previous version couldn't have come close to suggesting this upgrade path, but the latest beta correctly suggested every single change, along with the proper DPS upgrade amount.

Overall, excellent work, and so far it seems to be working as expected.

Kharthus · 07/12/09, 4:49 PM

Speed is much improved, thank you.

I'm running Excel 2007 under Parallels on my Mac and 351 items took 43s at 121ms/per.

Another nice piece of data on the gear planner would be 'number of items checked'. And maybe even 'estimated time' based on the last run. Currently the number of items only updates after you run.

The gear re-gemming still doesn't take socket bonuses into account. Overall it's much much better than the 1st version though.