City Optimizer

[seabasstion]

https://www.dropbox.com/s/k0npuud4zo7oqgv/CitySetup7.xlsm?dl=0

 

for those that weren't aware I had a city optimizer online awhile ago that would attempt to give you your ideal city build. It was largely solid with exception of a few minor things (like hospitals overpowering recycling centers in an illogical way). I got hit with many requests / clarifications once it got out and it was a bit of a hassle so i took it down. this led to even more requests for builds in a way that it didn't really support it.

 

i wasn't happy how i left it (and i still wanted a good working one for myself) so i built this. i finally fixed the hospital bug (it wasn't a bug it was an issue with the logic - but i digress) and added a lot better power facility control. it will require windows excel. im not sure how far back the compatibility goes so you will have to basically try it and see if you want to use it.

 

i've tried to design it to be as simple as possible so i dont have to field endless requests.

 

here is how it works.

 

step 1) enable all macros

step 2) enter your city ID (CITY - NOT NATION) in the designated box

step 3) hit the import from pnw button (scraping resource prices can take longer than it should - did alex put in flood control?)

   - this will import all the city improvements, data, and project information

step 4) it will ask you the number of military improvements you want to use. it defaults to what you currently have

step 5) it will ask you what land you want to calculate against. it defaults to what you currently have

step 6) wait for it run through all of the build levels. it will go from 100 to 4700 infrastructure.

   - i did it this way because i was getting tons of requests on, whats the optimal build at 2k, 2100, 2200 etc. this way you can have a nice big table to latch onto

step 7) it will ask you if you want to calculate your earnings for all of the cities associated with the nation belonging to this city id. it only takes a few seconds. i think it's worth doing

 

 

how to interpret it

1) in row 3 it will calculate your total earnings for your current imported build at O3. it breaks it down into cash and and resource value next to it

2) it will find your 'ideal' build in row 53. it will highlight the changes you need to make to get to ideal

   -range d39:e43 has the cost analysis to make these changes

3) it will find your 'ideal' build in row 54 with an additional 100 infrastructure from your imported city

4) it will find your 'ideal' build in row 55 with an additional 200 infrastructure from your imported city

   -range d46:e55 will have the cost analysis for the +100 infra build, +200 infra build, and your next city that is built exactly like the one you imported (with ROI). use this for 'what should i build next' type questions if you are purely econ focused

 

as an example i took to first city from LPS and ran it. this is what it came up with

 

this city is currently making a total of 1.65M a day. however if the city were to be adjusted where 1 nuclear power plant was destroyed, 6 wind mills built, and 1 hospital destroyed it would earn an addition 9,500 per day. it would cost -586k (meaning he gets a refund)  which pays for itself in -60 days (instantly)

 

to build his 26th city exactly like the one i imported, it would cost 660M and take 116days to pay for itself.

to build another 100 infrastructure with ideal improvements it would cost 10.4M and take 4900 days to pay for itself. note though that if he were to buy 200 infrastructure the ideal improvements would only take 440 days. this is due to hospitals being ineffective at this level as they produce pollution in themselves. the high population can really make some big swings.

 

if this 9500 increase was realized over his 25 cities he would increase his daily gross earnings from around 35.86M to 36.10M. you better get on that!

 

and to answer it before it is asked - i am not taking this to google drive

[Ishmael]

Looks cool! Unfortunately I'm getting a "Runtime error: 5" response whenever I try to run the macro after changing the ID to my own nation's.

[seabasstion]

Did you follow step 2 correctly?

[Khorne]

Looks cool! Unfortunately I'm getting a "Runtime error: 5" response whenever I try to run the macro after changing the ID to my own nation's.

 

Oh and, Seabass you're the real senpai here. <3

[ComradeMilton]

Any reason not to just do wartime/peacetime templates you can switch in and out of?

[Shakyr]

Interesting. I'll have to have a look, this kinda thing interests me

 

and to answer it before it is asked - i am not taking this to google drive

Damn, I don't actually have a copy of Excel installed. I've got an old disc around somewhere for 2010, I think.

 

EDIT: On an aside, the Google Drive scripting is actually pretty damn powerful, I have a spreadsheet there setup, that works out a nation's income, using the API.

Edited April 7, 2017 by Shakyr

[Khorne]

Interesting. I'll have to have a look, this kinda thing interests me

 

Damn, I don't actually have a copy of Excel installed. I've got an old disc around somewhere for 2010, I think.

 

EDIT: On an aside, the Google Drive scripting is actually pretty damn powerful, I have a spreadsheet there setup, that works out a nation's income, using the API.

 

Have a few that do the same. I like my editable version better, though, let's me understand how much money I'd be making (resources included, excluded in other versions) if I modified the setup. The API version I haven't bothered to make modifiable.

[seabasstion]

Any reason not to just do wartime/peacetime templates you can switch in and out of?

 

because you'll still be faced with the same question of what is the most economical setup with your wartime setup and your peacetime setup depending on your infra, land, bonus, and number of military slots used.

 

EDIT: On an aside, the Google Drive scripting is actually pretty damn powerful, I have a spreadsheet there setup, that works out a nation's income, using the API.

 

i agree it is really good - but where i do most of my developing google drive is blocked unfortunately

[Shakyr]

Have a few that do the same. I like my editable version better, though, let's me understand how much money I'd be making (resources included, excluded in other versions) if I modified the setup. The API version I haven't bothered to make modifiable.

This one is editable, I just prefill using the API

 

i agree it is really good - but where i do most of my developing google drive is blocked unfortunately

Fair enough, security and all I'm guessing I do a fair amount of my tinkering at home, so the only sites blocked are the ones I've set!

[Ayayay]

My version of excel froze up and crashed when I clicked inport from pw

[ComradeMilton]

because you'll still be faced with the same question of what is the most economical setup with your wartime setup and your peacetime setup depending on your infra, land, bonus, and number of military slots used.

 

i agree it is really good - but where i do most of my developing google drive is blocked unfortunately

I guess if you get really anal about it. I don't. I just make money in trading and theoretically I'm sure I have an income of some sort.

[seabasstion]

I guess if you get really anal about it. I don't. I just make money in trading and theoretically I'm sure I have an income of some sort.

 

in all fairness it is called the 'optimizer' and not 'an income of some sorter'

[Kid Winchell]

in all fairness it is called the 'optimizer' and not 'an income of some sorter'

As a point of comparison, this spreadsheet is, in fact, an "income of some sort"-er.

[seabasstion]

whenever i run '10629' it returns the following error

The coordinates or dimensions of the range are invalid.

which is a bummer since im really interested in seeing how this produces results

for (var i = 0; i < max; i += 1) {
    var chosen = choices[Math.random() * choices.length | 0];
    build[chosen] += 1;

i think youre on another level of coding skill than myself so could you explain to me the logic behind this is? it's not coming together in my mind how this would create an optimal suggestion

[Kid Winchell]

whenever i run '10629' it returns the following error

The coordinates or dimensions of the range are invalid.

which is a bummer since im really interested in seeing how this produces results

for (var i = 0; i < max; i += 1) {
    var chosen = choices[Math.random() * choices.length | 0];
    build[chosen] += 1;

i think youre on another level of coding skill than myself so could you explain to me the logic behind this is? it's not coming together in my mind how this would create an optimal suggestion

a) Fixed the issue. Make another copy or keep the old version and just make sure there's some dummy text anywhere below row 12.

2) It's not an optimizer. It's an "income of some sort"-er.

[PackAnimal]

@seabasstion is there an updated version of this with the parameters changed to suit the overhaul in improvements and resource production?

[seabasstion]

sorry - there is not

[Neymar]

Did you follow the correct steps?