PDA

View Full Version : Gear Planner Spreadsheet



Shindoku
08-02-2015, 12:55 PM
Hello everyone! I've been working on this for the last few months, inputting all of the named items in ddo into a spreadsheet. I imagine, like myself, there are people out there who just enjoy making gear sets for their characters or their friends. Myself, I am more of a visual person and would like to see everything laid out in front of me. This is a pretty simple sheet that, by using dropdown menus, you can search through all of ddo's availiable gear for that gear slot. Augments can be chosen in the dropdowns below the item's effects, and should the currently selected item have the corresponding augment type, the chosen augment will be shown in the gear's effect list.

However, this currently is not complete. I have not gotten around to putting in all crafting, things like: Thunder-forged, Temple of Elemental Evil, Cannith Crafting, etc. The same goes for randomly generated gear. I need to input all possible prefixes and suffixes, so it will take some time.

UPDATE:
Alchemical Crafting: Complete!
Thunder-Forged Crafting: Complete!
Tower of Despair Rings: Complete!
Dragontouched Armours: Complete!
Update 31, Gnomeworks loot: Complete!

Currently, I use this in Google Sheets, in which it was made, so that I can share the link and access it with any computer with an internet connection. I opened it in Libre Office, and other than some visual lines missing and not being able to type in your item instead of using the dropdown it works just the same.

This is how it looks when using Google Sheets:
http://oi62.tinypic.com/28j8t4m.jpg

I have had someone experiencing their browser crashing every time they tried to change the gear piece on Google Sheets. If this occurs, I'd suggest downloading it and opening the file with a spreadsheet program.

It can be Viewed/Downloaded on my Google Sheets drive here:
Google Sheets Link (http://bit.ly/GearPlanner106)

Instructions
To save to your computer: File>Download As>[Format] (.xlsx for Excel and LibreOffice)
To save directly to your Google Sheets (should you be logged in): File>Make a Copy...

Or, if you do not feel comfortable accessing it through goggle, it can be downloaded here at my dropbox (if you still wish to have it on your google sheets drive, you can download it and then upload it to your own):
Dropbox Download Link (http://bit.ly/GearPlannerDB106)

If anyone does choose to use this, I hope it helps!

Cordovan
08-04-2015, 01:10 PM
Just wanted to get this effort into the dev tracker. Very nicely done!

UurlockYgmeov
08-04-2015, 01:38 PM
lots of work. looks good!

cupajoe
08-04-2015, 02:10 PM
Wow. Awesome job.

Lagin
08-04-2015, 03:10 PM
nice work,
+1

Untitled
08-04-2015, 03:52 PM
Glad to see you finally posted it! Looking awesome so far. You even have a few people that have found bugs, next time you are in DDOChat.com (http://www.ddochat.com) hit up Dagaz for more info.

LrdSlvrhnd
08-04-2015, 04:43 PM
Awesome! Looks great, you clearly put a lot of work into it!

noneill
08-04-2015, 04:55 PM
Awesome!

geoffhanna
08-04-2015, 05:56 PM
Very nice, Shin, very nice!

Shindoku
08-04-2015, 08:12 PM
Thanks for the bump Cordovan! Currently I'm working on the Cannith Crafting effects, and it will take a bit as I need to piece together some information. If you guys have any bugs you want to report, just drop them in here and I'll try to get to them fixed.

Xionanx
08-04-2015, 08:29 PM
Totally rep worthy!

Jango-EX
08-04-2015, 09:49 PM
Oooo looks awesome!

PNP
08-05-2015, 05:30 AM
wow - very nice - being trying to do something similar for my bank toons (18 in total) so this is going to come in handy as both gear planner and bank with minor changes.

Very nice again and thanks for sharing

EllisDee37
08-05-2015, 01:04 PM
Having put some cursory thought into a gear planner myself, I see you went with the approach of creating multiple copies of named items that have variable effects. (eg: Gianthold dragon helms.) I considered that but then Dream Edge (http://ddowiki.com/page/Item:Dream_Edge) convinced me to look for an alternative. 100 variations for one item is...excessive.

That's when I stopped thinking about it. Maybe it would be good to set up variable-effect named items in a similar way to cannith crafting / greensteel / thunderforge items, all of which would ideally be supported. But whatever "that way" would be is still a blank to me.


Thanks for the bump Cordovan! Currently I'm working on the Cannith Crafting effects, and it will take a bit as I need to piece together some information. If you guys have any bugs you want to report, just drop them in here and I'll try to get to them fixed.If you PM jjflanigan he might let you bulk download his cannith crafting shard database.

Shindoku
08-05-2015, 01:18 PM
Maybe it would be good to set up variable-effect named items in a similar way to cannith crafting / greensteel / thunderforge items, all of which would ideally be supported. But whatever "that way" would be is still a blank to me.

I've thought of this, but I wanted the planner to have most of the functionality to it on the first page. Only moving to the other pages when customization of a crafted item was needed. I've been putting the crafting dropdowns at the top of the page the item resides in so that users wouldn't have to rifle through the list to find the item they wish to customize (this was especially apparent in weapons). I will consider this in the future though.


If you PM jjflanigan he might let you bulk download his cannith crafting shard database.

I'll do just that, it could save me quite a few hours!

EdwinRophe
08-05-2015, 03:12 PM
Was looking at this and it occurred to me that it would be helpful if you included the min lvl in the name of items if possible. It looks like you did some of the work in Gloves and armour, but if you added it to the end of the name so it would still sort Alphabetically that'd be cool. I don't know it you hand typed that or copy/pasted from the wiki or what, but thank you so much for your effort.

I have all kinds of uses for this

Shindoku
08-05-2015, 07:49 PM
Yes, I plan to have Minimum Levels added in the future with a box on the front page showing the minimum level the gearset can be worn at. I've also wanted to add a gear checklist, where items will state something is missing (such as Fortification). The minimum level addition would have to come after all of the crafting stuff though. It is not something that is entirely needed, but I did add it in there for the future.

kanordog
08-06-2015, 03:19 AM
Awesome, thanks for the work You put in!

Shindoku
08-11-2015, 12:19 PM
Just an update for the planner. I am working on cannith crafting currently with the info that Jjflanigan kindly gave me. It is going to take a while as there are a lot of effects. However, it will not have the proper restrictions of the builder on cubicleninja. Talking with Untitled at the bar last night, it was brought to my attention that he the restrictions were going to be part of it, and others may have the same thought. The gear planner is not meant to be used as a replacement for a cannith crafting calculator, but a representation of what gear you possess/wish to possess and make a set of gear with less overlap in effects.

whereispowderedsilve
08-11-2015, 10:16 PM
Good work, my Canadian brother from another mother! Kudos/+1!

/tongue in cheek: To bad you can't put this on a resume or can you...? Hehe! Miss all of you guys buddy! :P! :)! :D!

Tom.JonesJr
09-14-2015, 01:08 PM
Was looking at version 1.02 today and noticed that you have the effects for green steel weapons applied to green steel items. There is a different set of effects for the items. Great Job so far.

Shindoku
10-01-2015, 05:00 PM
I've gone ahead and added:
Update 28, The Devil's Gambit loot.
Sets. Once you have the required items equipped, the set bonus will show up on all of the equipped items (some items do not have the bonuses show up due to reference problems in the sheet.)
Tower of Despair ring upgrades.
Dragontouched Armour effects.
Ability score and Skill calculator. Thanks to Tom.JonesJr, we can now see how much of a desired Stat or Skill we have with the equipped items! Please send him your thanks for his awesome work!


Cannith Crafting is going a little slow. If I find I have a large amount of spare time (and less TR'ing) I'm going to try to blitz it and get it done and out of the way. Random generated items is going to take a while, I'm not just going to put the new stuff but the old stuff as well (which is going to take some research). Thank you to everyone for using the planner, you keep me going :)

If there are any problems with the current version, please let me know and I'll try to fix it immediately!

Shindoku
01-05-2016, 01:58 PM
I've just updated the links with version 1.04 . The newest version has the U29 loot and calculates Saves & Spellpower.

Kantia
01-07-2016, 09:03 PM
I've just updated the links with version 1.04 . The newest version has the U29 loot and calculates Saves & Spellpower.

I love it, this is quite awesome!


I've found a few issues with 1.04.

On the Character Sheet tab, there's no formula for the Mod calculations. I added (to cell F3) =ROUND((D3-10)/2,0)

On the Character Sheet tab, the Skills Gear formula should have an offset of 12 in the HLOOKUP (cells p17:p37)

On the Character Sheet tab, the Stats Gear formula isn't working. I traced this to an issue with the array formula in the Calculations tab. cells A3:AC3 were not set as array formula, AND they had two different values for the array size. (Some mentioned rows 14:278, some mentioned 14:1275. Array formula must have the same size arrays to calculate properly.)

Also, while the Calculations page does include sections for Will, Reflex and Fort, and includes Parry and Riposte, it doesn't seem to do anything with a Resistance item.

Shindoku
01-07-2016, 09:35 PM
I love it, this is quite awesome!


I've found a few issues with 1.04.

On the Character Sheet tab, the Skills Gear formula should have an offset of 12 in the HLOOKUP (cells p17:p37)

On the Character Sheet tab, the Stats Gear formula isn't working. I traced this to an issue with the array formula in the Calculations tab. cells A3:AC3 were not set as array formula, AND they had two different values for the array size. (Some mentioned rows 14:278, some mentioned 14:1275. Array formula must have the same size arrays to calculate properly.)

Also, while the Calculations page does include sections for Will, Reflex and Fort, and includes Parry and Riposte, it doesn't seem to do anything with a Resistance item.

Thank you! I've gone ahead and fixed the bugs you've pointed out on the main sheet. How in the world did I remember riposte but miss resistance!


On the Character Sheet tab, there's no formula for the Mod calculations. I added (to cell F3) =ROUND((D3-10)/2,0)
It still shows a formula on my end, but it is (for some reason) overly complicated. I have gone in an changed it. I hope something isn't being lost upon copying the file in googlesheets or when it is downloaded.

Kantia
01-07-2016, 10:57 PM
I was using Excel to open/edit it, but I have no idea where the issue kicked in!

Thanks for the responsive changes too

Gargoyle69
01-07-2016, 11:50 PM
Hi Shindoku,

That's awesome, I'm super-impressed !

I've located what might be a couple of issues with it too.
- The Stat Total calculations on the Character Sheet tab (Cells D3-D13) are inconsistent, some include all the columns (such as Str which is =H3+J3+L3+N3+P3+R3), and some don't (such as Dex which is only =H5+J5+L5+P5). Just needs to be updated to include them all I think ?
- Melee Combat stats are calculated in the Calculations tab (Cells AQ13 to AR13), but not showing on the Character Sheet tab (Cells E50, E51, J50 & J51), just needs the references updated (example, E50 =Calculations!AQ13)
- Speed doesn't appear to show in the Calculations tab (Column AS) (example, add Skirmisher's Belt), and also doesn't have a spot to show on the Character Sheet tab (this might be tricky, as Speed obviously has Alacrity and Striding stacking issues, but it would be amazing to have both stats showing separately!!)
- In the spellpower section of the Calculations sheet (Cells AD13 to AM13), only Corrosion (AD13) adds in results from Potency (Cell AN13), the other spell powers don't (just needs the formulas from AD13 extended across to AM13 ?)
- Data typo for Ship Kickers (Boots tab, Cell D111), "Balance +15 Skill" instead of "Balance Skill +15"
- Spellpowers don't add Implement bonuses & Skill bonuses (ie Spellcraft, Heal, Perform & Repair) - this might not be a bug, but just something not done (yet) ?

Thanks again mate, you're a rock-star :-)

Edit: added a couple more.

Shindoku
01-10-2016, 04:01 PM
- The Stat Total calculations on the Character Sheet tab (Cells D3-D13) are inconsistent, some include all the columns (such as Str which is =H3+J3+L3+N3+P3+R3), and some don't (such as Dex which is only =H5+J5+L5+P5). Just needs to be updated to include them all I think ?
- Data typo for Ship Kickers (Boots tab, Cell D111), "Balance +15 Skill" instead of "Balance Skill +15"

Edit: added a couple more.
I've gone and added the missing columns to the stats and I have fixed my typo on the Ship Kickers (I apparently did the same to the Epic ones).



- Melee Combat stats are calculated in the Calculations tab (Cells AQ13 to AR13), but not showing on the Character Sheet tab (Cells E50, E51, J50 & J51), just needs the references updated (example, E50 =Calculations!AQ13)
- Speed doesn't appear to show in the Calculations tab (Column AS) (example, add Skirmisher's Belt), and also doesn't have a spot to show on the Character Sheet tab (this might be tricky, as Speed obviously has Alacrity and Striding stacking issues, but it would be amazing to have both stats showing separately!!)
- In the spellpower section of the Calculations sheet (Cells AD13 to AM13), only Corrosion (AD13) adds in results from Potency (Cell AN13), the other spell powers don't (just needs the formulas from AD13 extended across to AM13 ?)
The Melee Combat, Ranged Combat, and the Caster stuff wasn't quite complete so I just put # into the boxes, in the future I'll put something like In Development to show it was incomplete. I wasn't planning on having movement speed on display, but it is something I could pretty easily work in. Implement bonuses can be added to spellpower, and to be completely honest I totally forgot about it. I'm unsure as to how I'm going to go about adding them to weapons that have been slotted with spellpower, as it would then appear/increase the Implement Bonus.

For version 5 I plan to have all of the combat stuff done, Spellpowers/Crits/Multipliers, Spell DCs (I am bad at DCs so there may be some bugs/oversight in this). I'm wanting to do a base weapon damage calculator that takes into account all of your gear, and you can just add the bonuses from enhancements/destinies for deadly/seeker (a side by side comparison of weapons could be pretty cool too). However, I'm unsure whether that will be done in time.

Thank you for the bug reporting and the feedback! I hope everyone is enjoying the spreadsheet, and hopefully it makes things a little easier!

cmecu
03-02-2016, 10:40 AM
I was about to make something like this myself , mainly for my characters that I play. I wanted to see the BiS for each level as I level from 1 to 20, and TR. I was going to plan out all my gear in advance, so I can have it ready for when I work on my completionist. I want everything to flow as smoothly as possible.

The other thing I was planning to do is also base the gear on where I would be leveling. I would want to make sure I have the optimal armor and weapons for specific dungeons that have monsters in it that might be resistant to certain things.

It sounds tedious, but it would be nice if all of us could some how break all this information down, and get it all compiled into once place for people like myself and others who are returning, and would like some kind of guide to follow to help us all out when leveling.

Shindoku
03-03-2016, 09:40 PM
I've gone ahead and updated the links with Version 5. I have added spellpowers, spell DCs, melee DCs, melee & ranged combat bonuses, hitpoints, spellpoints. Additionally, as people have been going through the calculation sheet, I rearranged it to make it more user friendly. Allowing users to see where the bonuses are coming from. If there's anything you guys can think of that would be nice to have in the planner, please post and let me know. I'm always looking for things to add beyond what I already have in mind. Also, again if you find any bugs just hit me up and I'll try to fix them as soon as possible!


I wanted to see the BiS for each level as I level from 1 to 20, and TR.

It sounds tedious, but it would be nice if all of us could some how break all this information down, and get it all compiled into once place for people like myself and others who are returning, and would like some kind of guide to follow to help us all out when leveling.

As gear will change from build to build, there's not many absolute universal best in slot items. With so many item effects on named items, crafted items, and randomly generated items, it is very easy to make a gearset with all of the same effects but different gear.

As for the information, I would suggest looking at the wiki. Each quest page displays what enemies you would be fighting throughout that quest.

Tom.JonesJr
03-04-2016, 10:03 AM
I found a small bug for you. It has to do with items that have the effect that adds to all skills of a type ie. Intelligence Skills these are being added to the intelligence score and not the ability score. This might be a time that you have to deviate from the ingame text Changing the text for the Manual of Stealthy Pilfering to "Exceptional Dex Skills(Balance Skill, Hide Skill, Move Silently Skill, Open Lock Skill, Tumble Skill) +4" for the Dex skills worked for me. You could set up some thing like you did with the ghostly, but the ghostly also has an issue that it will be added even if you have another Competence bonus from another source need to use a max with the 2 cells and add that to the total.

Roland_D'Arabel
03-04-2016, 01:04 PM
Sorry for the REALLY stupid question, but how do I download this to use in google sheets? I clicked the link in the OP which opened the doc in google sheets as read only. I can't for the life of me figure out what to do next. :confused:

Shindoku
03-04-2016, 11:16 PM
I found a small bug for you. It has to do with items that have the effect that adds to all skills of a type ie. Intelligence Skills these are being added to the intelligence score and not the ability score. This might be a time that you have to deviate from the ingame text Changing the text for the Manual of Stealthy Pilfering to "Exceptional Dex Skills(Balance Skill, Hide Skill, Move Silently Skill, Open Lock Skill, Tumble Skill) +4" for the Dex skills worked for me. You could set up some thing like you did with the ghostly, but the ghostly also has an issue that it will be added even if you have another Competence bonus from another source need to use a max with the 2 cells and add that to the total.

I changed it to Str, Dex, Con, Int, Wis, and Cha skills. It will not calculate currently, but I'll get around to that. The ghostly part on the other hand shouldn't be added on top of another competence bonus as in the total formula I added a MAX(Ghostly, Competence).


Sorry for the REALLY stupid question, but how do I download this to use in google sheets? I clicked the link in the OP which opened the doc in google sheets as read only. I can't for the life of me figure out what to do next. :confused:

File>Download as> [Format in which you want to save it as]. My apologies, I'll edit the OP when I get the chance with these instructions.

Roland_D'Arabel
03-05-2016, 05:55 PM
. My apologies, I'll edit the OP when I get the chance with these instructions.

No need to apologize! Everyone else figured out what to do I am sure.

So, after choosing Download as> which file format should I download if I want to turn around and open the doc with google sheets?

banjo174
03-05-2016, 10:11 PM
Thank you so much!

Shindoku
03-05-2016, 10:32 PM
No need to apologize! Everyone else figured out what to do I am sure.

So, after choosing Download as> which file format should I download if I want to turn around and open the doc with google sheets?

Usually, you'd want to download as the Microsoft Excel (.xlsx) and open it with Microsoft Excel or LibreOffice. However, if you are logged into your google account, you can make it much easier by just hitting File>Make a Copy... . It will allow you to create a copy to your account that you are free to edit, and access on any computer you wish to log in to google on.


Thank you so much!

No problem!

Roland_D'Arabel
03-06-2016, 12:57 PM
File>make a copy worked great. Thanks for your help and all your efforts with this project!

Shindoku
05-19-2016, 01:42 PM
Update 31 loot is now available in the spreadsheet. I've gone and added a dropdown on most of the Mythicable item's pages to allow for a +1 to +4 boost to the appropriate attribute.

Also, in case anyone is wondering. The mythic boosts of say dual wielded weapons or two rings with mythic boosts DO stack (and do properly in the spread sheet).

Gargoyle69
07-01-2016, 12:59 AM
Update 31 loot is now available in the spreadsheet. I've gone and added a dropdown on most of the Mythicable item's pages to allow for a +1 to +4 boost to the appropriate attribute.

Also, in case anyone is wondering. The mythic boosts of say dual wielded weapons or two rings with mythic boosts DO stack (and do properly in the spread sheet).


Hey Shindoku, loving this spreadsheet and loving it even more that you're keeping on updating it.

Couple of things I found with the latest version:
* Ability Scores on Character Sheet Tab don't include Quality bonuses, looks like calculcations should be replaced with B17, C17, etc
* Something wierd going on with the social skills (Diplo, Haggle, Intim, Perform - but not Bluff for some reason) for example Calculations!N17 is picking up spellpowers in columns AQ/AR ?
* Also something wierd with UMD - giving me result of 0.13, looks like it's picking up ranged alacrity in column BH ?
* Saving THrow Calculations giving error finding "Parrying VIII", same for AC calculation

Keep up the great work ! :-)

Shindoku
07-02-2016, 10:20 AM
Hey Shindoku, loving this spreadsheet and loving it even more that you're keeping on updating it.

Couple of things I found with the latest version:
* Ability Scores on Character Sheet Tab don't include Quality bonuses, looks like calculcations should be replaced with B17, C17, etc
* Something wierd going on with the social skills (Diplo, Haggle, Intim, Perform - but not Bluff for some reason) for example Calculations!N17 is picking up spellpowers in columns AQ/AR ?
* Also something wierd with UMD - giving me result of 0.13, looks like it's picking up ranged alacrity in column BH ?
* Saving THrow Calculations giving error finding "Parrying VIII", same for AC calculation

Keep up the great work ! :-)

Thank you! Unfortunately, one of the things I have noticed is: goggle docs is absolutely terrible for storing a large amount of calculations. I have gone over the calculations sheet every update for the last 4 updates and completely rewrote the formula. It will save the calculation, but upon loading up the formula, some of the references get replaced with other cells. And from your last comment, it seems that it happens to data as well. I changed all references of parrying to +1 etc. Epic armbands of the silenced ones' insightful dex keeps losing insightful. It's a train wreck. What I'm going to do is fix all of it in Excell, (hopefully it doesn't do the same) and put a single download on the front page age. Leaving the other link for those who want to fiddle around with the Google doc.

Tom.JonesJr
07-06-2016, 10:17 AM
I have downloaded the file from the dropbox link and when I try opening it in excel 2013 it tells me that errors were found then it tries to fix and removes several formulas.

Shindoku
07-06-2016, 01:01 PM
I have downloaded the file from the dropbox link and when I try opening it in excel 2013 it tells me that errors were found then it tries to fix and removes several formulas.

I'll play around with them to see if I can get them working. It's most likely a mix of google uses arrays that other programs do not and the data storage problem from google, as I download the sheet from googlesheets after I have updated it there. I usually open downloaded sheets in LibreOffice, which doesn't seem to have a MULTIPLY (I'm unsure why). Would it be possible to get which categories have missing formulae from you?

In the meantime I'll remove the calculations tab and character sheet tab until things are straightened out.

Tom.JonesJr
07-07-2016, 07:28 AM
I'll play around with them to see if I can get them working. It's most likely a mix of google uses arrays that other programs do not and the data storage problem from google, as I download the sheet from googlesheets after I have updated it there. I usually open downloaded sheets in LibreOffice, which doesn't seem to have a MULTIPLY (I'm unsure why). Would it be possible to get which categories have missing formulae from you?

In the meantime I'll remove the calculations tab and character sheet tab until things are straightened out.

I think what we really need to do is get moving on fixing the Gear Planner in the Character Planner. I have almost finished with my new screens only have 2 left. I think I will move on to the Gear planner after that before the Destiny Panel. We should have the new Cannith Crafting out by then to see how that will effect us.

Sam-u-r-eye
01-26-2017, 12:29 PM
+1 great planner

Hostyle
12-25-2017, 04:38 AM
Amazing work! Thank you, +1 rep