Handy Excel-based Scenario analyzer (using data from ROAR)

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Hello all,

Just wanted to share a spreadsheet that I created for myself and have been using for a while. It displays scenarios by publication and, using data from ROAR, shows both rating and balance along with basic info such as which nationalities are involved and how long the scenario will take in hours to play (estimated). Also included are tabs that can track your played games as well as a list that one can create easily that shows your "want to play" list. See screen shot below.

Let me know if you like it and if you have any ideas to improve. Enjoy.

Ken

1528064976794.png
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Last edited:

Eagle4ty

Forum Guru
Joined
Nov 7, 2007
Messages
6,913
Reaction score
5,094
Location
Eau Claire, Wi
Country
llUnited States
Oops-- the file was too large for GameSquad upload. Also, I noticed that there is a specific folder for all downloads.

So, I have added a link to the zipped Spreadsheet, which I uploaded to a file sharing site, in the ASL Resources area here:
http://www.gamesquad.com/forums/index.php?resources/handy-excel-based-scenario-analyzer-using-data-from-roar.39/

Ken
Did the same thing. Too bad so many people enter the results for newer (e.g. updated) scenarios using the older Scenario entries, it really makes many of the scenarios reprinted in Yanks-2 or DB-3 or AoO almost meaningless. I noticed this when I saw an awful lot of the older iterations of these scenarios having been played recently, but very few of the updated ones. I can't really believe such a large number of people would be only playing the older ones and foregoing the newer updated titles.

Nice & somewhat helpful idea BTW.:thumbsup:
 

lionelc62

Senior Member
Joined
Nov 16, 2007
Messages
410
Reaction score
452
Location
Northern France
First name
Lionel
Country
llFrance
Great file thanks !
Two questions :
- The ROAR balance has 5000 + lines but I can see only 1500 scenarios in the 'summary' tab ?
- How is it possible to expand the 'creating your own custom scnario list table ?

Regards
Lionel
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Great file thanks !
Two questions :
- The ROAR balance has 5000 + lines but I can see only 1500 scenarios in the 'summary' tab ?
- How is it possible to expand the 'creating your own custom scnario list table ?

Regards
Lionel
Lionel,

Yeah, the spreadsheet only has the scenarios that I selected-- they are both the scenarios that I have in my possession as well as mostly the "most played" out there. The reason it is not all 5000+ scenarios is that I manually added information for each scenario (PTO, Night, Air, Bocage, # of turns, as well as # of units, AFVs, etc in order to estimate time to play).

Now, if folks would value a single long list of all the scenarios without the information I listed above-- just the ROAR balance, rating and # of wins for each side, I can do that easily. In fact, I think it's a good idea and will do it for the next version.

Expanding the custom table on the sheet entitled, "Setup" is fairly easy for anyone skilled with Excel. But making the "Yes"/"No" option turn on or off the listing is a bit more complicated. If anyone would like me to add a module or few, let me know and I will add. If you want all the modules in ROAR to be added, that would be too much plus the table would be quite lengthy. Happy to modify- just let me know what you want.

Ken
 

lionelc62

Senior Member
Joined
Nov 16, 2007
Messages
410
Reaction score
452
Location
Northern France
First name
Lionel
Country
llFrance
Lionel,

Yeah, the spreadsheet only has the scenarios that I selected-- they are both the scenarios that I have in my possession as well as mostly the "most played" out there. The reason it is not all 5000+ scenarios is that I manually added information for each scenario (PTO, Night, Air, Bocage, # of turns, as well as # of units, AFVs, etc in order to estimate time to play).

...

Ken
I understand. Thanks for this file !

Lionel
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Again, the link is obsolete. Unfortunately, the file (1.5Meg) is too large for this site.

If you want this spreadsheet, PM me and include your personal email.

EDIT: I was able to compress/zip the file enough to get it down to size. Download is lower in this thread.
 
Last edited:

jrv

Forum Guru
Joined
May 25, 2005
Messages
21,998
Reaction score
6,206
Location
Teutoburger Wald
Country
llIceland
Again, the link is obsolete. Unfortunately, the file (1.5Meg) is too large for this site.

If you want this spreadsheet, PM me and include your personal email.
Have you tried zipping the file to make it smaller?

JR
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
SUCCESS!

I was able to shrink the spreadsheet down to a small enough size to fit into GameSquad's file limit. Yeah!

I removed nearly all of the ROAR data, leaving the two data sheets nearly empty. What this means is that anyone who downloads the attachment below will need to visit ROAR at http://www.jrvdev.com/ROAR/VER1/default.asp and follow the instructions that I wrote on the first sheet. It's simple and should take you only a couple minutes. Once all the data is cut-pasted in, you will see all the useful analysis and be able to use the workbook as designed.

Enjoy!

If any questions or problems, ask.

Ken
 

Attachments

Kijug

Senior Member
Joined
May 30, 2008
Messages
418
Reaction score
390
Location
Texas
First name
Matt
Country
llUnited States
I copy/pasted into cell B1 but after three seconds I got the msg, "ALERT: We can't do that to a merged cell." I have MS Excel for Mac, but latest version 16.26.

I'm guessing your macro is doing some copy/pasting somewhere? And just to be sure...I "Select All" on the web page which includes all the header text, etc., as well as the scenario data...correct?
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Kijug,
Try again. Perhaps you selected the wrong cell? I am not familiar with Excel for Mac, but I am not aware of any issues as you mentioned.

There are no macros involved with this spreadsheet. It's a simple copy and paste. And, yes, you "Select All" on the web page, which will grab all the header text, etc. The spreadsheet accounts for this.

Try again and let me know how it goes. My wife has a Mac and I can fire it up and see if I have the same problems, if needed. Can debug then.

Ken
 
Last edited:

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Just for clarification, this is what the top of the "ROAR Ratings" sheet should look like after you "Select All", then copy and paste into cell B1 (using ROAR data from yesterday):

9649
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
And this is what the ""ROAR Balance" sheet should look like after the cut-and-paste from ROAR:

9650
 

Kijug

Senior Member
Joined
May 30, 2008
Messages
418
Reaction score
390
Location
Texas
First name
Matt
Country
llUnited States
Well, it seems to be working...thanks! I did get the merge error again...but then I did it all again and it worked fine.

Looks great..good job!
 

PrinceTuiTeka

Recruit
Joined
Jul 20, 2018
Messages
29
Reaction score
15
Country
llNew Zealand
Hi Ken,

I cant seem to get the Ratings tab entries to show in the summary tab. Is there anything obvious I may be missing?
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
Hi Ken,

I cant seem to get the Ratings tab entries to show in the summary tab. Is there anything obvious I may be missing?
Prince,

First off, I will load a new version of the spreadsheet as soon as I post this message. I tried to cut-and-paste the data from ROAR into my spreadsheet and ran into problems. Not sure, but maybe ROAR slightly changed their format? Anyhow, I updated the spreadsheet and now it works. So, download the latest version and try again. Some things to keep in mind:

It is important to first select ALL of the text on the ROAR page (http://www.jrvdev.com/ROAR/VER1/Recommnd.asp for the ROAR Ratings tab and http://www.jrvdev.com/ROAR/VER1/RecordByPub.asp for the ROAR Balance tab). Use CNTRL-A to grab everything.

Once "Select ALL" is clicked, then copy it to the clipboard (CNTRL-C).

Then, on the ROAR tab in the Excel spreadsheet, manually select cell B1. Note that it works best if you click on cell B2, then use arrow keys to "walk" the highlighted cell to B1. (If you click on B1, it will probably activate the hyperlink ("Home/More Functions") that is already there...

Once you are able to select cell B2, then paste (CNTRL-V) the ROAR data. It should look just like the image below. If the first scenario name on this list is NOT in cell C16, you've done something incorrectly and the Summary tab will not work.

Once both tabs have the updated ROAR data in, the Summary sheet should show everything. If it doesn't, screen shot both the ROAR Ratings sheet and the ROAR Balance sheet and contact me.

10341
 

KenH

Member
Joined
Feb 24, 2004
Messages
66
Reaction score
65
Location
Houston, TX
Country
llUnited States
I just uploaded zipped Version 2.1.

Try using this and let me know if it works! Good luck.

Ken
 
Top