Cleric Cast Parse Spreadsheet

Discussion in 'Priests' started by Clarisa, Oct 18, 2017.

  1. Clarisa Augur

    In the interests of helping the cleric community, improving this tool, and hopefully encouraging others to share their tools, I would like to offer the cleric cast parse spreadsheet that I use to evaluate cleric applicants for my guild and for general knowledge and improvement purposes. It has come a long way from the first spreadsheet I came up with, which required a lot of manual data entry that took me a little over an hour for every cast comparison. Thanks to the efforts of fellow guild cleric Panorramix, it now requires just copying/pasting of a few parses from GamParse and provides a lot more data.

    You can download it from the link below, which is a link to a OneDrive folder with the most recent version of the spreadsheet (1.4). Just click download on the page there with the file selected:

    https://1drv.ms/f/s!Ak_B2EdsKUCXgQLsxjzyP9GfTWY4

    Requirements

    There are, however, some requirements. The first is that you need GamParse (any version, though I am sure its creator/contributor would rather you use the latest one) and to use that, you need to log everything involving cleric casts in the game. It is possible, however, to use raw parses generated by someone else for use in the spreadsheet, though the instructions will assume you are logging things yourself. The second is that you need to download either OpenOffice (http://www.openoffice.org/download/index.html) or LibreOffice (http://www.libreoffice.org/download/download/) to use it. These are open-source programs that are available for free and provide functionality that is similar, though more limited, than Microsoft Excel. That might be a deal-breaker for some, but it was important to me that the spreadsheet be usable by anyone without requiring an additional purchase.

    Samples

    Here is what the spreadsheet can give you (not using actual cleric names because not all of those in my guild would be comfortable with their names and data being shared):

    Cast Comparisons by Category

    [IMG]

    Cast Summary

    [IMG]

    Bar Chart (All Clerics)

    [IMG]

    Pie Chart Comparisons (Individuals)

    [IMG]
    [IMG]

    Instructions

    You'll first need to load up a parse in GamParse and combine all of the events you want to compare. Here, I'll just select them all and name them "EOK plus TBM Raids" then click on "Combine Fights" to combine them:

    [IMG]

    Then, you'll need to go to the "Spells and Discs" tab, sort by class by clicking on "Class", then highlight a cleric whose parse you wish to export to the spreadsheet. Here, I will export my parse by clicking on "Export to Forum":

    [IMG]

    Finally, you'll need to open the spreadsheet and right-click on the A1 cell in the "Spells" tab of the spreadsheet and select "Paste" (just press OK if something else pops up after that) to insert the parse into it. To find the "Spells" tab, look at the bottom and above where it says "Sheet 1/5" you should see some arrows to help you navigate the different tabs (Spells, Detailed Breakdown, Glorious Table and Summary, Pie Charts, Cast Time -- ignore that last tab though because it just helps create the "Time" stats and doesn't provide parse-specific information):

    [IMG]

    The spreadsheet will do the rest of the work for you, and you can just navigate the different tabs to view the one you want to see and click on it to bring it up.

    You will have to modify the name of the cleric here on the "Spells" tab if you wish to modify the name throughout the rest of the spreadsheet. That can be done by just clicking on the cell the name is in (here it is A3) and changing the name there to something different -- you won't need to change it anywhere else.

    Then you just need to repeat the process from the selecting the cleric step for every other cleric you want to compare (up to 7 max).

    Troubleshooting

    I haven't had any problems using the spreadsheet but only Pano and I have used it so far. One thing you want to be sure of is that a certain option is enabled in OpenOffice if you're getting 0s for everything:

    [IMG]

    That "regular expressions" option specifically is required for the formulas to work properly.

    Analyzing and Other Stuff

    I left a lot of things out about analyzing the results the spreadsheet gives you, but I will get to that another time if there is enough interest in the spreadsheet. I will say, though, that context is important in analyzing the results of this (or any) parse. So it's best not to view this as a "the one with the best numbers wins" situation or even view it as a competition where there is a "winner". A cast parse also just shows how much something was cast, not how useful those casts actually were. The assumption is, however, that clerics are casting things because they intend for them to be useful (or in the case of heals, casting them as often as possible to heal proactively), so I generally don't expect clerics to "cheat" and just cast things at random to get their numbers up (though that is, of course, always a possibility).

    The group healing situation also determines cast choices to a large degree. A cleric in a group with more tanks who tank simultaneously, for instance, will always have a higher group heal total, and since those spells take longer to cast, they won't be able to cast as many single-target heals. There are, however, subtle aspects to healing that can be uncovered through a parse like this, such as showing when spells and abilities are being under-utilized and the percentage of those casts to other casts. It is also useful to break things down by category (Utility Spells, Buffs, AAs, etc.) because it helps to see a cleric's relative contribution in each of those areas. Again, context is important because if you assign particular clerics to re-buff during combat (we don't) then one cleric is obviously going to do better in that category than everyone else.

    Speaking of categorization, some might not agree with how I have categorized things also. Undying Life, for instance, could be categorized as three different things (a single-target heal, a group heal, or a utility/buff spell due to the HP boost). In the spreadsheet and our guild, we mostly use it as a utility/buff spell because we encourage limiting it to targets that need the HP boost, but in other guilds where clerics are encouraged to just include it in a single-target multibind and it is just cast as another single-target heal, it would probably be more appropriate to categorize it as a fast single-target heal. I don't think it matters either way, however, as a cleric who uses it as a single-target heal without especially considering the HP buff is still providing the HP buff to their target, so it is still being used as a "utility" spell. Some clickies like the Manisi Balm effect from the Small Manisi Branch are included in the AA section as well even though they are not AAs because we wanted to encourage their use (and honestly, they are almost as good as AAs like Burst of Life). There may also be spells missing from the spreadsheet because we don't encourage their use (like Ardent Light). The spreadsheet is kind of hard to modify (the cost of making it easier to input the raw parse data) but if you have any feedback on things you feel would make it better or more usable for you, let me know.

    There is a lot more I could say about analyzing cast parses and about what some of the stats in the summary mean but I will have to save it for another time. Hopefully other clerics find the spreadsheet useful and if you have created something similar, please do share it, as we would like to see what others have come up with and how we can improve ours. Conditional Formatting (which would allow highlighting of the first, second, and third most frequent casters of each spell, for instance) is off the table, though, due to the difficulty of getting it to work with these formulas in the open-source spreadsheet programs.
    adetia, Borias, arbre and 13 others like this.
  2. Metanis Bad Company

    This is awesome!
    Borias likes this.
  3. Pano Augur

    I would just like to add, as long as something is pasted in a column, it is going to work.

    Also, this can be adapted to any other class by just changing spell names in Detailed.
    Borias and gotwar like this.
  4. Borias New Member

    Tabby needs to step up his game!
    Pano likes this.
  5. Kialya Keeping your butt alive since 2002

    I agree - serious awesomeness here. A guild mate used it to compare our stuff - must say I love looking at my casting stats :)
  6. adetia Monkess Wonder, Ruler of All

    Clarisa, this tool is fantastic.
    Would it be possible, or could you give me a down & dirty method of adding more "people" slots to the tool? I could use 2-3 more and it wasnt overly obvious for me to see how to do that.

    Also for whatever reason, none of the pie charts appear to work for me - is there another setting i need to flip on beyond the calculation for reg expressions? Usually i'm pretty handy with this kind of thing but I must be missing something - I always use Excel so just did a fresh install of open office.
  7. Pano Augur

    It can also be done with Excel, except the part where I use RegEx (Ressurections). I could give you a somewhat working formula but you would have to tweak it because of the Resurrection line.
  8. Clarisa Augur

    Thanks to Adetia's suggestion and Pano's hard work, I have uploaded 2 new versions of the spreadsheet to the OneDrive folder (link in first post):

    Cleric Cast Parse Spreadsheet 1.4 (Blank Version) Extended
    Cleric Cast Parse Spreadsheet 1.5 for RoS (Blank Version) Extended

    The "Extended" means that these versions of the spreadsheet now support up to 10 clerics. Even if you don't have 10 clerics, as Pano brilliantly suggested, the extended versions might be useful to compare a single cleric's performance over 10 raid nights or 10 raid events (provided you select/combine the parses properly and slightly edit the name section as mentioned in the first post to tell them apart).

    The 1.5 for RoS version, as the name implies, is updated for RoS with the new spells named as they are as of Nov 13 on Beta. It assumes, however, that all clerics are using the new spells, so the older 90-95 single-target heals, all duration/delayed heals, and all group heals and buffs older than the RoS versions are dropped. So, if your clerics aren't using all the new spells, it might not be entirely useful for comparisons until they are. I am not sure if we will do a "mixed" version for RoS with some of the old heals and spells added in for clerics who don't level up as fast but since current EOK/TBM raid focuses max at 110, we are assuming that most clerics won't wait until they get new gear and Rk. III versions of the spells to use them, so leveling speed should be the only limiting factor to using the new spells unless the cleric has raid gear older than TBM.

    As for the disappearing pie charts issue mentioned above, I have run into this from time to time but usually I can get them to appear if I save the spreadsheet (checking to make sure there are no N/A or "error" values in the 'Detailed' tab/sheet first) then close and re-open it. I am not sure exactly why this happens though I blame it on my laptop not being able to generate them as I paste the data and doing the saving, closing, and re-opening routine gives it time to do it.

    As always, please feel free to offer any other suggestions or requests regarding the spreadsheets or let us know if you have any problems.
    kizant and Brohg like this.
  9. Clarisa Augur

    I have uploaded 2 new versions of the spreadsheet (thanks to Pano) to the OneDrive folder (link in first post):

    Cleric Cast Parse Spreadsheet 1.6 for RoS (7 Cleric Version).ods
    Cleric Cast Parse Spreadsheet 1.6 for RoS (10 Cleric Version).ods

    Changes include adding Merciful Shield (the new group and raid breastplate click effect for Ring of Scale) to the Utility AA section and correcting a bug with the previous version that caused clerics to be given credit for Issuance of Mercy and Vicarum's Retort spells they did not cast. The naming format for the files was also changed (10 Cleric Version instead of 'Extended').

    I also want to mention a general limitation of the spreadsheet concerning a single cleric using different ranks of the same spell on the same parse, which happens sometimes if they begin the raid with say Merciful Remedy Rk. II and upgrade to Merciful Remedy Rk. III during the raid. In such cases, the spreadsheet will only record casts of the first version of the spell cast (in this example, Merciful Remedy Rk. II) and will ignore casts of the upgraded version. It's a problem that could be fixed but would require a lot of work, and as it will only be a problem for a single raid (because the cleric won't go back to using Rk. II after upgrading) we decided to just leave it as-is. It is also possible to edit the parses before pasting them into the spreadsheet to prevent the issue if you want to avoid it (delete the line containing Merciful Remedy Rk. II and add those casts to the count for Merciful Remedy Rk. III).
  10. Rolaque Ancient

    Here's a suggestion (sorry if I missed it in your text, but it wasn't shown in the pic of GamParse you posted).

    In the GamParse comparison window (which lists all of the casts done by a specific player) when you click on the "Casts" column heading, it automatically lists all their casts in numerical order. That ordering is very useful, since it shows what is being cast most often (and puts all of the single cast items at the bottom). Plus you can then paste that version into your spreadsheet.
  11. Clarisa Augur


    That's actually pretty useful. Thanks! :) Though we have tried to include most useful casts in the spreadsheet there are a couple that get left out so having a more organized raw parse is pretty important, too.
  12. Raccoo Augur

    I've been using this, and I think I found an error. Focused CR might be showing as CRs. Had 1 cleric that used 3 FCR and 1 CR, and the output showed him using 3 CR and 3 FCR. Also any chance you can add the Apothic clickies? Thank you!
  13. Clarisa Augur


    I uploaded Cleric Cast Parse Spreadsheet 1.7 for RoS (7 Cleric Version), which includes Focused Apothic Balm (the new heal clicky), to the drive linked in the OP. It's only the 7 Cleric Version for now, though. I will upload the 10 Cleric Version when it is finished.

    I will let Pano know about the issue with Focused CR and CR counts also, so hopefully he can help get that fixed.
  14. Clarisa Augur

    Cleric Cast Parse Spreadsheet 1.8v3 for RoS (7 Cleric Version) uploaded. This should fix the problem with the FCR and CR casts being counted incorrectly. Thanks Pano for the quick fix!
  15. Raccoo Augur

    Thank you, Clarisa and Pano!
  16. Clarisa Augur

    Cleric Cast Parse Spreadsheet 1.8v4 for RoS (10 Cleric Version) uploaded, for those who need the 10 cleric version.
  17. Clarisa Augur

    Uploaded:

    Cleric Cast Parse Spreadsheet 1.9.1 for RoS (7 Cleric Version)
    Cleric Cast Parse Spreadsheet 1.9.1 for RoS (10 Cleric Version)

    Pano updated the spreadsheets to include Exquisite Benediction, Contraventions, and most cleric direct damage spells (like Sanction and Chromaburst). The damage spells/abilities have also been integrated into the summary, bar chart, and pie charts under the category, "Nukes", as shown in the sample pie chart below:

    [IMG]
  18. Pano Augur

    If anyone needs anything else added, let me know.
  19. Clarisa Augur

    Uploaded:

    Blank Cleric Cast Parse Spreadsheet 1.9.8 for TBL (7 Cleric Version).ods
    Sample Cleric Cast Parse Spreadsheet 1.9.8 for TBL (7 Cleric Version).ods (this version is the same as the blank version but with the data used for the sample images below)

    Pano updated the spreadsheets for TBL, which means that some older spells have been exchanged for their newer versions. There is a "Promised Remedy Cap" in the "Detailed" sheet as well (circled below):

    [IMG]

    The default value is 0, which means it is ignored. If you input any other value in the box, it will "cap" Promised Remedy casts to the chosen value when calculating the total for the category. Why would it need to be capped? Though our guild in particular encourages Promised Remedy usage in raids (more for the reactive-to-damage instant Promised Interposition heal than the delayed Promised Remedy heal itself), other guilds might not or their clerics might not be using it equally. It may be that only 2 of your 5 clerics use Promised Remedies in raids and their Utility counts may end up being much higher than the others due just to usage of that spell. In that case, you might want to cap casts to the average amount cast (or to the amount that you consider ideal usage) so the results aren't impacted by it as much.

    Also new is a bar chart in the "Glorious Table" tab which shows the results of each cleric's pie chart (which breaks down their casting by percentage in each of the categories) in a single chart:

    [IMG]

    The colors and the order of the categories are deliberate and meant to draw attention to the Utility Spells/Buffs and AA Casting categories, which our guild tends to focus on more for cleric applicants. The individual pie charts are still available in the "Pie Charts" sheet.
    Mediik, Gundolin and gnomeboss like this.
  20. Nylrem Augur

    Great tool!

    Very glad added the contraventions!

    Experienced cleric, on farm events, should be casting more contraventions than group heals by fair amount. Burn AA, ITC + 3x contraventions, forceful rejuv, 3x contra is arguably (very arguably, situation dependent) the best healing a cleric can do, not to mention the DPS factor.

    Does this happen to take into account twincasted nukes or contraventions for total count? Guessing would be fairly problematic to do so.