[Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM) [Now w/ Economy Overhaul Mod Support]

The place to discuss scripting and game modifications for X4: Foundations.

Moderators: Scripting / Modding Moderators, Moderators for English X Forum

User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

[Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM) [Now w/ Economy Overhaul Mod Support]

Post by Duncaroos »

--> Duncaroo's X4 Empire Logistics Tool (Vanilla Economy) <--

--> Economy Overhaul Mod Version <--
**Mod Link

This is a quite substantial overhaul, or pretty much a replacement, to my Storage Estimator previously. When updating I was not very happy about how data was imported into the workbook, how the input tables were, and if ever someone wanted the datafiles updated to say a completely different economy with different wares, modules, etc., it would be too cumbersome to do. The new workbook also allows me to handle X4 updates to the economy, new modules, etc. more easily.

I am publishing this as a Beta, given I have not gone through every nook and cranny to catch any errors but see nothing major thus far and the VBA macros are working very well so thought I'd share to you wonderful folks :). There are a bunch of User Notes on the top right of the tables on the yellow input tabs to clarify what each input/calculated column is defined as, which was not available previously. Input and Analysis Tables have pre-defined grouped columns to show less or more information, but important results are in the "Results" tab. Please ensure you "Enable Macros" on the top of the Excel file when you open it in your application - the tool will not work otherwise. I left my stations in there for sake of example.

Will provide some detail below on the Tool:

OVERVIEW

The Empire Logistics Tool is a Macro-Enabled Microsoft Excel Workbook that allows players to have a deeper look into the production chain, habitat, storage, profit data, and base construction costs of multiple stations in a single location. Players can either create stations from scratch or import all current station plots from their save game using the Workbook. To allow future input into the Workbook - players can import construction plans to the Workbook which adds the construction plans to the bottom of the existing data.

*Note that you need to download to your local computer and run via the main Excel application; web browsers will not function. Ensure you "Enable Macros" when after downloading - not doing so will not allow the spreadsheet to function.

FEATURES & BENEFITS
  1. Production and Logistical Analysis
    1. Provide detailed analysis on the production chain of all stations within a game file.
    2. Allow stations to be set to a logistic group to redistribute wares between stations as necessary.
    3. Estimate required station storage needs based on a station's desired production time, and distinguish excess storage requirements for final products and/or intermediate products.
    4. Provide overall summaries for storage allocation and deficits, target ware storage levels (for setting in-game module settings within the Logistical Overview), station production mass balance, and logistic group mass balance.
    5. Allow stations to be set to fill habitats to maximum capacity (e.g. ship-production stations like shipyards), to see the impact on food and medical consumption.
    6. Accurate production bonuses due to workforce efficiency (per cycle calculations, as opposed to per hour like other tools).
    7. Ability to specify priority wares for Scrap Recyclers, to only produce one type of ware over another (doubles throughput for prioritized ware).
  2. Finances
    1. Provide base station building costs for all stations (using minimum, average, and maximum ware prices).
    2. Provide profit/loss per hour metrics based on best-case, average, and worse-case scenarios.
    3. Clearly show when stations need to purchase from other factions when a shortfall is occurring.
    4. Allow stations to be set to mine raw resources to avoid purchasing these inputs from external factions.
  3. User Interface
    1. Support multiple game versions (for version-locked games) and any combination of DLCs (or none at all).
    2. Provide dynamic drop-down menus to align with available sectors, modules, and wares for the specified game version and active DLCs (if any).
    3. Provide multi-language support for dynamic drop-down menus:
      1. Czech (Čeština)
      2. French (Français)
      3. German (Deutsch)
      4. Italian (Italiano)
      5. Japanese (日本語)
      6. Korean (한국어)
      7. Polish (Polski)
      8. Portuguese (Português)
      9. Spanish (Español)
      10. Turkish (Türkçe)
      11. Ukranian (Українська)
    4. Provide easy and quick way to import new stations into an existing Workbook.
    5. Assign stations to X4 Sectors to set correct production-related parameters [e.g. Solar Output].
    6. Allow build method to be defined (only applicable for select modules shared between COP and Terran economies (e.g. Scrap Processors, Observation Decks, etc.)
    7. Ensure production time and excess storage space requirements can be specified globally for all stations, or be overridden on a per-station level or per-production module level.
HOW DOES IT WORK

The Workbook uses various data tables to store information regarding wares, station modules, production methods, storage data, habitat data, and workforce consumption data that were generated from importing actual X4 data files using XML mapping. These data tables are used in a variety of dynamic ranges and formulas utilizing mainly LET(), XLOOKUP(), and INDEX() functions; some calculations, such as redistributing wares, requires the use of custom functions created in Excel VBA. Game / construction template importing, updating languages within data entries, and updating the core production analysis table all use Excel VBA macros to perform necessary actions.

Game file import needs the uncompressed XML file (*.xml, not *.xml.gz). Just unzip the .gz file to get the XML file - and you're done (it's that easy!).

All Excel VBA macros are open to anyone to view. I've tried to add in comments in case anyone is interested to somewhat understand what is happening. Macros only read external files selected by the "File Open" dialog when executing specific import macros, and does not write to your computer. As you do need to run this with Macros enabled - you need to download the file to your computer and run using the full Excel application (using the web browser will not work).

BETA PARTICIPATION

I always welcome any comments or suggestions to improving the worksheet. Please reply to this post if you encounter something wrong :!:

Some things I have in mind, but have not figured out yet (likely a wish list at the moment):
  • Handling a station being part of multiple logistic groups...
  • Results page for summarizing Logistic Overview Settings for handling intermediate wares (i.e. Buy all but, Sell all but)...
  • Production optimizer (e.g. auto-update production module quantities to meet station production chain requirements)...
  • Station cost minimizer (e.g. using 1x M Container module vs 2x S Container Module [e.g. the 2x S Container is more expensive than 1x M Container even though it has less storage space])...

Happy managing!
Last edited by Duncaroos on Sat, 25. Jan 25, 03:12, edited 10 times in total.
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

Small update to Beta v0.12 (use same link at beginning of OG message).

- Checked v7.1 HF1 and HF2 updates - no impact.

- Minor: Fixed error where solar and/or workforce bonus for station with multiple modules (e.g. 3x Refined Metals) was rounding down the number of units produced per cycle for the group, and not per module, which showed a higher production than actual.
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

A decent update to the tool (now Beta v0.20) (use same link at beginning of OG message):
  1. Import Game Tool
    1. Added functionality to select another faction for importing from a X4 Save File. Benefits (intended & unintended):
      1. Review production shortages to identify areas of improvement by Player's faction [relevant thread]
        • user will need to do their best to assume logistic groups to see shortages in specific areas; still trying to implement the option to allow a station to be within multiple logistic groups to automate this.
      2. Sneak a peek on Xenon, Kha'ak, and other faction installation locations for military purposes.
  2. Stations Tab
    1. Added "Saturated Market Profit per Hour" metric.
    2. Updated grouped filters to improve levels of detail.
  3. Module Assignments Tab
    1. Fixed "Intermediate Ware" check in the Module Assignment tab.
    2. Allow assignment of miners to specific modules.
  4. Analysis Tab
    1. Updated habitat consumption calculations to not round units/cycle. Rounding is now done at units/hr using Banker's Rounding (to be consistent with X4 Logistics Panel).
  5. Result Tabs
    1. Reconfigured Pivot Table Formatting.
    2. Grouped by Ware Storage Type (Container,Liquid,Solid,Condensate) for Global Mass Balance Results.
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
Wozzlebatneo
Posts: 2
Joined: Wed, 4. Sep 24, 00:44
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Wozzlebatneo »

I have tried to use your tool as it looks like something I really need. However, I must be a real klutz because I cannot work out how to get the Spreadsheet to input OR read the Savegames XLS ??? Any advice would be gratefully accepted.
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

You're not a klutz, I know my sheets are sometimes complicated, but most of the people I work with say once they get familiar with the sheets, sheets are useful. Getting freshly into it is more difficult :lol: !

Did you enable macros? What warning do you get (feel free to send a screenshot in a DM)? Excel typically disables macros for anything on your download folder - you may need to move it to say your "documents" folder to override any settings that completely prohibit macros from being run.

Note initially you need to unzip your save file so it is uncompressed; working on trying to handle it on the spreadsheet side
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
Wozzlebatneo
Posts: 2
Joined: Wed, 4. Sep 24, 00:44
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Wozzlebatneo »

So, I downloaded the Spreadsheet, made a folder for it in my Documents, copied the unpacked game save to that folder and ran the Spreadsheet?. Yeah, Klutz, NOW I see an "Import X4 Save File" button that was showing "error" before. Thank you
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

A slight update to the tool (now Beta v0.21) (use same link at beginning of OG message):
  1. General Macro Code
    1. Fixed macro VBA error where table autofilters were disabled. Autofilters now re-enabled during code execution to allow necessary actions to be performed.
  2. Import Game Tool
    1. Now imports Station IDs (ABC-123) from save file and exports to respective column in the Station List tab.
    2. Changed station name output to "Unknown (Auto Named)" where an in-game script is used to determine the Station Name.
  3. Stations Tab
    1. Added "Game Station ID" column to assign 6-digit code (ABC-123) determined in-game (Optional Input).
      • Useful for mass importing a save file a second time and you need to compare past logistic / storage settings from a previous file that has different Numerical Station IDs (0001, 0002, 0003) assigned from when it was first imported.
  4. Analysis Tab
    1. Fixed Base QTY per cycle calculation where VBA formula outputs "-0" value.
    2. Fixed "Redistribute Ware" calculation that output a "#VALUE!" error when a station contains a Habitat Module, but the "Forced Workforce Bonus" in the Station tab is set to 0%.

Of course anyone is free to provide feedback and requests and I will see what I can do :)
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

I was going to do a v0.30, but decided there were some QOL improvements I could push out quicker (now Beta v0.22) (use same link at beginning of OG message):
  1. External Data - Languages
    1. Added Ship and Ship-Related Equipment to Language Table. Reduced table size by eliminating all non-used 't file' pages and items. [For use in larger update later]
  2. External Data - Station Modules
    1. Fixed required version for Ship Fabrication and Maintenance Build Modules (v2.0 required).
  3. External Data - Production
    1. Added "Build" Modules to Data Table to account for "Optimum Workforce" and "Optimum Build Drones" for Ship Fabrication and Maintenance Modules.
  4. Import Game Tool
    1. Ship Fabrication/Maintenance Modules now imported when "Production-Only Modules" is set to True.
    2. Fixed code running update to Analysis Table when not required.
    3. Macro now checks X4 save file is in the correct format prior to loading (Checks presence of "seed" attribute in <info>)
    4. Macro now checks X4 construction plan is in the correct format prior to loading (Checks presence of "name" attribute in <plan>)

Next update, I hope to address Wharf/Shipyard Consumption Estimates (please see poll - I am looking for opinions!)
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

Small v0.23 update:
- Added missing language data for 'Raw Scrap' ware.
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}
User avatar
Duncaroos
Posts: 718
Joined: Wed, 4. Jan 12, 22:23
x4

Re: [Beta][External Tool] Empire Logistics Tool (Excel Workbook XLSM)

Post by Duncaroos »

To support games that use the "Economy Overhaul Mod", a new version of the Empire Logistics Tool has been created. Link to this version is here: https://drive.google.com/file/d/1Bjtklz ... sp=sharing

Change Log for Economy Overhaul Mod version only (Vanilla version unaffected):
  1. External Data - Languages
    1. Added Pages 97100, 97101, 97200, 97201, 97300 for German, English, and French.
    2. Removed language support for everything but the three languages mentioned above.
  2. External Data - Sectors
    1. Updated Mercury Sunlight Efficiency Factor.
  3. External Data - Wares
    1. Added Economy Overhaul Mod new economy wares. (Requires TER DLC).
    2. Replaced vanilla wares with complete revision made by Economy Overhaul Mod.
  4. External Data - Storage / Habitats
    1. Updated ARG/PAR/TEL/SPL S/M Container Storage Capacities.
    2. Updated ARG/TEL/SPL S/M Habitat Capacities.
    3. Updated TER S/M/L Habitat Capacities.
    4. Updated ARG/PAR/TEL/SPL M/L Storage (All Types) Prices and Material Costs.
  5. External Data - Station Modules / Production
    1. Added Economy Overhaul Mod production modules and processes.
    2. Updated TER connection, piers, defence, and production modules to be more expensive.
    3. Updated TER Energy Cell and Metallic Microlattice module build times.
    4. Updated TER Silicon Carbide and Metallic Microlattice Module Prices.
  6. Import Game Tool / Import Construction Plans
    1. Functionality currently Disabled, due to misalignment between the mod author's module names are from vanilla...VBA macro code currently cannot handle it. (Please inquire if you want this functionality - I will only fix based on demand due to the effort involved).
Playing X4+All_DLC on:
CPU: Ryzen 5 5600X; RAM: 4x8GB DDR4 3200MHz; GPU: GTX 1070 8GB, Driver v536.23, DirectX 12.0; OS: Win10 Home 22H2 (19045.4780); Monitor: Single Acer S232HL 1920x1080

Duncaroo's Empire Logistics Tool (v0.23 Beta) - {{Vanilla Economy - Direct link}} {{Economy Overhaul Mod Version - Direct link}}

Return to “X4: Foundations - Scripts and Modding”