Monitor your City

Recent City Map Workups (June 18th)A separate spreadsheet tab is available for each of the following cities, and we're setting up a Saved Filter View, for each of them, in the main data base in the ActiveDays tab. There are a LOT of tabs, but there's ALSO a nice Drop Down List in the lower left corner of the page. You DO NOT have to flick through all of the horizontial tabs.

Our Unique City Identifer is: TownHallLevel, ServerWorld, PlayerName. We're also carrying Chapter Names in the Group Outline Row Header for each city, but as data rather than as a key identier.

- 49 us4 ajqtrz
- 49 us2 Player9999
- 37 en1 Lelanya
- 35 zz1 Anonymous3
- 35 en2 Lelanya
- 34 us4 Katwick
- 34 en1 Lela Swell
- 33 us2 Lelanya
- 33 en3 Lelanya
- 28 us8 Lelanya
- 27 zz1 Lelanya
- 27 us7 ajqtrz
- 25 us2 Lela Swell
- 23 zz1 ajqtrz
- 19 us7 Lela Swell
- 17 zz1 Katwijk
- 11 us1 Lelanya
- 09 zz1 Katwick
- 08 us7 Katwick
- 07 us4 Hudson Preston

What we need at this point is permission to look at, and post, unfamiliar cities so that we can work out most of the unforeseen glitches. Once we're satisfied with the City Map User Experience, we'll start promoting the ActiveDays module as well, and then you'll be able to Turn Cranks, Set Goals, and do a Monte Carlo Sensitivity Analysis for your various Commodities, as well as admiring what you're already accomplished.
If you're willing to let other folks look over your shoulder, do please send us a Direct Message in Discord, click on Katwijk's name, or simply post your permission in the Discord City Map thread. Bragging Rights? Anybody??Executive Summary (June 7th)Monitor Your City - Katwick 2025F04
At its heart, "Monitor Your City" is simply a carefully organized method for taking notes about the performance of your Elvenar City, with a few local calculations that will pay you back for the time that you're investing to Monitor Your City.

A fundamental objective for "Monitoring Your City" is facilitating the on-going observation of OTHER SIMILAR CITIES that their Owners are willing to share. As there are more than 500 COLUMNS for the Commodities that can be Produced (+) and Consumed (-), and a similar number of Cities, each with multiple ROWS for the buildings that are being tracked, we've provided comprehensive Column and Row OUTLINE groups that effectively allow users to supress the Settlement Commodities, as an example, that are not relevant to their current chapter, and the cities for all but a few chapters. Grouping gets pretty important when a spreadsheet has more than a million cells.

While using Groups is VERY convenient AND USEFUL, expanding and collapsing Groups is regarded as editing, with the uncomfortable consequence that anybody with a link to the spreadsheet will be able edit everthing. This is not as silly as it may seem, as Google Sheets automatically backs up the data whenever "enough" changes have occured, so for ordinary mistakes the Admins can easily revert to an earlier version of the spreadsheet and, if malicious users force us to forego the convenience of Groups, we'll simply present a "typical" page that's View Only, and require prior permission for Editing, thereby acess to the Outline Groups. In the "Wonky Spreadsheet Stuff" section we'll do a deeper dive into the tradeoffs between Convenience and Disaster.

The Admins will also be creating Saved Filter Views, so that only a PORTION of the spreadsheet will be downloaded, when you use "your" preset Saved Filter View as your desktop icon. The saved filter views and the outline structures are isomorphic, so if the spreadsheet is wildly successful, and becomes so large that it bogs down, sharding the spreadsheet into three separate spreadsheets for the Standard, Sentient, and Ascended chapters will be a no-brainer.
The game itself is well designed, with Quests and a Research Tree that do a great job of guiding you along a well-traveled path. But what if you prefer Scouting? "Monitor Your City" has FIVE major components:

1) Once we have obtained the Owner's permission, we will capture their CityMap JSON code, load the consequent CSV file into a dedicated tab, and add a Gemini Scatter Plot that provides a DYNAMIC hover with a popup for Type, X, Y. The chart is also filter sensitive, and will only include the rows that survive your filter for the various types of buildings. Be aware that you'll need to refresh the page, up by the URL, and thereby rebuild the chart to reflect your recent changes, because the chart is BUFFERED by the data in the Orange section, which breaks the immediate update chain.
1.1) If the Owner wishes to also use the ActiveDays tools in Monitor Your City, we'll create a related City Section in the ActiveDays tab.
1.2) Note that we're using the basecode names for the various City Map Entities, because the localized names are buried in the static Popup tooltips. On the US servers, for example, "Poos_Place" is rendered as "Smallest Room."

2) The relationship between the various ID fields is subtle:
2.1) id indicates building sequence. It indicates the building sequence for your City, and DOES NOT change as the building is Moved, Upgraded, nor Evolved. If you add 6 Residences, for example, they will probably have sequential ids. Then, a few months later when you realize that you need a 7th Residence, the ids will have a big jump, even though the buildings are currently all at the same level. Note that Streets are never upgraded, they're simply replaced.
2.2) Type indicates the underlying code module for a building, and therefore it's capabilities. Some of the commonalities might seem strange, until you peel away the decorative fluff and focus on what the building actually DOES.
2.2.1) Culture just sits there. It's a one-trick pony. It can be buffed by Visitors, whereupon it provides MORE Culture for only 8 hours, unless one of the AWs has extended the duration of the buffs. But that's it. There are NO choices once the building is in place.
2.2.2) Culture_Residential PRODUCES something, that's often selectable, on a daily basis, and then STOPS until the Owner collects the produced items.
2.2.3) Residential does all of those things, AND supports early withdrawals, which resets the countdown timer.
2.2.4) Military adds the wrinkle of Traning Squad size, allows the sequential production of up to FIVE different military types, and permits the early withdrawal of TRAINED squads, but doesn't automatically reset
2.2.4) Settlement buildings typically produce several different items, but you can't stack the production sequence, nor is early withdrawal permitted.
2.3) cityentity_id concatinates a descriptive id, the Type, and the Level. It's all about Artwork and Production rates, and precisely defines the current version of a particular building, of which you MAY have several.
2.4) player_id is a different kettle of fish, that gets particularly interesting when you're using other cities as your building catalog. If you've found a user whose playing style is similar to your own preferences, you can save yourself a lot of grief by starting out with THEIR production rates until you understand the building well enough to start tuning it to optimize your city. Note that the source user MAY be on an entirely different server. We're all fish in the same pond.3) As some buildings can do several DIFFERENT things, we've introduced the concept of SUB-buildings, and have overloaded the Sub-Building concept to include Research costs, Upgrade costs, and Quest rewards.
3.0) Blank or Zero: For simple building you can leave the sub-building field blank. If you are using ANY of the other sub-building fields, then use a zero to float the "real" building to the top of the local sort.
3.1) One identifies Research that you prefer to track, rather than lumping it in with the other Benchmarks
3.2) Two identifies Upgrade costs
3.3) Three identifes the rewards that will accrue once a building has been built or upgraded to a particular level.
3.a) Marble, for example
3.b) Moonstone, for example
3.c) Primordial Minerals, for example

4) Concatination of the cityentity-id and the sub-group will be use to index a VLookUp table that contains NickNames for the various sub-building, which will usually be product related, Just as an example, the sub-building codes for Goods Manufactoring will be:
4.a) G01:G09 for Standard Goods
4.b) G11:G19 for Sentient Goods
4.c) G21:G29 for Ascended Goods
4.d) Note that a given city SHOULD only have 9 of the 27 possible offering, based on their Relic Buffs. It's not wise to ignore a +800% buff, when it's pretty easy to Trade for your unbuffed goods. The intracacies of Trading, Traveling Merchants, and Wholesale Purchases is every bit as complicated as the composition of your military strike forces.

5) Benchmarks:

The top portion of each City Section allows the Owner to manually benchmark the Running Totals that the game provides, with Sparklines on the Capped Commodities. After a few weeks you'll find yourself using update, Tab Tab, update, Tab Tab, update, without even looking at the keyboard nor the spreadsheet, while thinking mostly about your Goals, and the adjustments that you'll need to make in order to reach those goals in a reasonable number of Days. 6) Owners can set a GOAL for each of their Commodities

6.1) DaysRemaining is calulated as (Goal - TheMost RecentBenchmark) / DailyYield
6.2) DailyYield is a Summation of the Commodity Consumption(-) and Production(+) in each COMMODITY COLUMN for each building ROW that you are tracking, in the bottom portion of your City Section.
6.3) A Monte Carlo Sensitivity Analysis is available that allows you to vary one input (in the free version), typically How Many Building Cycles, over a range without actually changing anything in the spreadsheet, while concurrently monitoring four different outputs (with some nice charts), typically a spread for how many days it might take you to reach your goals. In a well balanced city, the Days to Goal will all be similar.

7) The bottom portion of each City Section replicates the main buildings from the City Map tab, and typically includes all of the buildings that allow the owner to select various options, day by day. The streets, the various static event buildings, rewards (and losses) from Events, Research, Quests, Spire, Tournament, and Upgrades are best swept into the Benchmarks.

8) We've also provided a timestamp field, for indicating when a building was first placed into service or upgraded, which is closely related to the id field, and a few other convenience fields such as PICK ME, which highlights a row in BRIGHT CYAN to help you stay on the right row. PICK ME can also be used as an arbitrary filter key.Bells and Whistles (June 10th)Outline Groups, Filter Views, City Maps, Expiration Dates, and Conditional Formatting are used extensively, to help you find your way. Here's how we're using them.

Outline Groups:

Outline Groups are closely related to Hidden Columns and Rows. There are, as an example,
18 different Settlements, starting with Chapter 6, and the Column Outline Groups allow you to supress all but the one Settlement that you're currently working with. The numbers in the Yellow on Brown
Columns indicate how many NON-ZERO cells are included in the rows of that particular group.

A Saved Filter View does NOT preserve your Group Outline selections.

1) For ROWs this is not an issue because the Basic Filter, as we're using it, matches the TownhallLevel, ServerWorld, UserName triple that uniquely identifies each City Section. We're indiexing with Townhall levels, rather than Chapters, because Chapter is a rather vague concept, in game, whereas the Townhall is ALWAYS present as the building with the lowest id number. There are roughly two Townhall levels per chapter.

2) For COLUMNs you'll want to right click on one of the Column Group Bars and select Collapse All Column Groups. You can then simply click on a Column Group Bar or Icon to display the Column Groups that you want to view, as you need them. It's a very elegant approach.

The local Summations are in the MIDDLE of each City Section, between the Benchmarks and the Buildings. The Brown ROWs are just fencelines between the various cities. Filters:

Filters only work on ROWS, and they actually suppress data. You will only see the ROWS that survive your filter. In fact the Server only downloads the QUALIFYING rows. Filters are FULLY INTEGRATED
with Summations, and the other ranged functions, and using them effectively is an Art of its own.

If you're looking around, and trying to figure out what OTHER FOLKS have been doing, then Filters are a godsend, but on a day-to-day basis you'll seldom use Filters, EXCEPT for the Saved Filter View that you SHOULD be using as your desktop icon for "Montitor Your City."

When you're in a Filter View, you can also sort (just) the filtered data without disturbing other users. This makes Benchmark Timestamps particularly interesting, as we are providing Duration Sparklines (How long did it take to get to this Timestamp) when a NEW Timestamp has been entered. The Timestamps allow you to go nuts with looking at the pacing in OTHER cities, which is wonderful.

For EXPIRING Buildings we've repurposed the Sparkline field, and we're using it to show Expiration Dates.

The spreadsheet is set up such that inserting new ROWs BELOW an obsolete building, and then using Copy Paste to insert the NEW (group of) buildings, the best approach. You CAN copy paste an entire city, but you'll have to then manually reset the bottom of the City Summation Range.City Maps

The CityMap JSON code includes the X,Y coordinates for the top corner of each building in your city.
- (0,0) is the TOP CORNER of the Expansion area, up by the Rocks.
- Beginning at 0, X increments RIGHT and down
- Beginning at 0, Y increments LEFT and down. (Ignore the minus sign; we're just using it to flip the Y-Axis.)
- In a new city the Main Hall is located at (15,0)

The default City Planning Map at ElvenArchitects.com, is rotated 45 Degree anti-clockwise. If you use the BasicFilter in Row 3 to sort FIRST by Y, and then by X, the Planning map and the Gemini generated Scatter Plot will be isomorphic.

When you Import a CSV file it may mess up your column widths, but you can easily restore the formatting by copying any row from a similar tab, and using Edit > Paste Special > Column Width Only. We've also suppressed the state, and connected fields by making them only two pixels wide, as they are not useful for our purposes.

JSON throws blanks rather than zeros, but the sort order for blanks and zeros is NOT the same. As zero IS a legitimate index value, we've coerced the blanks by simply adding zero to ALL of the index numbers. In other circumstances, where zero just means that nobody is home, use an If statement to test for blank and throw a blank rather than a zero. This approach avoids long strings of zeros in areas that are otherwise empty, and it also speeds things up a bit by avoiding the related computation altogether. It's pretty mild spreadsheet magic, which we'll discuss in more detain in the Wonky Spreadsheet Stuff section.

The City Map Scatter Plot is DYNAMIC, once you've moved the focus to the chart, by clicking on it, hovering will get you a tooltip, for each building that indicates Type,X,Y. The chart is ALSO SENSITIVE to the BasicFilter in Row 3, just above the imported data, although you'll have to Reload the Page to rebuild the Scatter Plot. So there's a really handy way to locate your Smallest Rooms, Evolving Buildings, Expiring Buildings, and Buffable Pets. And obsolete streets for that matter.

In the Step-By-Step section we'll go into the How-Tos, but the Gemini Coding AI will go nuts with graphs if you prompt it correctly, including stuff like HOW MANY buildings (Hello? Streets??) have an identical cityentity-id.Conditional Formatting

If you click on the top left corner of the spreadsheet, and then select Format > Conditional Formatting. You'll get a sidebar that details ALL of the dynamic formatting that we're using.

Conditional Formats are STACKED, and the FIRST qualifying format is the ONLY one that influences a given cell.

Pick Me: If you check the PICK ME box, the entire row will display as Black on Cyan. This is mostly to help you stay on the row you're updating, but it also warns any Looky-Loos that you're updaing your data, or Copy/Pasting their data.

Supress Zeros: The Cells in the Brown Boundry Columns count the number of NON-ZERO row cells in the local section to their immediate left. If there ARE values, then the user needs to expand that section. If the Function returns a zero, we hide the zero by using Brown on Brown.

Brown Columns: Anything else in the Boundry Columns will be displayed Yellow on Brown.

Formulas: (Other) Formula cells are grey, and we're using the same "hide the zeros" approach. This may seem peculiar, but there are THREE good reasons.

1) We're not using Protected Cells, so the elaborate formulas in GRAY CELLS should be left alone.

2) It's often convenient to use a simple formula to input data. Coin Instants plus Coins could well be =Capacity*(50%*37 +25%*3 +10%*49 +5%*150) +Coins.

3) Benchmark data is entered directly into the Commodity Column, whereas Model data is calculated using the NUMBER of BUILDINGS times the DAILY YIELD that you're anticipating.

Consume / Produce Color Scale: ENTERED values are scaled by color, which makes it easy for Old Eyes to sort out (Minus) or (Plus).
* GREEN indicates GO FASTER at -20000 or worse (HEAVY CONSUMPTION)
* RED indicates GO SLOWER at +20000 or more (EXCESS PRODUCTION)
* YELLOW indicates smaller, MANUALLY ENTERED numbers.
* WHITE is a BLANK cell, with no value whatever, which will calculate as zero.

Static formatting has been used for the headers, and for the horizontial city divider rows.

We have NOT used Outline Boxes, nor any other sort of Prompt, to identify the input fields. A stack of colored cells is a pretty obvious indicator.Monitor Your City (Step by Step) June 10thIt's entirely permissable to make your OWN spreadsheet that replicates Monitor Your City, or to simply copy this speadsheet for your own private use.

So long as the Column structure remains consistent, we'll be able to use the IMPORTRANGE() function once you're comfortable with your data, so that other users will be able to admire your work.

In this section we'll assume that you're starting with a brand new Google Sheet, that's blank, and that you intend to use =IMPORTRANGE() to obtain the headers and fomulas from Monitor Your City. The point of this exercize is that you shouldn't trust something that you don't understand.

While Google Sheets WILL run on a mobile device, we've made no attempt to chop up the data so that it will fit into a smaller screen, although these Instructions, for example, are sized so that you can read them on a Mobile device held horizontal. We'll be needing the Browser Development Tools (Ctrl+Shift+i) in any case, to monitor the network traffic.

To be blunt, if you're sharing your information with other users, and thereby enhancing the the value of Monitor Your City, we'll be very responive to issues that might arise and to questions that you might have. If you're doing your own thing, then not so much.Setup

We'll walk through ALL of the steps that would be needed to start from scratch, but you can, of course, skip many of the steps if you're using the application in a prepared environment. Even if you don't do all of the steps yourself, you'll be more comfortable if you know HOW they were done.

1) We'll assume that you know how to get into Elvenar.com using both the desktop Google Chrome browser and the desktop Microsoft Edge browser. We've not tested the application on other Browsers, nor on any of the mobile platforms (which are too small in any case).

2) Create a free account at Jam.dev, as you will need access to their JSON to CSV conversion tool. (JavaScript Object Notation) to (Comma Separated Values), and they also have a very nice screenshot utility. There's a Jam Jar in the upper left corner of their help page, that's a bit too subtle for my taste, but it will get you to their Utilities page.

3) Create a Blank Google Sheet by following the steps at How to use Google Sheets - Computer - Google Docs Editors Help.

4) Pin Notepad, or your favorite Text Editor, to your Task Bar. You'll need it to save the CSV data from the converter.

If you have an ALT, you're golden. The cleanest way to get at the JSON code "entities" is to use the in-game Roster (it's under your In-Game Potrait) and Search for the City of interest. The Network Response will be MOSTLY the data that you want for that City Map.

If you're using the Google Chrome Developer Tools, they bail out if the JSON heirarchial structure gets more than 5 or 6 levels deep. So if you want to examine your OWN (large) city, or any of the other larger cities, you'll need access to Microsoft Edge. Edge hangs in there for all the cities that we've examined, but it doen't support a global search for strings that are buried in JSON files; you'll have to select each JSON file, and then use Find Ctrl+f.In-Game to In-Spreadsheet

1) Click on the In-Game Roster, it's under your potrait in the upper left corner, and search for a target city.

2) Use Ctrl+Shift+i, or the Browser Menu > More Tools > Developer Tools. Select the Network tab, and set the filter to json.

3) In-Game click on the roster widget and Visit their city.

4) The Chrome Browser supports Searching within JSON code, the Edge Brower does not, so you'll have to click on (each of) the JSON files.

4) Now click on the (only line of) JSON code, and observe that the displayed Pretty Print code iincludes "__class__": "CityMapEntityVO", also note that each of the objects includes the data that we're after.

5) Click on the Pretty Print and Find (Ctrl+f) CityMapEntity. Click on the arrows (or use Ctrl+g) to get to the FIRST hit and then backwards to the LAST hit, and observe the position of the slider bar at the top and bottom of the range

6) Click just BEFORE the square bracket that indicates the start of the entities array, and SWIPE to INCLUDE BOTH the Open Bracket and the Close Bracket for the array. Now press Ctrl+C to move your selection onto the Clipboard. You can now close the Browser.

7) Open the JSON to CSV converter tool and JAM.dev, Paste the JSON code, and then click COPY.

8) Open Notepad, click on New Tab, and name the tab by typing its name on the FIRST LINE OF THE TAB, using the convention "1,Chapter ServerWorld User City Map", and then hit Enter. Note the COMMA, and please use a two number Chapter, ie 07, for the begining Chapters.

9) On the SECOND line, use Ctrl+p to paste the CSV code, edit the first field in the Header Row (the 2nd row in the file, and then save the file. Later on, the 1 and the 2 will force those lines to the top of a sort on the first of the Header fields.

10) Now open your new Google Sheet (or your new blank tab), position the cursor in cell C4 (it's GREEN with a 1 in it). To be entirely safe, zap the existing Green Data, then elect File > Import > Append to Current Sheet (click on the List format if you need more detail about the files), and then select Replace the data in the current file and Download your CSV file (tab).

11) When you're getting ready to upgrade a Building, you can earn some Brownie Points by copying a few Rows from Cities that are just a bit ahead of you. And It will get even easier once the data has been trundled over to the ActiveDays sheet, as you may be quite happy with the Production Rates that have been indicated.Formatting a City Map Tab

1) Copy the Filename in in D4, "15 us4 Katwick City Map" as an example, and Rename the Spreadsheet Tab to match..
2) Timestamp Cell I4 by using Ctrl+Shift+Alt+; (semicolon)
3) The structure of the JSON code ends up with Headers in the first row of the CVS text file, but Notepad uses the first row to create a filename. We do not need a 100 identical file names, so we insert a file name in the first row, ahead of time, and subsequently Import the CSV text two row higher than the real data, so that the first data row is the same row for all of the files.

4) A BasicFilter is already defined in C3:L3, and the other two lists are derivitive, so they will reflect your Filter Selections.

7) Highlight Column K4:K and Insert > Checkbox
8) Highlight Column A4:K and select Format > Conditional Formatting, and define the formula option as =$K4, with a background color of Cyan

9) Supress the unneed Columns by using Edit > Paste Special > Column Width Only from any similar tab
9a) Manually: Resize the Columns by swiping them, Shift+>, and Edit > Resize Columns A-Z > Fit to Data
9b) Manually: Supress the unneeded data by resizing Columns F, &J to 2 pixels


Using Gemini

1) Select the Gemini (Artificial Intellegence) Icon in the upper right corner of the spreadsheet.
2) Prompt Gemini with "Scatter Plot of x vs y (y-axis flipped), Color by Type"

3) Play around with Gemini, it's really quite capable. One of the more interesting charts was a count of the various unique entities, especially the Streets and minor Event Buildings that aren't worth monitoring.

4) When you're done with the City Map tab, please use the BasicFilter to Sort by --Class__, id, Y, X (manually in reverse order) as indicated in Row 3. And perhaps set the filter to suppress the Streets and non-residential cultural items that aren't worth tracking individually. Such items are best swept into the Benchmarks.

5) Most of the remaining items will have options that the Owner can exploit to balance the production in their City.

6) Note also, that a similar workup is available for the buildings in the Summoning Tab, that you may have Teleported, or forgotten about. If you'll never need them, they should be Disenchanted.Charting

Nope. We're not going to do a deep dive into the gory details of how to create a chart.

Conceptually, a Scatter Plot is merely a Line Graph wherein the Dots are NOT connected. To set it up you need to correctly structure your data:
1) HEADERS in the first row, EXPLICITLY for each of the "Lines" in your graph
2) X-Axis data in the First Column
3) Y-Axis data for EACH of the Series

Once you've moved the focus to the Chart, by clicking anywhere in the body of the chart, HOVERING over a building will yield a TOOLTIP !!!! (I'm impressed!)

The Scatter Plot is SENSITIVE to the BasicFilter in $C$5:$M$5, although you'll need to press Reload this Page (up by the URL) to rebuild the Scatter Plot.

The array for the Chart is automatically derived from the downloaded data, by using an If() function that looks for Row Items that match the Header Items. The graph is in Columns A:B, and you can slide it around if you need to work on items that are a ways down the file.

For Convenience, we've provided a Type Count in T1:AK1, above each of the Headers. Those numbers indicate how many (additional) Rows your City will need in the ActiveDays tab.:Wonky Spreadsheet Stuff