Keeping Track of Your Dragons - Spreadsheet Discussion/Tips Thread

[DeeDe99]

Introduction/Background

This thread has evolved out of discussions in response to questions about lists of dragons.

There will be specific topic posts on this thread as it develops and I will put an index here as the thread grows.

Index:

My KISS Principle Spreadsheet
Habitat Tracking
Arena Battles Tracking
Battle Colour/Type Cross Matching
Intro to Pivoting Your Data

Before we start discussing our own techniques and sharing our various ideas, I want to do a little recap, an Introduction/Background post.

There are already spreadsheets and lists maintained by other users which can be found in the Game Guide under Dragon Story Lists - In Progress. It looks like this when opened in Google Docs.



The work that has gone into this is amazing and the player who did all the hard work is given as 1crazycajun. It is still being updated and includes breeding results collection (from all players who want to contribute data) and statistics - very comprehensive.

There is also the Dragon Story Inventory, Chart and Checklists if you have missed it! Thanks to Sehana for those.

For many of you already using or wanting spreadsheets, this may be a great starting or reference point.

I had found these before I started my spreadsheet, but found them a little more complicated than I personally wanted, although they are brilliant pieces of work.

On the thread that hatched this thread, the Album (iOS only at this stage) and various other ways of looking at lists of your dragons has already been discussed and suggested.

A quick recap is as follows.

The Album

At this stage only available to iOS users, greyed out in Android. Lists all currently available dragons plus expired/limited dragons that you have acquired. Very limited information and is not sorted in any useful order. Does not include rarity or evolution/breeding time. The stars under the dragon image indicate the evolution stages completed for that particular dragon; if you have more than one of any one dragon, it is the more senior dragon's status that is reflected here.



Dragons you do not have are greyed out.



The information available is as shown:



Arena Selection List

Dragons that are otherwise occupied are covered over with that information. Ideal for the Arena, of course, but not for listing your dragons. Not available during Arena sleep time or to those under level 30.



Evolution Flower List

Only lists those not yet Epic and if they need more food, covered over with "Feed More" so the details can't be seen.

Trading Portal List

Excludes dragons that can't be traded. Be careful not to accidentally trade something! There IS a confirmation screen, so don't worry too much.

Market Lists

Excludes dragons you may own that are not currently available or cannot be purchased. Dragons you already own that are available in the market are listed at the bottom of the Market list. Can be useful as a quick reference guide.

[DeeDe99]

For those who may not be in the know, the first question we should answer is, what is a spreadsheet?

A spreadsheet is comprised of columns and rows, the columns are usually labelled A, B, C etc while the rows are numbered 1, 2, 3.

The intersection of each column and row is called a cell. Within the cell can be entered data: alpha characters or numbers or a vast array of formulas. If you want to know more, Google and Youtube are great learning resources. The rest of this thread assumes basic spreadsheet knowledge!

I've kept my own tracking spreadsheet very simple. I have future plans for linking various spreadsheets such as specific dragons to habitat location etc, but just haven't got around to it.

The main questions I initially wanted to be able to answer quickly were:

What dragons have I got?
How did I get them?
What level are they?
What rarity are they?
What types/colours are they?
What is their evolution time?
How much revenue are the dragons earning an hour and a day? (Need this for budgeting purposes re food and expansions outlay)
Do I have spare habitat slots for which types/colours? (Those eggs hatch, you see, and need a bed)

I wanted something compact and quick, where I could filter the list by selection criteria (i.e. find all my level 3 dragons or find all the purple primary types).

There are no pretty pictures in my spreadsheet, it is a basic working tool only.



I can use any of the filters at the top (Rarity circled in red in this example) to find specific groups of dragons, or an individual dragon.



At the bottom I have a total of the Earning column so I know how much coin I can afford to spend on food before I start going backwards! I reconcile the total dragons to the total on the profile page of the game, remembering that number on the profile page includes Arctic dragons and excludes anything in the Stable.

I have a pivot table built on this to tell me how many of each level I have. The two blank at the bottom are the Arctic Isles dragons and I leave it that way deliberately. While preparing this post, I realised changing my notation in the main spreadsheet from "Epic 10" to "10 Epic" made this pivot table much more readable, hence the difference between this screen shot and the one above!



I have similar pivots for Rarity and Dragon, so I can quickly see what % of each rarity and how many duplicates of any dragon I have.

Under Parent I enter source indicators when I did not breed the dragon and my Legend is as follows.



I have spreadsheets for Habitats and for Arena data, but that's for another day, another post. Enough for today.

[DeeDe99]

Habitats: do I have a spare bed for a new dragon? A new red dragon? Green dragon?

I got tired of having to click on every habitat to see, so I set up a simple habitat list with conditional formating of cells to show me instantly where I have free beds.



I won't go into much detail of the list, I think the columns are self-explanatory!

To conditionally format, select the top cell in the Occupancy data column. From the Home Menu, select Conditional Formatting



Select Highlight Cells Rules, then select Less Than



From the range button, select the corresponding Capacity cell. Set your preferred format. Click OK.

Using the Format Painter tool (the little paintbrush), copy this format down the column. Hey, presto, your vacancies are highlighted automatically.

Of course all this means updating this spreadsheet when you hatch and place a new dragon, but the number of times it has saved me from leaving an egg sitting on a nest for 24 hours waiting for a habitat to build, it has been worth it!

What I want to work on now is giving each habitat a unique identifier so I can link each dragon on the dragon list to a habitat, using a selection list, but I've been moving my dragons around too much to bother at the moment - I'd spend more time updating my spreadsheets than breeding dragons! But I can see as my numbers grow and the dragon locations settle down, I'll find such an enhancement useful.

Edit: Due to inspiration from Roguebee, I have now linked each dragon to a habitat. Details are in post #20. I no longer need to update this spreadsheet at all (unless I add a new habitat, of course), it automatically updates.

Mapping the habitats to a specific island plot is a little further away for me. I just haven't had the need, yet.

[DeeDe99]

When I first got to level 30, the Arena was new and exciting! I was so totally ignorant about the whole battling thing. Then I got to my first tournament. What a struggle that was! Many thanks to all those who encouraged me through that tournament! It was the Warrior Prince tournament and looking back now my record keeping was atrocious!

By the next tournament I had improved. One spreadsheet, Tournament battles on the left-hand side (very neatly recorded) and non-tournament battles on the right hand side of the spreadsheet. But I was REALLY bad at recording non-tournament battles. Look at this example: a couple I've forgotten who I actually used to win, no levels recorded a lot of the time, just really useless!



After the Black Ice Tournament I decided to get a bit more serious. Several of us were experimenting with magic in the arena, and there is often much discussion on tournament threads about particular dragons being great fighters on one island yet not so great on other islands. On top of that, I was (still am) developing my own colour cross-matching theories and I needed better reference data! So I revamped and may well do so again. Yes, of course there is a pivot table involved, did you really have any doubt?

I opened another spreadsheet and took all my well recorded tournament data and pasted it all into the one list. I then looked at my very badly recorded non-tournament data and copied what was useful (i.e. where I had actually recorded properly) and left the rest behind. Over time that missing data will matter less and less as new data is collected.

I then sorted it by opponent and added filters at the top, so it looks like this (I've scrolled down the list a bit here).



This way I can quickly see what I've used against what and been successful in the past, especially useful during tournaments. Most of the time recording doesn't take much effort - just copy/paste (or insert copied cells) an existing record for that opponent and update the data that is different: my dragon or the levels or the strike.

The Pivot Table? It just tells me who has won the most. Oh, that's out of 340 recorded battles.



In reality, who has won the most is pretty useless information because that all depends on what opponent types I have faced. If I'd faced a run of dragons needing be fought with Rainbow hybrids, Nouveau could be at the top of the list instead of Warrior Prince. If I had different dragons, I might use different dragons, so these percentages would be different. However on my island if you win more you are likely to get fed more, so it does serve some purpose!

I don't record losses unless it is a tournament and then only on the actual tournament sheet. Once the tournament is finished I copy/past the records (excluding losses) to this main summary sheet and sort the list again. (Bragging warning) I don't lose often enough to make recording losses worthwhile (you can believe that or not as you see fit ).

My next (and last unless I am asked for something specific) post will be about recording colour/type combos. Weekend job that is, I think.

Good luck in the arena!

[DeeDe99]

Last one! Aren't you all relieved about THAT! This is a bit wordy as it requires explanation. It also really isn't about tracking your dragons, more like tracking your Arena opponents I suppose, so maybe a little off-topic.

Now this is messy. It is also a work in progress because when I started I wasn't sure how I wanted the final product to be. SimDiva and I have talked about using a database application, but neither of us have had the fortitude and with the Tales starting I think this will continue to be a "one day" sort of project. What I have achieved to date suits my purpose and really, once it is done, it is done.

For those who haven't already figured this out, when it comes to the Battle Arena I don't use the unmentionable tool. I found it way too cumbersome and I don't have one third of the dragons etc etc etc. I also had different ideas about how to pick winners.

I forget which tournament it was now, but I started to depend on the Colour Chart. The problem was, it was hard to work out what to use just by looking at the chart, especially with tri-colours. I'd pick a fighter, lose and then realise I had a "bad" colour I hadn't noticed in the list. I needed something clearer.

So I started creating little groupings in a spreadsheet and marking off the good colours that appeared in the list of bad colours as shown below.



The Caesar example shows clearly how few colours/types are actually left to use! Now, I don't know, but I work on the basis we gain points for good colours and lose points for bad colours so my aim is to take a fighter into the arena who has only good OR good + unmentioned colours if at all possible. Unmentioned on the basis that might not earn a point, but it won't lose me one either. Sure, I compromise if I don't have the perfect combination! But at least I know what I'm aiming for. I'll try to compensate with level or rarity if my colours aren't as good as I'd like.

I don't know if types get extra points for being mentioned three times! But it is a possibility, is it not? Champions, being strong against virtually everything are mentioned multiple times, but often so is Rainbow or Gemstone.

As I come up against dragons with the same type combinations, I add their name to the cross-match BUT when I choose a fighter I pay careful attention to whether the opponent is a (for example) white/blue or a blue/white. My results indicate that this may make a difference. I like to have my dragon's first-named type be against the opponent's first-named type. It may mean absolutely nothing!



Rarity and level I can do in my head on the fly although I usually only worry about level as a "tie-breaker" if rarity and colour/type are tight.

My problem is this spreadsheet is cumbersome - or has been cumbersome to develop. A spreadsheet is not the right tool but it has been OK for prototyping. I now have 76 different colour combinations cross-matched and while I get a new one every now and then, it is not too often.

I have the "Find" tool on the quick access toolbar, so it is just a click and enter the opponent's name to jump to the right cross-match.

I have also made some changes to my own copy of the Colour Chart, which I have detailed previously on the Master Battle Thread in this post.

That's it. I'm done! Thank you for reading, I hope some of this may have been of use to some!

[MaribouMay]

Great content! Thank you for taking this on.

I appreciate how organized you are and how easy it is to understand. I would love to learn about pivot tables. Maybe you could show us how they can manipulate a spreadsheet?

[DeeDe99]

Great content! Thank you for taking this on.

I appreciate how organized you are and how easy it is to understand. I would love to learn about pivot tables. Maybe you could show us how they can manipulate a spreadsheet?

Thank you May. Certainly I can give a Pivot Table Intro which should get most people started. I'm using Excel, so people using a different spreadsheet application may have to search out the appropriate function in their application, but it shouldn't be too difficult to locate.

The great thing about Pivot Tables is once they are set up, that's it! You only need to update the raw data in the main list and the Pivots will update themselves (well, OK you do have to hit "Refresh" if you don't set them to auto-update).

Select the Insert menu, then Pivot Table



The following dialogue will pop up:



Click on the range button and highlight the range containing your data. If you select the range before selecting Insert > Pivot Table, this box will already be populated, as in my screen shot. I have marked an "Insert Rows above this line" row in my main data so I never have to change the range the Pivot Table works from: by inserting rather than adding at the end, the range grows dynamically.

Select if you want your pivot table on the same spreadsheet as your data, or a new spreadsheet. I usually select a new spreadsheet for the first one and then put all my pivot tables there. That way I don't have to move the pivot tables later if I make changes to my base data layout, such as adding additional columns.

Click OK and you will then be shown the Pivot Table Field designer. I have pre-completed this one. Simply click and drag the fields you want into the areas of the table as shown.



In the above, I am counting the Hybrid column as it always has a value in it. It doesn't matter what the value is, as long as the cell isn't blank, it will be counted.

Then I use the same column to display the value as a percentage of the total, so I drag that field down to the Values area twice. On the second one, click the little arrow beside the "Count of" and a sub-menu will display, select "Value Field Settings" to open the dialogue.



To be continued in next reply, as there is a limit of 5 pictures per post.

[DeeDe99]

Part two Pivot table

Click on the "Show values as" tab then select "% of Grand Total" from the dropdown. You can also change the name on this dialogue, so I have called this pivot table column "Percentage".



You can also add a filter to the Pivot Table, to look at a sub-set of your data, for example, Gemstone dragons only.

On the Pivot Table Fields dialogue, I have dragged "Type 1" into the Filter area.



I can now select from the Filter:



To see my Gemstones by Rarity.



Hopefully that is enough to get people started.

To update/refresh your pivot tables after you have made changes in your base data, you can either:

Right click in a pivot table and select "Refresh"

Click in a Pivot Table, select Analyse from the PivotTable Tools menu, then Refresh. You have the option to refresh all, or just the one you have active.

Have fun!

[roguebee]

I'm going to stay quiet and read. This discussion has already far surpassed what I've done with mine.

[MaribouMay]

I'm going to stay quiet and read. This discussion has already far surpassed what I've done with mine.

To the contrary, I think you should share yours. I am guessing that many will not want a complicated spreadsheet and will benefit from seeing yours. I will work on a post to show mine; I'm sure it won't be as fancy as DeeDe99's.