GearCity

GearCity

View Stats:
JC Denton Jun 29, 2021 @ 10:14pm
New Utility - Executive Secretary
I've been inspired to start a new utility, which essentially creates additional reports based on data in the SQLite database. Dubbed the Executive Secretary, it provides a few reports that provide additional insight into the market, and thus help you make more informed decisions.

There are three reports as of the August 2021 update:

1. Industry-Wide Profitability

Summary:
- Shows income/expenses/profitability while excluding stock sales (including IPOs and issuing new shares) and stock purchases, giving a more realistic view of profitability than the in-game Company List report.
- Includes columns for Margin, Market Cap, and Price/Book to help you make informed decisions about where to invest.
- Includes columns for Company Equity and Runway (how long until their equity goes negative) to help you decide whether you should sell, and also to know if any bankruptcy bids are likely to occur soon.

Bonuses:
- You can view multiple years at once, e.g. see the totals across the past 3, 5, or even 20 years instead of one year at a time. Don't let a profitable-for-one-year-only company fool you into investing in them!
- You can jump directly to a specific year. Want to see the 1905 results when it's 1955? No need to click "Previous" fifty times.
- You can view results for the current year-in-progress - just save the game and reload to get an update, no need to wait for January. Note that the "Runway" column works best when viewing the previous year's data (it assumes all years have 12 months).

Limitations:
- Bond repayments are included in expenses in both this report and the in-game Company List report, although only the coupon payments really affect profitability (versus cashflow, where bond repayments do matter)

Screenshot - Margin over 4 years[ajtjp.com]
Screenshot - Runway[ajtjp.com]

2. Yearly Market Sales

Summary:
- Shows the worldwide sales of all vehicle types by year
- Useful to tell at a glance if the market is shrinking


Screenshot - Yearly Market Sales[ajtjp.com]

3. Defunct Companies

Summary:
- Shows companies that went bankrupt, or were acquired (and remain subsidiaries, e.g. not spun out again)
- Includes year of founding and year of going bankrupt/acquired
- Sort of a "company graveyard" memorial that I felt was missing.

Screenshot - Defunct Companies[ajtjp.com]

---------------

To run this utility, you'll need to have Java 8 or later installed. Eventually I plan to offer a batteries-included option for Windows, but for now, if you don't have Java, you can download it from Bell Software here[bell-sw.com]. I recommend the "Full JRE" option if you are an end user; either 64-bit or 32-bit will work.

Download the latest version (August 2021) of Executive Secretary here[ajtjp.com]. If Java is installed properly, double-clicking it should run it. When it runs, it will start with a file open dialog; point it to your Gear City save and it will crunch the numbers for the report. It only reads from database; it doesn't write anything to it.

You can view the source code here[hg.sr.ht] with the database querying code here[hg.sr.ht].
Last edited by JC Denton; Aug 20, 2021 @ 8:36am
< >
Showing 1-12 of 12 comments
JC Denton Jun 29, 2021 @ 10:20pm 
In the future, I have ideas to enhance it such as:

- [DONE] Integrating P/B (Profit to Book value) into the current report, to make it a general-purpose research-your-stock-investments report.
- Average car price per company. Would you be investing in a company that makes high-end cars, or tries to focus on the volume segment of the market?
- [DONE, except debt] Add a report that shows companies' equity, debt, and cashflow situations. Detect if your investment is about to file for Chapter 7 bankruptcy.
- [DONE] Letting you easily see which companies are bankrupt, and when they went bankrupt. I've always kind of wanted there to be newspaper articles when companies go under. Maybe some sort of obituary summary for them based on their historical data.
- Geographic summaries, e.g. see total vehicle sales by continent, country, or district. You can get this manually if you tally up the Competition chart in the District screen, but it takes long enough that I usually ballpark it in practice.
- [DONE] Total market sales by year. Perhaps some charts for sales by type, letting you visualize things such as the plateauing and decline of the Phaeton. Don't be like me, introducing a new Phaeton in 1949.
- Company summaries for companies that aren't public. Sometimes they go broke and you can bid on them, but how can you make a decision based on almost no information?
- Some fun historical reports, such as showing trends in cylinder counts over time (I3 vs V4, for example), or automatic vs manual transmissions. I've tried to trend this over time for my companies, and it's a lot of work to correlate manually.
- A Showroom-style report, that lets you focus on the trends across generations of your vehicles more easily. Maybe you can even track market dominance, instead of numerical sales.
- More of a stretch, but perhaps some more details on factory costs, number of sales needed to break even in a region, etc. I don't know everything I would need to for this yet, but even a little bit of data on this could help solve questions such as, "why is my Swiss factory so much more expensive than my New York factory?"

These are pretty much all things I've wondered about through my playthroughs, and sometimes tried to categorize manually.

Since it's a free time project, expect updates to be sporadic. I'm also leaving on vacation soon, so don't expect updates until at least late July. But feel free to add your own wishes and suggestions.
Last edited by JC Denton; Aug 20, 2021 @ 8:27am
tbjack Jun 30, 2021 @ 6:30am 
wow, that sounds great! I'll give it a try in the next few days.
Eric.B  [developer] Jun 30, 2021 @ 11:49am 
Great little tool/idea. I've known several people who do dumps out of sqlite to cvs to excel. You should make a note that you have to save the game before loading it/refreshing it in your reports.

Also sorry for how messy the db is, layers and layers of stuff were added to the game over the last decade that I never intended to implement or do. It really needs a good refactoring, which is a feature bounty ticket.

FYI, the reports aren't perfectly gaap compliant mostly because of the amount of processing time it would take to do it. And limitations of the UI prevent me from having multiple, dynamically created tables which would be the best way to go about showing all this stuff in game. So I have to spit everything out into html, and that prevents me from doing dynamic input and creation needed for things like letting the player enter the year or getting a range of years. etc. Nearly all the reports are static in the game, so every little thing I do to them adds more processing to the end of turn. That sort of ties my hands.

Another feature bounty is replacing the entire UI system with a new UI library that would allow me to make dynamic tables. That would allow me to return the reports from html back into in-game GUI. But that's a pipe dream because of how expensive it would be.

(Although I will say, I probably should split out stock/asset sales. I leave in there for now, because it's faster to just throw the income number in the DB out there.)

Anyway doing all this outside of the program removes some of the limitations and issues that plagued GC. So it's a good idea, I hope it catches on as a campaign program. (Although I would have chosen QT instead of Java. ^,~ )


Originally posted by JC Denton:
Since it's a free time project, expect updates to be sporadic. I'm also leaving on vacation soon, so don't expect updates until at least late July. But feel free to add your own wishes and suggestions.

Feel free to ask if you have any questions related to the DB.

I have a formula wiki coming post release. That might help with some of your extra stuff you want to add.
Last edited by Eric.B; Jun 30, 2021 @ 11:51am
JC Denton Jul 12, 2021 @ 5:26pm 
Just checking back in after some time away from home. I was actually rather pleased how easy it was to get some basic info from the DB... the last game I wrote a utility for used a binary format, so it was much easier in this case. Will I run into some confusing areas and have questions at some point? Probably. But at least for early on, it hasn't been too difficult to piece together some queries to answer some of my basic questions, despite my rusty SQL.

I wouldn't actually be able to tell if the reports were GAAP-compliant, not being an accountant :). GearCity has increased my level of interest in accounting, however.

I'm actually rather pleased with how some reports in-game do allow you to e.g. remove the "Other" from a line chart, so the 90% share of "Other" in the early doesn't obscure the other companies' market share. And the search/sort options are quite handy. But yes, those decisions a decade ago can tie hands. My longest-running project is a bit over 10 years old, and I definitely wouldn't have built it the same way with more experience. But such is life.

As for myself, I've realized over the years that I enjoy solving problems with code more than I enjoy new programming languages, so I went with Java as what I knew that allowed me to hit the ground fast, and produce fairly pretty reports in the process. There probably are better options - but I'd rather play GearCity than learn a new language or framework for the sake of learning a new language or framework.

The formula wiki does sound like it might be useful. Factories are probably the area I've puzzled over the most (I finally closed the super-high-cost Zurich factory, but it seems to have made no real difference to my total factory costs once production shifted elsewhere... or maybe a couple hundred grand instead of the expected several million in savings per month).

Anyway, I've made a couple minor additions locally, adding in Profit/Book and Market Cap columns to help evaluate potential investments and acquisitions; as far as I'm aware the only way to view Profit/Book in-game is to view each company's stock report, which can make it easy to miss bargains. Although as it turns out, the bargain companies in my game tend to be unprofitable, often heavily so... maybe that is why they're priced as bargains.

I'm probably going to wait until near the end of the month to publish an update, so publishing the update doesn't take too much time relative to additions that I make.
JC Denton Aug 20, 2021 @ 8:33am 
I have released an updated version, which adds 80% more columns to the initial report (Market Cap, Price/Book, Equity, and Runway), to make it a lot more useful when it comes to deciding when to buy/sell stocks.

It also has two new, small reports - Yearly Market Sales (all vehicles, worldwide, divided up by year), and Defunct Companies (companies that are bankrupt/acquired, and the year of their fate).

I've added some new screenshots in the first post, and also updated the download link there. But for convenience, I'll add the link here as well:

Latest Version[ajtjp.com]

Feedback is welcome. Future improvements are likely, though the rate of them remains highly unpredictable.

P.S. Does GearCity have a forum other than the Steam one? I miss being able to inline screenshots as old-school forums allowed.
Last edited by JC Denton; Aug 20, 2021 @ 8:34am
Eric.B  [developer] Aug 20, 2021 @ 8:45am 
Originally posted by JC Denton:
P.S. Does GearCity have a forum other than the Steam one? I miss being able to inline screenshots as old-school forums allowed.

Currently we only have the Steam forums. The Steam forums sucked off most of the users of the official forums, to the point where it was pretty much only spam bots posting there. I 100% prefer the official, old-school as you call it, forums.

I plan on re-opening it when the feature bounty system goes live, since it will be heavily integrated on there, and such work has to be done off-Steam.

Eventually we may land on other third-party forums, assuming the game is accepted. But they're pretty much the same as Steam forums (Can't say whom or where on here, as it's against Steam's TOS.)

We do have a Reddit (/r/gearcity), which allows first-party image posts. (I dunno how to do more than 1, but I have seen full albums uploaded before.) Alternatively, you could use a third party host like cubeupload.com
Last edited by Eric.B; Aug 20, 2021 @ 8:47am
tbjack Aug 27, 2021 @ 5:35pm 
@JC Denton: It's a great tool. But unfortunately I have issues with the new version (2021.08.1). Some save games can't be opened. The programme simply shuts down when I try to load these saves. With the old version (2021.06.1) it works fine.

Maybe it's due to the size of the savegame (ca 5-20 MB)? With earlier save points/smaller save games (ca 2 MB), it usually works without problems. But maybe this is just a coincidence.

I use the recommended Java version (JRE 8 Full).
JC Denton Sep 4, 2021 @ 5:20pm 
@tbjack - Are you able to upload the save somewhere for me to investigate? It is true that at this point, the program assumes that the queries are going to work, so if they don't for some reason, it will shut down. Something must be different in the saves that I haven't anticipated based on the limited number of games I have to test with. Which is really just the ones I've played myself, so the saves are heavily influenced by my playstyle.

That size (5-20 MB) shouldn't be a problem, however; the latest save I am playing with is 558 MB and growing all the time. Although it does make me wonder, have you ever used the Purge Data option in Gear City to keep the size down? I haven't tested with that, but could see it potentially affecting the reports. I'm also curious in general about the difference in save sizes; even a 1911 archive I have weighs in at 48 MB, although I have been playing with 300 AIs so if you have fewer, maybe that's why we're seeing such different save file sizes.

Apologies for the slow reply; I've been on vacation and haven't been on Steam for awhile.
Last edited by JC Denton; Sep 4, 2021 @ 5:20pm
Eric.B  [developer] Sep 4, 2021 @ 6:11pm 
Depending on play styles, save games can easily get into the 1-2GB in size.

For this sort of program, you shouldn't be putting the database into memory. So file size shouldn't be a problem unless you are. In which case, don't. :)
tbjack Sep 5, 2021 @ 1:27pm 
Many thanks for your reply. Unfortunately, i just reseted the game a few days ago, so i don't have the saves anymore. But i will try to reproduce the problem as soon as possible and upload the saves.

Two other things i should mention, as they may have caused it: 1. I play with a modded version of the game (~50 AI companies; slightly modded turnevents; moderately modded compononents.xml). 2. Maybe - I'm not sure anymore - I had made small changes to one or two of the initial save games with an sqlite editor (bigger size of competitors' factories; maybe also changes to the competitors' money which sounds like a more likely source of the problem). However, the issue appeared with several save games independent of each other and I am quite sure that I didn't make changes to all of them.

As I said, I'll get back to you as soon as the problem reappears.
JC Denton Sep 11, 2021 @ 11:06pm 
I've made another update, which you can download here[ajtjp.com]. The main new feature is an Engine Chart tab; you can see a screenshot of that here[ajtjp.com]. But there are a fair amount of changes:

  • New Engine chart. This lets you chart either layouts (inline, V, radial, etc.), cylinder counts, layout/cylinder count combinations, and fuel types by year, to see the trends. You can also choose to chart them either in absolute numbers, or as percentages. This is for all your "make autogas-fueled W9 engines dominant" fantasies.
  • The secretary will now automatically open the file you most recently opened, so you don't have to select the file every time.
  • The Yearly Market Summary Report now displays industry-wide (all companies) income, expenses, and profitability. See just how much money the industry is burning per year when it's unprofitable... in my current game it peaked at $6 billion per year, in 1956, while 1926 remains the most profitable year at a $3 billion industry profit.
  • The Defunct Companies report now shows the parent of acquired companies.
  • The Industry-Wide Profitability Report is now called the Company Profitability report, since it shows company-specific information, and since the Yearly Market Summary Report now shows industry-wide profitability.
  • Reports will now use all available screen space if you maximize their windows.
  • You can now change which file is opened without restarting the program (File -> Open Save DB)
  • Error handling has been made somewhat more robust. Most notably, if a SQL error occurs for some reason, it should now be reported to you in an alert dialog, making bug reports easier.
  • Fixed a bug that can cause the behavior tbjack observed with the August release, where the program shuts down post-opening-a-save. I can't guarantee it's the exact cause, without the saves, but the odds are better.
  • Some significant code quality improvements, so that the program doesn't explode in complexity as I add more reports. There are still more to go, but this release marks a transition from "throw something together quickly as a proof of concept" to "I might keep adding to this for a while, I should make it pleasant to work on."
  • Some initial performance improvements, so the program doesn't take forever to load as I add more reports. Currently that means the new version loads roughly 14% quicker than it would have otherwise.

As the size of the change list suggests, this is the largest update so far, with a ballpark estimate of having more than twice as many changes as the first and second releases combined. While a lot of this is effort put into allowing you to switch files, and getting the chart behaving intuitively (including allowing you to hide a series by clicking on it), I think the sum of updates makes the program a lot friendlier to use. I definitely am enjoying not having to tell it which file to open every time!

I'll likely continue adding some options to the Engine charts (e.g. allowing you to view the data for your company only), covering more types of data (e.g. fuel efficiency, average horsepower), and plan to add a similar Gearbox chart in the future. To me, these charts are more for the fun alternative-history role-playing than the CEO decision-making that the initial report aimed to support, but those are two faces of the same die when answering the question of why I enjoy playing Gear City.

(Also potentially relevant to the save file discussion, it should not be an issue, as I'm using JDBC and specifically this library to query the DB: https://github.com/xerial/sqlite-jdbc)
Last edited by JC Denton; Sep 11, 2021 @ 11:07pm
JC Denton Sep 15, 2022 @ 1:36pm 
I've just released a major update to this program, which adds 3 new reports and more than quadruples the amount of info on the "Engine Layout" (now "Market Trends") report.

You can read about it and download it from the official GearCity forum, at https://www.ventdev.com/forums/showthread.php?tid=4377&pid=9907#pid9907 . I'm making that the main thread going forward, since it allows embedding pictures of the report, and a picture is worth 1000 words in explaining what the reports do.
< >
Showing 1-12 of 12 comments
Per page: 1530 50