Excel Based AAR Template.
Rev 8. 7/27/2023.
Has a lot of automated functionality including report generation, is EXCEL based and contains 35 tab.
The way this template is now it's very easy to step all over automated functionality. I haven't worried about that until now because I've been the only one using this version and even if I step on something, which I do occasionally, I know how to fix it.
I will document each tab of this template including the automated functionality in the following posts.
A basic knowledge of EXCEL is required; however, a moderate level of proficiency with EXCEL is even better (i.e., desired).
Rev 9. 8/18.2023.
1. Fix an error in the the USSR BPP Calculation, Sheet=USSR.
Excel Based AAR Template.
Moderator: Shannon V. OKeets
Excel Based AAR Template.
Last edited by rkr1958 on Fri Aug 18, 2023 7:50 pm, edited 1 time in total.
Ronnie
Re: Excel Based AAR Template.
WA -> Weather & Actions -> Tab #1.
(Turn) Enter turn # (i.e., 0-38 and Month & Year will automatically filled in by EXCEL.
(Month & Year) Automatically filled in.
(Impulse) Enter impulse #.
(Modified Roll) Enter modified weather roll (if applicable); otherwise leave blank.
(Advance #). Enter impulse advance # (if applicable)
(Roll Modifier). Enter roll modified (if applicable) for the next impulse pair weather roll.
Enter weather for each zone (F=fine, R=rain, St=storm, Sn=snow, B=blizzard). Excel will automatically color the enter to match MWIF's coloring scheme.
Enter actions as appropriate for the active major powers.
(EOT). If possible turn ends, enter the number (or less) needed to end it.
(Actual). Enter the actual EOT die roll. If equal of less (i.e., turn ends) cell is automatically colored red, if not (i.e., turn continues) the cell is colored green.
(Notes). Enter any notes for a given impulse that you wish.
You can use the EXCEL hide function to hide rows then copy and paste into a graphics program (e.g., Gimp 2) to generate Weather & Action graphic for a given turn.
This sheet (tab) is linked to the sheet 33 (LL) to map turn # to month and year. Specifically columns Q-U and rows 2-40 of that sheet.
- Tab-01-WA.png (120.9 KiB) Viewed 1397 times
(Month & Year) Automatically filled in.
(Impulse) Enter impulse #.
(Modified Roll) Enter modified weather roll (if applicable); otherwise leave blank.
(Advance #). Enter impulse advance # (if applicable)
(Roll Modifier). Enter roll modified (if applicable) for the next impulse pair weather roll.
Enter weather for each zone (F=fine, R=rain, St=storm, Sn=snow, B=blizzard). Excel will automatically color the enter to match MWIF's coloring scheme.
Enter actions as appropriate for the active major powers.
(EOT). If possible turn ends, enter the number (or less) needed to end it.
(Actual). Enter the actual EOT die roll. If equal of less (i.e., turn ends) cell is automatically colored red, if not (i.e., turn continues) the cell is colored green.
(Notes). Enter any notes for a given impulse that you wish.
You can use the EXCEL hide function to hide rows then copy and paste into a graphics program (e.g., Gimp 2) to generate Weather & Action graphic for a given turn.
- Graphic-WA.png (44.38 KiB) Viewed 1397 times
- Tab-33-LL.png (139.63 KiB) Viewed 1397 times
Ronnie
Re: Excel Based AAR Template.
L -> Wargaming Logging -> Tab #2.
This tab is where you log your game. I've developed this sheet with a lot of flexibility to allow one to log and/or modify the logging items as they wish.
(Turn) Turn #
(Month & Year) Automatically filled in based on turn # and is linked to the LL Tab, columns Q-U.
(Impulse) Impulse #
(Rnd/Seq) Round or sequence number (e.g., air-to-air, naval combat, MP use oil, etc.). However you wish to sequence and/or leave blank.
(Theater). Limited to Theaters as defined in column B, rows 2-16 of the LL Tab. Type in or pull down for Theater (e.g., Asian, CBI, Med).
(Att MP). Attacking or primary major power, faction or side. Limited to MP's as defined in column C, rows 2-18 of the LL Tab.
(Def MP). Defending or receiving major power, faction or side. Limited to MP's as defined in column C, rows 2-18 of the LL Tab.
(CBT TY). Combat or function type. Limited to column D, rows 2-8 of LL Tab.
(CBT STY1). Combat or function subtype #1. NOT limited but recommend that one consistently as possible following certain standards for filtering reasons as will be demonstrated later and throughout the rest of this documentation.
(CBT STY2). Combat or function subtype #2. NOT limited but recommend that one consistently as possible following certain standards for filtering reasons as will be demonstrated later and throughout the rest of this documentation.
(Target). Target of a given log entry.
(Cols N-S). 6 "configurable" data/text cells (discussed in more detail later).
(Log). Log item narrative. "Ctl-C" for line return.
Linked to LL Tab.
This tab is where you log your game. I've developed this sheet with a lot of flexibility to allow one to log and/or modify the logging items as they wish.
- Tab-02-L.png (228.53 KiB) Viewed 1365 times
(Month & Year) Automatically filled in based on turn # and is linked to the LL Tab, columns Q-U.
(Impulse) Impulse #
(Rnd/Seq) Round or sequence number (e.g., air-to-air, naval combat, MP use oil, etc.). However you wish to sequence and/or leave blank.
(Theater). Limited to Theaters as defined in column B, rows 2-16 of the LL Tab. Type in or pull down for Theater (e.g., Asian, CBI, Med).
(Att MP). Attacking or primary major power, faction or side. Limited to MP's as defined in column C, rows 2-18 of the LL Tab.
(Def MP). Defending or receiving major power, faction or side. Limited to MP's as defined in column C, rows 2-18 of the LL Tab.
(CBT TY). Combat or function type. Limited to column D, rows 2-8 of LL Tab.
(CBT STY1). Combat or function subtype #1. NOT limited but recommend that one consistently as possible following certain standards for filtering reasons as will be demonstrated later and throughout the rest of this documentation.
(CBT STY2). Combat or function subtype #2. NOT limited but recommend that one consistently as possible following certain standards for filtering reasons as will be demonstrated later and throughout the rest of this documentation.
(Target). Target of a given log entry.
(Cols N-S). 6 "configurable" data/text cells (discussed in more detail later).
(Log). Log item narrative. "Ctl-C" for line return.
Linked to LL Tab.
- Tab-33-LL.png (139.63 KiB) Viewed 1365 times
Ronnie
Re: Excel Based AAR Template.
L (Tab #2). Adjustable/Variable Headers.
Column U, row 1 (yellow cell) may be used to change your logging tab header.
This cell is like to tab #32 (MK), which I've currently programmed with 36 different header type. One is free to modify these headers or add to their number. Headers from this (MK) tab are linked to the (L) tab by the sequence numbers in column A. You must keep these number in sequence (i.e., 1,2,3, ...) and if you add to the header must sequence them up from there (i.e., 37,38,...) in order to link properly to the (L) Tab.
Column U, row 1 (yellow cell) may be used to change your logging tab header.
- Tab-02-Adjustable-Header.png (294.31 KiB) Viewed 1363 times
- Tab-32-MK.png (233.41 KiB) Viewed 1363 times
Ronnie
Re: Excel Based AAR Template.
Logging Tab (L -> Tab #2) Excel Filtering
I use the Excel filtering function all the time to both review events and to produce reports and graphics to communicate my AAR. I use the next tab (LRG -> Tab #3) to produce these but first an example of using Excel filtering function.
I wish to produce a report/graphic of all Japanese strategic bombing, ground strike & ground support vs China an in reverse chorological order.
(1) First filter on theater (Asian).
(2) Next, filter on Att MP (JPN).
(3) Then, filter on CBT Ty (col J) (AC).
(4) Then, filter on STRAT, copy the Global # (col A), paste into a temp worksheet, sort in descending order and (temporarily) save.
(5) Then, filter on GSTK copy the Global # (col A), paste into a temp worksheet, sort in descending order and (temporarily) save.
(6) Finally, filter on GSPT copy the Global # (col A), paste into a temp worksheet, sort in descending order and (temporarily) save.
I use the Excel filtering function all the time to both review events and to produce reports and graphics to communicate my AAR. I use the next tab (LRG -> Tab #3) to produce these but first an example of using Excel filtering function.
- Tab-02-Excel-Filtering.png (274.21 KiB) Viewed 1358 times
(1) First filter on theater (Asian).
(2) Next, filter on Att MP (JPN).
(3) Then, filter on CBT Ty (col J) (AC).
(4) Then, filter on STRAT, copy the Global # (col A), paste into a temp worksheet, sort in descending order and (temporarily) save.
- Tab-02-Excel-Filtering-Example.png (242.13 KiB) Viewed 1358 times
(6) Finally, filter on GSPT copy the Global # (col A), paste into a temp worksheet, sort in descending order and (temporarily) save.
Ronnie
Re: Excel Based AAR Template.
LRG -> Tab #3 -> Report/Graphic Generation Tab.
I use LRG (log report generation), Tab #3 for log report generation. Redundant, I know.
All you need to do is to paste in the global #'s, in the order you wish, for the events you wish to see in your report. In this example I enter the global #'s sorted in descending number of all Japanese strategic bombing raids in Asian versus China.
I also populate cell U1 with the value value MK tab value for the strategic bombing header.
Now this tab allows one to insert a MK header in any row they wish given the global # for that row is left blank the and column U for that (yellow) cell is populated appropriately (e.g. 3 for ground strike in row 28 for this example).
I then paste in the global numbers in descending order for Japan's ground strikes against China.
Similarly I then add in the ground support header (4 in row 40 for this example) and then paste in in descending order the global numbers for for Japan's ground support against China. You report is already ready to go except all entries fill only 1 line even if they spilled over into multiple lines in your L, tab #2. What you need to do is highlight all report rows (rows 2 - 53 in this example) and then double click between any two row numbers on the far extreme left of your worksheet. Excel will automatically adjust the height of all highlighted rows so that all text is shown.
I use LRG (log report generation), Tab #3 for log report generation. Redundant, I know.
- Tab-03-LRG.png (92.79 KiB) Viewed 1353 times
I also populate cell U1 with the value value MK tab value for the strategic bombing header.
- Tab-03-LRG-Exampe-A.png (227.43 KiB) Viewed 1353 times
I then paste in the global numbers in descending order for Japan's ground strikes against China.
- Tab-03-LRG-Exampe-B.png (337.12 KiB) Viewed 1353 times
- Tab-03-LRG-Exampe-C.png (312.9 KiB) Viewed 1353 times
Ronnie
Re: Excel Based AAR Template.
LRG -> Tab #3 -> Report/Graphic Generation Tab -> Graphic Example.
To produce a graphic from this for my AAR I decide to select and hide columns F,G,H,I & J. Then I selected, copied, pasted columns A-T and rows 1-53 into a graphic program (Gimp 2) and saved the graphic as a png. That what's posted below. Of course afterwards I unhid the columns that hid for future report.
To produce a graphic from this for my AAR I decide to select and hide columns F,G,H,I & J. Then I selected, copied, pasted columns A-T and rows 1-53 into a graphic program (Gimp 2) and saved the graphic as a png. That what's posted below. Of course afterwards I unhid the columns that hid for future report.
- Tab-03-Graphic.png (307.96 KiB) Viewed 1352 times
Ronnie
-
Mayhemizer_slith
- Posts: 9330
- Joined: Wed Sep 07, 2011 2:44 am
- Location: Finland
Re: Excel Based AAR Template.
Interesting. I bet you get nice statistics from your game. I don’t even know how many times I have bombed enemy factories.
If your attack is going really well, it's an ambush.
-Murphy's war law
-Murphy's war law
Re: Excel Based AAR Template.
I do. Just for grins I decided to extract my land combat stats (A & B) for all LCs through the end of turn 11 (May/June 1941).Mayhemizer_slith wrote: ↑Fri Jul 28, 2023 8:38 pm Interesting. I bet you get nice statistics from your game. I don’t even know how many times I have bombed enemy factories.
Assault Land Combat Stats.
(1) # = 64.
(2) Avg Ex[PWIN]=0.975
(3) Avg WIN=0.938
(4) So, actual results are a bit lower than expected by 3.7% (i.e., 0.037) for assault.
(5) Fractional roll in 24 of the 64 assaults. Ex=499.5, Avg=597, Range 46 - 974.
(6) 2D10 roll x 64. Ex=11, Avg=10.9, Range 3 - 19.
Blitz Land Combat Stats.
(1) # = 28.
(2) Avg Ex[PWIN]=0.943
(3) Avg WIN=0.929
(4) So, actual results are (again) a bit lower than expected but by only 1.4% (0.014) for blitz.
(5) Fractional roll in 22 of the 28 blitzs. Ex=499.5, Avg=555, Range 34 - 995.
(6) 2D10 roll x 28. Ex=11, Avg=11.1, Range 2 - 20.
Statistics are fun! Also the dice that I'm using seem to be "fair" by producing near the expected.
- IMG_20230728_162151540.jpg (232.6 KiB) Viewed 1324 times
Ronnie
Re: Excel Based AAR Template.
CP -> Tab #4 & BOA -> Tab #5.
CP -> CP lost & aborted tracker/convertor -> Tab #4.
(1) Columns C-P, rows 3-38 -> enter CPs sunk (lost) and aborted for each major power and their associated aligned minors.
(2) Tables in columns S-AG, Rows 1-40 are automatically generated from the data entered in (1).
(3) 200,000 tons per CP is used to convert the number of CPs lost or aborted into equivalent tonnage in millions of tons.
BOA -> Tab #5.
(1) Everything (tables & plots) in this tab are automatically generated. This tab is linked to the CP tab #4.
(2) BOA (blue) = historical allied losses by year (top plot) and cumulative losses by year (bottom) plot in GMT = gross million of tons.
(3) Game sunk (green) = equivalent tonnage (gross million of tons) lost in the game.
(4) Game Aborted (grey) = equivalent tonnage (gross million of tons) aborted in the game.
CP -> CP lost & aborted tracker/convertor -> Tab #4.
(1) Columns C-P, rows 3-38 -> enter CPs sunk (lost) and aborted for each major power and their associated aligned minors.
(2) Tables in columns S-AG, Rows 1-40 are automatically generated from the data entered in (1).
(3) 200,000 tons per CP is used to convert the number of CPs lost or aborted into equivalent tonnage in millions of tons.
- Tab-04-CP.png (195.49 KiB) Viewed 1292 times
(1) Everything (tables & plots) in this tab are automatically generated. This tab is linked to the CP tab #4.
(2) BOA (blue) = historical allied losses by year (top plot) and cumulative losses by year (bottom) plot in GMT = gross million of tons.
(3) Game sunk (green) = equivalent tonnage (gross million of tons) lost in the game.
(4) Game Aborted (grey) = equivalent tonnage (gross million of tons) aborted in the game.
- Tab-05-BOA.png (141.28 KiB) Viewed 1292 times
Ronnie
Re: Excel Based AAR Template.
HX -> Tab #5
Columns A -E, G-K & N-R.
User input cells:
(1) Location - hex or sea area location description.
(2) row - MWIF hex coordinate row #.
(3) col - MWIF hex coordinate column #.
EXCEL Calculated Cells
(4) hc - MWIF encoded hex location (unwrapped).
(5) hc` - MWIF encoded hex location (wrapped).
(a) if hc > 65536 -> hc` = hc - 65536
(b) else, hc` = hc
Note: hc` blank if hc <= 65536 or is blank
hc is blank if both corresponding row & col cells are blank.
Columns A -E, G-K & N-R.
User input cells:
(1) Location - hex or sea area location description.
(2) row - MWIF hex coordinate row #.
(3) col - MWIF hex coordinate column #.
EXCEL Calculated Cells
(4) hc - MWIF encoded hex location (unwrapped).
(5) hc` - MWIF encoded hex location (wrapped).
(a) if hc > 65536 -> hc` = hc - 65536
(b) else, hc` = hc
Note: hc` blank if hc <= 65536 or is blank
hc is blank if both corresponding row & col cells are blank.
- Tab-05-HX.png (157.47 KiB) Viewed 1189 times
Ronnie
Re: Excel Based AAR Template.
GLS -> Tab #6 Game Log Turn & To-Date Summaries.
User input -> cell G1 (yellow) -> turn # (0-38).
Top Table -> game log summary for turn specified by theater.
# Logs = total number of logs entered for that theater & turn.
# NC = number of naval combats for that theater & turn.
# AC = number of air combats for that theater & turn.
# LC = number of land combats for that theater & turn.
# TC = total number of naval, air & land combats for that theater & turn.
Bottom Table.
Cumulative summary by theater for all turns logged to date.
NOTE: not affected by user input cell G1 (yellow).
Summaries derived from user war logs (L -> Tab-02) and user inputs into columns:
(B) turn #
(G) theater
(J) CBT TY
User input -> cell G1 (yellow) -> turn # (0-38).
Top Table -> game log summary for turn specified by theater.
# Logs = total number of logs entered for that theater & turn.
# NC = number of naval combats for that theater & turn.
# AC = number of air combats for that theater & turn.
# LC = number of land combats for that theater & turn.
# TC = total number of naval, air & land combats for that theater & turn.
Bottom Table.
Cumulative summary by theater for all turns logged to date.
NOTE: not affected by user input cell G1 (yellow).
- Tab-06-GLS.png (124.99 KiB) Viewed 1184 times
(B) turn #
(G) theater
(J) CBT TY
- Tab-06-GLS-from-Tab-02-L.png (278.29 KiB) Viewed 1184 times
Ronnie
Re: Excel Based AAR Template.
WAR -> Tab #7. WAR Directives Summary.
User input -> cell M1 (yellow) -> turn # (0-38).
War directive summary by Major Power (MP), to-date and Turn Activity for user specified turn #.
To-date summary & Turn Activity derived from war logs (L -> Tab-02) and user inputs into columns:
NOTE: L (Tab-02) variable header (cell U1) =32 (War Directive) shown.
(H) Att MP = major power against which the war directive is issued.
(K) WAR = column searched for war directives (key=WAR). (NOTE. CBT STY1 default header; i.e., if U1 is empty or =0).
(L) User specified WAR directive sequence #. NOTE: This sequence number if NOT used for the summary counts and may be in whatever form the user wishes to use.
(S) WAR directive status (enumeration). See column T, row 1 for definition.
LGR (Tab-03) Report Summary.
Excel filtering may be used to extract the global log numbers (Col A), which then may be pasted into the LGR (tab-03), column A to produce a user desired report. Note that the user specified (cell u1) the war directives header for this report. The example below is for all Japanese War directives whether open or closed issue to-date. The user could have further filtered on enum status if they so have desired in tab-02 (L) is they so have desired to have produced (for example) a report on the Japanese WAR directives still open.
User input -> cell M1 (yellow) -> turn # (0-38).
War directive summary by Major Power (MP), to-date and Turn Activity for user specified turn #.
- Tab-07-WAR.png (127.51 KiB) Viewed 1180 times
NOTE: L (Tab-02) variable header (cell U1) =32 (War Directive) shown.
(H) Att MP = major power against which the war directive is issued.
(K) WAR = column searched for war directives (key=WAR). (NOTE. CBT STY1 default header; i.e., if U1 is empty or =0).
(L) User specified WAR directive sequence #. NOTE: This sequence number if NOT used for the summary counts and may be in whatever form the user wishes to use.
(S) WAR directive status (enumeration). See column T, row 1 for definition.
- Tab-07-WAR-from-Tab-02-L.png (248.6 KiB) Viewed 1180 times
Excel filtering may be used to extract the global log numbers (Col A), which then may be pasted into the LGR (tab-03), column A to produce a user desired report. Note that the user specified (cell u1) the war directives header for this report. The example below is for all Japanese War directives whether open or closed issue to-date. The user could have further filtered on enum status if they so have desired in tab-02 (L) is they so have desired to have produced (for example) a report on the Japanese WAR directives still open.
- Tab-07-WAR-Summary-Using-Tab-03-LRG.png (414.73 KiB) Viewed 1180 times
Ronnie