Tracking Spreadsheets (Full Version)

All Forums >> [New Releases from Matrix Games] >> Gary Grigsby's War in the East Series



Message


ledo -> Tracking Spreadsheets (11/6/2017 5:17:13 AM)

Heyo, I've spent quite a bit of time on the forum recently, reading AARs mainly. I've had a couple of stabs at the game as Axis against AI but I kept losing interest as everything got pretty chaotic and I lacked context for my decisions. I decided to try to track things better and build up my knowledge of the OOB. I looked around for some tracking spreadsheets but couldn't find any that went into precise detail on anything other than production stats. The commander's report is too clunky and difficult to read and organize. I was wondering what you use to keep track of your OOB. I've spent the last two days playing and building a 90% automatic ground force and air force tracker but I'm not sure if I'm missing anything or there's anything to add.

I was going to post the link to my spreadsheet, but I don't have the required ten posts yet. You can pm me if you too are looking to save time building a tracking spreadsheet.

Currently, for the Ground Force OOB, I have all the key stats with conditional formatting for easy reading. I'm using index matches and countifs to pull support levels on every individual unit (excluding construction and AA support units) and index-matching to add the support of that unit's HQ and adjusting for leader difficulty ratings and leader skills to provide a cascading cumulative support level for each combat unit (which includes all possible support from O.K.H, Army Group, Army and Corps that could potentially reach the division). This has been really useful for reorganizing my support units but I haven't added in distance and command capacity penalties to the leader test modifiers yet, has anyone got a sheet that does this? I'm not 100% clear on how these penalties work so I've been putting off adding those calculations.

I haven't yet automated the movement of divisions/Corps between HQs (unfortunately I didn't work out how to do this until I started working on the Airforce OOB). It usually takes about 15-20 minutes after a reorganizing turn to manually move your units around in the sheet (although support units will move automatically whenever you upload a new TOE).

The Airforce OOB tracker is more automated. It uses the air bases as constants and automatically pulls all wings attached and their key stats (fatigue, morale, experience etc.), as well as checking their plane models against defunct models to let you know if they need an upgrade. The array formula is a bit of a drain on your processor but you can move your air wings however you want import the new data from the game and it will automatically move the wings between the various air bases as well as the national reserve.

For production stuff I've mainly just taken ideas from AARs. I think it was maybe Dinglir or Tyronec who gave me the model for national OOB and production tracking, but I can't remember exactly.

Anyway, let me know if you have any spreadsheets you use, or you're also new and want to use mine as a starting point.




EwaldvonKleist -> RE: Tracking Spreadsheets (11/6/2017 9:40:56 AM)

I am always interested in fancy spreadsheets :)
Pm has been sent




AlessandroD -> RE: Tracking Spreadsheets (11/6/2017 9:43:02 AM)

Hello,

I'm really interested to see your spreadsheets, if you want I can upload it for you.
I'm getting back after a very long break and your work seems very helpful for me.




EwaldvonKleist -> RE: Tracking Spreadsheets (11/6/2017 1:56:39 PM)

Hi all,
the spreadsheet can be downloaded here:
https://drive.google.com/open?id=1oO1n_7hZJJRJV-wbJgLfvdce91HTBYeT
It is an amazing display of excel skills, and here is a further description how it has to be handled.

quote:

Things that need to be changed manually:

Make sure to change the date in the the basic setup. Currently this is the check to see which air models are out of date. If you keep mine (Jun-41) you might not get an accurate reading for your own data.

You need to import the unit tables from the commander's report and then copy paste them into the raw data worksheets in exactly the same format. Also copy paste the unit data into the Ground Force OOB Support Calc work sheet exactly the same way, including only "Type" "Name and "HQ Name" columns in the exact same order.

Moving units around in your OOB. You have remove and add rows to make room and then drag down all the formula columns. Remember that there are hidden columns so expand those if something isn't working. This includes breaking down and building back up a division. If you see an error next to a division try adding 1/ in front of it and it will probably fix it. You'll then have to add two rows and input 2/ and 3/. Eventually I'll try automate this as is done in the Air Force worksheet. Ground force leaders and support levels will change automatically. OKH sometimes bugs out though, if it does copy paste the word OKH from the raw data and replace every mention of OKH in the Ground Force OOB sheet (including hidden cells), there should be 5, i think.

One of the hidden columns in the Ground OOB is the Support Level Modifier this includes the chance of commitment as per the manual. This is always 1 for a division. At the corps level you will see a denominator of 10, 20 for army level, 40 for army group and 80 for O.K.H. If uncertain always copy a formula from the exact same type of HQ or unit.

If you have more than 8 air wings in an air base you will have to unhide all cells, add additional rows to the air base and make sure the 1-8 numbers in cells A:A extend down to however many number of air wings you wish the worksheet to find.





Telemecus -> RE: Tracking Spreadsheets (11/6/2017 2:29:02 PM)

I have some quite extensive spreadsheets on tracking - if you are into indexes, lookups and countifs etc. you will be quite at home. Happy to send them to any who want them or even upload them to the site if interested. I would be quite interested in developing crowd-supported spreadsheets which all could use in their games rather than each developing their own and presumably with duplication of effort.

Ledo - I sent you a PM!





HardLuckYetAgain -> RE: Tracking Spreadsheets (11/6/2017 2:58:18 PM)


quote:

ORIGINAL: Telemecus

I have some quite extensive spreadsheets on tracking - if you are into indexes, lookups and countifs etc. you will be quite at home. Happy to send them to any who want them or even upload them to the site if interested. I would be quite interested in developing crowd-supported spreadsheets which all could use in their games rather than each developing their own and presumably with duplication of effort.

Ledo - I sent you a PM!





Am I the only one that does not play using spreadsheets or other aids? Maybe that is why I sux at my games :(




EwaldvonKleist -> RE: Tracking Spreadsheets (11/6/2017 3:07:07 PM)

To be honest I dont believe spreadsheets do improve the gameplay. I only use them because I like charts :)




Telemecus -> RE: Tracking Spreadsheets (11/6/2017 4:12:28 PM)

quote:

ORIGINAL: HardLuckYetAgain
Am I the only one that does not play using spreadsheets or other aids? Maybe that is why I sux at my games :(


I wish I could sux like you then! I think though spreadsheets are a minority sport.

It may be worth puncturing the myth of spreadsheets at this point though.

In "Defense of the Soviet Union begins ONE hex west of Pskov" I believe HardLuckyetAgain once wrote they spent a lot of time clicking on lots of units to find the ones they wanted. That is the point where I said aha - that is why I use a spreadsheet! Beginning of every turn I export the commanders report. And for professional reasons I am a very quick user of spreadsheets - I know I can quickly filter and find what I want on them, often quicker than the CPU can process my request! If you have this muscle memory than it is simply a time saving device on doing lots of clicks.

A parallel can be made with the management consulting industry. Spreadsheets used to be large pieces of paper stretched over a table, or even the whole floor of a room. Updating them meant getting pencils and erasers out and could last days costing a lot of money. So it was easier to pay a substantial, but slightly less large, sum of money to a retired industrialist with grey hair whose experience had already worked out the answer and get it from them. When the personal computer was created young graduates could be employed to do the spreadsheets and get the same answers for even less money.

HardLuckYetAgain is a very experienced player - whatever they say [:D] They are our equivalent of the "grey hairs" - spreadsheets are not necessary because they have already worked out the answers from doing it countless times already. And if clicking on counters is the quickest method for them that is the right way to go.

At best spreadsheets can be a time saving device and a quick data cruncher. They may give some answers, but they do not make the questions - for that you need the talent of the player. For that a spreadsheet is never a substitute.




Searry -> RE: Tracking Spreadsheets (11/6/2017 5:00:29 PM)

The only reason I would use a spreadsheet is to find the highest CV units.




ledo -> RE: Tracking Spreadsheets (11/6/2017 8:18:10 PM)

Spreadsheets definitely aren't exactly necessary. Again, I only started building one to be able to better understand the OOB at the beginning, much of the automation stuff came later when I saw opportunities to speed things up. When I started going through all the data bit by bit for the manual inputs actually gave me a much better contextual view of my forces (a view that I could now support by going back across the data and checking my assumptions).

However, spreadsheets have been important for a new player like myself. Having the data visualised from green-red allows me, as one example, to actually see quite quickly who has very little support and who is completely stacked. For example, realizing just how stacked Schroth's XII Corps is, while Hoth's 3rd Panzer Group and much of AGS are wallowing with minimal support, a completely useless state of affairs if you use 4th Army to take Brest-Litovsk and breach the line. As a new player, for the first time I felt quite confident in my repositioning of my support units, and my choice of who to use to make a breakthrough, guard the line etc. Moving planes to and from the national reserve is also faster and more methodical, I don't have to click through all the units and or find them one by one from the commander's report I just scroll through the list and see who's below 50% ready or has a combination of high fatigue and needs an upgraded plane model etc.

While building the spreadsheet took quite a bit of time, my turns actually take much less time now, and the decisions I make are based on far greater amount of information. However, an experienced player probably already knows all their forces like chess pieces so its probably not necessary.





56ajax -> RE: Tracking Spreadsheets (11/7/2017 7:55:11 AM)


quote:

ORIGINAL: HardLuckYetAgain


quote:

ORIGINAL: Telemecus

I have some quite extensive spreadsheets on tracking - if you are into indexes, lookups and countifs etc. you will be quite at home. Happy to send them to any who want them or even upload them to the site if interested. I would be quite interested in developing crowd-supported spreadsheets which all could use in their games rather than each developing their own and presumably with duplication of effort.

Ledo - I sent you a PM!





Am I the only one that does not play using spreadsheets or other aids? Maybe that is why I sux at my games :(

No...




fetmun -> RE: Tracking Spreadsheets (11/8/2017 7:16:55 AM)

I am wonder how could you extract raw data from game engine? Is it some txt of xls reports that generates automatically at the the end of each turn?




morvael -> RE: Tracking Spreadsheets (11/8/2017 7:32:33 AM)

Yes, look at Export button in Commander's Report (bottom right if I remember)




tomeck48 -> RE: Tracking Spreadsheets (11/8/2017 9:30:32 PM)

I looked at the Commander's report but don't see an Export button. I'm really interested in finding it as I'm currently entering my own data in my own spreadsheets so if I could automate some of that it would be really great.




thedoctorking -> RE: Tracking Spreadsheets (11/8/2017 10:09:22 PM)


quote:

ORIGINAL: tomeck48

I looked at the Commander's report but don't see an Export button. I'm really interested in finding it as I'm currently entering my own data in my own spreadsheets so if I could automate some of that it would be really great.


Little button down at the lower right. When I click it, it says "exported", but I can't find a file.




ledo -> RE: Tracking Spreadsheets (11/8/2017 10:27:52 PM)


quote:

ORIGINAL: thedoctorking


quote:

ORIGINAL: tomeck48

I looked at the Commander's report but don't see an Export button. I'm really interested in finding it as I'm currently entering my own data in my own spreadsheets so if I could automate some of that it would be really great.


Little button down at the lower right. When I click it, it says "exported", but I can't find a file.


I'm at work and I don't have the file in front of me but I think it appears in the save folder. Otherwise, it'll be in the main game folder, but I'm pretty sure it's the save folder.




ledo -> RE: Tracking Spreadsheets (11/8/2017 10:36:04 PM)

Just as a side note, for those looking for more automation I'll be probably adding a feature that counts the number of corps/division etc. under each HQ then automatically creates slots for them and then fills them in with an array formula similar to the air force worksheet. If that's not possible or beyond my skill level, I'll have preset slots with a dynamic drop-down list to select existing HQs from the export (however, like the air force OOB that will mean a lot of unnecessary blank rows). I'll post it here (or get someone else to) when I'm done. Hopefully, that should make the sheet a lot more intuitive and easy to use. After that I'll look to put in a macro that makes importing as simple as possible. I'm a bit worried the changing number of columns in the export .csv might break it though, but there's probably some excellent import macros out there on the internet.




tomeck48 -> RE: Tracking Spreadsheets (11/9/2017 2:47:54 AM)

For the life of me, I'm looking at the Commander's report and I don't seen a little button that says export. Not lower right, not lower left, not up top, not in the middle.

In the lower right I see two rows. Depleted, Combat Ready, Loaded and Withdraw. Under that it says HQ range. No buttons. Can you give me a better hint. Sorry to be a pest but I just don't see it.




thedoctorking -> RE: Tracking Spreadsheets (11/9/2017 2:59:54 AM)

Waaaay down at the bottom when you have the Units tab open. Reading from the left, you have ALL (under Unit Size) ALL (under Frozen), ALL (under Static) Mech Airb AT Front SPGun Mort Fort Prtsn (all those in red), then ALL (under HQ range) and then, all the way over, Export. In white, so you can click on it.

What version are you using? I have the public beta. Maybe it was recently added and you have an older version?




morvael -> RE: Tracking Spreadsheets (11/9/2017 6:01:37 AM)

I guess so, this must be quite old version.




Telemecus -> RE: Tracking Spreadsheets (11/9/2017 11:56:37 AM)

May be useful for any one else going through this thread - go to this thread for a big picture with a big arrow pointing to the export function. http://www.matrixgames.com/forums/tm.asp?m=4363500&mpage=1&key=look%2Csave%2Cfolder�

I know the export was added in a recent version




morvael -> RE: Tracking Spreadsheets (11/9/2017 12:04:05 PM)

Happy to see someone put that function to use.
What I would like to see is a tool that would take a collection of such files and produce some nice stats & graphs in a PDF.




morvael -> RE: Tracking Spreadsheets (11/9/2017 12:10:43 PM)

Maybe even someone could make a tool which would make a mini map showing positions of forces. I did such thing once, for HPS games:


[image]local://upfiles/22250/70772C429DB24DB3851B90AA410C67F6.jpg[/image]




morvael -> RE: Tracking Spreadsheets (11/9/2017 12:11:34 PM)

If you join such images from every turn in one gif you get a nice animation of moving and fighting forces.

[image]local://upfiles/22250/DE92D98E1F134CA6B95682D88DC9CEEE.jpg[/image]




Telemecus -> RE: Tracking Spreadsheets (11/9/2017 12:13:27 PM)

In PMs with Ledo I suggested amalgamating our spreadsheets into something generic that could be hosted and used (or adapted) by others for their own use. I am interested in such projects of automation - and of getting into geodata! I am guessing a few others have indicated their interest?




morvael -> RE: Tracking Spreadsheets (11/9/2017 12:27:57 PM)

I believe you need to involve some kind of programming language to produce more fancy output from data than spreadsheets will allow. Maybe there is someone amongst WitE players with such skills?




Telemecus -> RE: Tracking Spreadsheets (11/9/2017 12:56:24 PM)

You could use the spreadsheets as a front end or data feed for something more fancy. The great thing about spreadsheets, if you think of it as programming by the end user, is that many people are happy to use them or adapt them if they are just formulas. If you get in to pivot tables the numbers goes down. And get as far as say visual basic and the numbers drop right off. If you had spreadsheets that all could use as a front end separate from what else it would be used for I think that would be best. When it comes to GIS stuff I have only used some of the open source programs available- happy to contribute but I guess there are some real experts lurking!




morvael -> RE: Tracking Spreadsheets (11/9/2017 1:01:11 PM)

Must... resist... temptation... to write such mapping software. I have too much on my plate...
And yet, it would take no more than few hours for a basic version...




ledo -> RE: Tracking Spreadsheets (11/9/2017 5:53:38 PM)

That map looks fantastic. I'd love to see that.




tomeck48 -> RE: Tracking Spreadsheets (11/10/2017 3:41:32 AM)

I'm playing version 1.09. The button must have come in later.




Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
1.84375