MechWarrior: Living Legends

Community Activities => Community Involvement => Topic started by: Snyp3r on May 12, 2011, 11:50:03 PM

Title: Drop Calculator on Google Docs - Final Edition
Post by: Snyp3r on May 12, 2011, 11:50:03 PM
thEClaw and Az are continuing to update the calculator. The new thread can be found here: http://forum.mechlivinglegends.net/index.php/topic,17371.msg302022.html

FINAL UPDATE: With Diablo 3, Mechwarrior: Online, moving to a new apartment and going back to school all coming up, I can't see myself keeping this updated anymore. I've attached a .zip to this post containing the master .xlsx file I used, along with a few pages of documentation I wrote up to try to explain the functions and formulas. Feel free to modify it and use it in your own project if you want to.

VictorMorson came up with the idea of having a drop calculator spreadsheet on Google Documents. The reasoning was that it would be an easy way to save drop lists and share/edit them with the entire unit. So with that in mind, we set about making one which you can find right here:

0.5.5b calculators (https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdERQQ3FQaWJWd3dFT0R1djd5LXJkZEE)
0.5.5b reference sheets (https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdGtldHMxOXlLSWNWWkVvNjZ1c3lPdmc)
The Excel 2007 versions of the spreadsheets are also available in the file attached to this post.

All data is now read via script directly from the game files. Everything should hopefully be accurate now, but let me know if you spot any errors.

5/12/2011 - v0.1: Initial version for 0.4.9.
5/18/2011 - v0.2: Added ammo calculators.
5/19/2011 - v0.2.1: Added "Full (Mini)" calculator with tiny fonts and full features (great for netbooks!)
5/30/2011 - v0.3: Updated all information for MWLL v0.5.1, various changes to calculator formatting.
7/1/2011 - v0.3.1: Fixed Thor A loadout data missing CDSRM6.
7/19/2011 - v0.4.0: Added tonnage calculators, added equipment lookups to calculators, improved formatting on all sheets.
1/26/2012 - v0.5.4 BETA: Overhauled and updated for MWLL 0.5.4. Thanks Az, thEClaw! Following MWLL version numbers now.
1/30/2012 - v0.5.4c: Advanced version number to non-beta.
2/8/2012 - v0.5.5a: Updated for MWLL 0.5.5.
2/8/2012 - v0.5.5b: Fixed small reference error in Excel spreadsheets. Google Docs sheets were not affected.
3/17/2012 - v0.5.5 FINAL: Identical to v0.5.5b. The master spreadsheet with all sheets included is now available in a .zip attachment, along with documentation about how it works.
Title: Re: Drop Calculator on Google Docs
Post by: Cygma on May 13, 2011, 12:00:03 AM
Always good to have alternatives - just FYI, FCL has a drop deck for which you can send links to your setups around and includes ammo and BA:
http://www.fcleagues.com/fcmwll/dropdeck.php
Title: Re: Drop Calculator on Google Docs
Post by: Snyp3r on May 13, 2011, 12:09:32 AM
Nice! I don't think any of us knew that existed, actually. Google Docs is still nice for some things though, especially since you can have multiple people editing it in real time.
Title: Re: Drop Calculator on Google Docs
Post by: GB-72AT Talon on May 13, 2011, 01:54:39 AM
very nice!

Title: Re: Drop Calculator on Google Docs
Post by: VictorMorson on May 13, 2011, 06:40:59 AM
Always good to have alternatives - just FYI, FCL has a drop deck for which you can send links to your setups around and includes ammo and BA:
http://www.fcleagues.com/fcmwll/dropdeck.php

The main advantages to the Google Doc one if anyone wants to use it:


The FCL one sounds pretty neat.  The main reason we were interested in doing this one, however, was specifically so you could do C-Bill drops in practice games, without a huge amount of headache on the part of the DC and without leaving the game window. 

I hope it helps some of you out there!

As i said, always good to have alternatives like yours :)
I was just pointing out the FCL one in case people hadn't noticed it.

Oh sure thing!  Glad to have it linked in the thread.  I was just listing reasons one might use one over the other, given the situation.  The one we put up was designed with the thought of practices in mind - the FCL one works great for other situations too.  Very useful to have!
Title: Re: Drop Calculator on Google Docs
Post by: Cygma on May 13, 2011, 06:52:34 AM
As i said, always good to have alternatives like yours :)
I was just pointing out the FCL one in case people hadn't noticed it.
Title: Re: Drop Calculator on Google Docs
Post by: ~SJ~ Lobo on May 13, 2011, 12:50:27 PM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.
Title: Re: Drop Calculator on Google Docs
Post by: SquareSphere on May 13, 2011, 04:30:12 PM
Last time i check FCL was out of date, Could you add ammo cost to yours well?
Title: Re: Drop Calculator on Google Docs
Post by: ~SJ~ Lobo on May 13, 2011, 04:38:33 PM
Last time i check FCL was out of date, Could you add ammo cost to yours well?

Maus uploaded one you can find here, that has ammo costs

https://spreadsheets.google.com/ccc?key=0Ap2gzwgmMVPQdC13MWtCaWwwZlBodzREWGNmVjBaUWc&hl=en_GB#gid=1
Title: Re: Drop Calculator on Google Docs
Post by: Snyp3r on May 13, 2011, 05:12:39 PM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.
You can make your copy of the sheet private and choose to only share it with specific people.
Title: Re: Drop Calculator on Google Docs
Post by: ~SJ~ Lobo on May 13, 2011, 05:16:18 PM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.
You can make your copy of the sheet private and choose to only share it with specific people.

Basically it would be best just use the link as a place for you to update it and everyone else to save local copies on their own PCs. That's really the only way since google does not open a new instance of the document for each user, and if you are making a list, I have to wait until your done, etc.
Title: Re: Drop Calculator on Google Docs
Post by: VictorMorson on May 14, 2011, 12:27:48 AM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.

The sheet is View Only.  What you want to do is Make A Copy of the view only doc, then edit that one - that way you can edit it personally or share it to selected Drop Commanders, as that option will make a 2nd copy in your personal Google Docs. :)  Without that option it'd definitely be a problem!
Title: Re: Drop Calculator on Google Docs
Post by: =Outlaw= on May 14, 2011, 05:03:15 AM
Very nice. I wish we had this back in the day. Having DCs discuss and figure out a good dropdec with mechs availabe..and still do it in time for the next drop was always a challenge. This streamlines it considerably
Title: Re: Drop Calculator on Google Docs
Post by: ~SJ~ Lobo on May 14, 2011, 05:53:52 AM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.

The sheet is View Only.  What you want to do is Make A Copy of the view only doc, then edit that one - that way you can edit it personally or share it to selected Drop Commanders, as that option will make a 2nd copy in your personal Google Docs. :)  Without that option it'd definitely be a problem!

Indeed I was really excited to make ours online and viewable to the community until I realized that simple failure of google docs.
Title: Re: Drop Calculator on Google Docs
Post by: VictorMorson on May 14, 2011, 06:16:35 AM
We already tried this, the problem with a public sheet on google docs is: multiple people editing it at once. I can hop in and completely fapp up what you are doing. Not to mention do you really want random people screwing up the formulas and constantly forcing you to reload the sheet? Much safer to download it.

The sheet is View Only.  What you want to do is Make A Copy of the view only doc, then edit that one - that way you can edit it personally or share it to selected Drop Commanders, as that option will make a 2nd copy in your personal Google Docs. :)  Without that option it'd definitely be a problem!

Indeed I was really excited to make ours online and viewable to the community until I realized that simple failure of google docs.

If you use the Make a Copy feature, not the Save feature, it saves it not to your own PC but rather your own Google Docs account.  This allows you to copy the master sheet, make changes and immediately share it with all other Drop Commanders or indeed, the public at large really easily.  It's a far better idea than saving it to your local PC for that very reason.
Title: Re: Drop Calculator on Google Docs
Post by: Snyp3r on May 19, 2011, 12:02:23 AM
Updated. Added ammo calculators, some other miscellaneous features, and a more detailed instructions page. All calculators are sized for 16 players now (easily changed, if you're so inclined), and there's Compact and Simple versions with some of the features stripped for the sake of space.

https://spreadsheets.google.com/ccc?hl=en&key=tCnycYMC5OrxZQqqXRUC2UQ
Title: Re: Drop Calculator on Google Docs - Now with ammo!
Post by: VictorMorson on May 19, 2011, 03:10:42 AM
One last update on how to make the calculator easier to use for drops.

You can easily copy sheets. 

i.e. once you have your own personal copy of the Calculator saved to your google docs, you can simply right click any sheets (such as the 16 player drop), name it anything you want (i.e. "Tuesday's Drops") and then edit it indefinitely, or even delete it, without impacting the original sheet. 

We plan on updating the sheet when the next build comes out, but it was built so that anybody could edit it if necessary, to remove fears of the calculator becoming abandonware, or simply not updated like some similarly featured calculators available right now are.

EDIT:  Major props to Snyper for this, by the way.  I had planned to make the google doc version of this myself, on a very simple "Chassis Columns, Variants Rows, put the # in you want of each" method, and he took the whole concept and created something far, far more well polished than I ever even remotely planned out of it.
Title: Re: Drop Calculator on Google Docs - Now with ammo!
Post by: Snyp3r on May 29, 2011, 06:47:38 PM
I'll be getting this updated for 0.5 as soon as I can, probably during Monday/Tuesday, just been too busy this weekend to do it.
Title: Re: Drop Calculator on Google Docs - MWLL 0.5 edition
Post by: Snyp3r on May 31, 2011, 04:50:17 AM
Updated for 0.5.1! New version can be found here: https://spreadsheets.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdHlqZzlzaWlZRUx5ek4zYnRCTHlZTEE
Title: Re: Drop Calculator on Google Docs - MWLL 0.5 edition
Post by: SquareSphere on May 31, 2011, 05:10:51 AM
Hmmm I thought clan ammo cost were different from IS, am I just imagining that?
Title: Re: Drop Calculator on Google Docs - MWLL 0.5 edition
Post by: Snyp3r on May 31, 2011, 05:15:38 AM
Yeah. All the ammo prices there are from the price dump console command added in 0.5, but I don't think they've changed from 0.4.9 where Clan/IS prices were still the same. I included the ATM-E prices since they're in the dump, even though, as far as I can tell, they're not used in-game yet. If anything looks suspicious though, feel free to look it up and let us know if it's wrong, there's a good chance we made some errors somewhere along the way.
Title: Re: Drop Calculator on Google Docs - MWLL 0.5 edition
Post by: Snyp3r on July 09, 2011, 09:47:30 PM
Giving this a bump, I haven't abandoned it, still trying to keep it updated and fixed!
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on July 20, 2011, 07:58:03 AM
Updated to version 0.4.0.: https://spreadsheets.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdHlTazVDa2tOZXA3THNsaDNuOWladGc

Added tonnage calculators and some simple inventory tracking features we thought we might end up needing for the league. Also added equipment information to the calculators alongside weapon information, and reformatted all sheet fonts to be smaller and easier to read on lower-resolution screens (and probably high-res ones, too). The mini calculator is more mini than ever before, and should fit more comfortably on tiny netbook screens. And because both Fred and Morson were making the mistake, "Bushwhacker" is now an acceptable typo, and will still be recognized by the calculator.

I also provided both 8 and 16 player versions of each calculator by default. This clutters things up a little bit, but you are of course free to delete (or simply hide) any sheets except the Inner Sphere, Clan, Ammo & BA, and DataTables sheets (which are required for functionality).

As always, please report bugs, errors, or feature requests in this thread or via PM.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: =KoS= Tripod on July 20, 2011, 01:54:01 PM
Cool

I never DL'd the other one cuz you had to jump through a bunch of hoops and shit
Will give this one a try when I'm back home 8)
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: VictorMorson on July 23, 2011, 04:23:05 AM
Cool

I never DL'd the other one cuz you had to jump through a bunch of hoops and shit
Will give this one a try when I'm back home 8)

It should be pretty simple to get going.  Just head to the sheet, then go to File and make a copy, which will instantly make a sheet for you in your own Google docs folder.  Then you're good to go.  It sure helps me get drops going much, much faster than I'd ever be able to by hand, or even with some of the other calculators.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Priest on September 03, 2011, 10:15:09 AM
I'm going to check this out; looks like some work went into it, and never hurts to have a cool calculator sheet.  Always looking for new things; got to love google docs.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on November 20, 2011, 04:05:06 PM
Is this still alive?

I downloaded the spreadsheet, made some changes to make it compatible with OpenOffice and wanted to update the asset-information. But I can't seem to find out where the calculator gets its information about the kinds of ammunition that can be bought. Data of the Thanatos A is updated so it doesn't carry an HGauss anymore, but the calculator still wants to buy HGauss-ammo. Any idea how to fix that?

The spreadsheet only seems to work in Microsoft Excel 2007 or newer, took me quite a while to find out that every other version/clone of Excel is missing a function.

EDIT: I found the hidden sheet that contains the data for buyable ammunition. Didn't even know sheets could be hidden :P.
Aside from double checking the free tons and the amount of heatsinks I updated everything for the newest MWLL version. If I ever get around to complete the work, I will provide the spreadsheet here. (I changed the behavior of the asset-lists a little bit to make it more comfortable to update. But aside from that I merely updated the data and improved compatibility with older Excel versions as well as OpenOffice. Hopefully that is something the author(s) can agree with.)
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on December 16, 2011, 04:18:51 AM
Hey there. Yeah, I'm going to update this once 0.5.4 rolls around. The only documented change I remember for 0.5.2/0.5.3 was a Raven loadout, so I didn't bother to check anything else against the actual game. Didn't know the Thanatos and such got changed (I've been playing Battlefield).
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on December 16, 2011, 09:22:54 AM
Here a list of things I changed:

The backwards compatibility was achieved by replacing every call to IFERROR (seemingly only available in Office 2007 and newer) by a proper IF-function.

IFERROR(VLOOKUP(SUBSTITUTE(B6;" ";"");TTable;2;FALSE());"")
became
IF(ISERROR(VLOOKUP(SUBSTITUTE(B6;" ";"");TTable;2;0));"";VLOOKUP(SUBSTITUTE(B6;" ";"");TTable;2;0))

This way everything works fine in older versions of Office as well as OpenOffice.

I also updated all the data, except the following:
Sheet „Inner Sphere“: free tons, heatsinks
Sheet „Clan“: free tons, heatsinks
Sheet „Ammo_BA“: columns A-D (has to be synchronized with the hidden sheet)

In addition I tried to automate a portion of the vehicle-lists. There is a hidden column now (Q for me) containing information about the equipment of a 'Mech, read out of the game data by a Python script available somewhere here in the forum. I wanted to automate the rest, too, but I didn't have the time back then.

A nice thing to add would be the ability to handle data like "2xClan_ER_Medium_Beam_Laser; 1xClan_DualStreakSRM4; " to set the weapons available for a vehicle. Again, that data comes directly from the game files and an update to a newer version of MWLL would only take a couple of minutes with this.
Another nice thing would be a combobox (a popup list or whatever else it may be called) wherever one can enter a vehicle name. It is not always clear how to spell the names of vehicles correctly.

Thanks a lot for creating this file, Snyp3r. It looks a little bit complicated to use at first but is really a nice and handy tool. I hope you are able to use my changes somehow.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on December 17, 2011, 06:29:32 AM
Awesome! Thanks for sharing dude, I'll get all the updates merged over to Google Docs sometime within the next week for sure. Once that's done, I'll post up the original Excel document too for folks who want to use it in an external spreadsheet application, since Google Docs loses some things in translation when you import/export.

A nice thing to add would be the ability to handle data like "2xClan_ER_Medium_Beam_Laser; 1xClan_DualStreakSRM4; " to set the weapons available for a vehicle. Again, that data comes directly from the game files and an update to a newer version of MWLL would only take a couple of minutes with this.
Yeah, something like this would be nice, but I have no idea what would be a good way to handle it. If I had knowledge of Python I might be able to modify the script you're talking about (although I didn't even know it existed, actually) to convert it to a more user-friendly format, but I'm not sure how else to do it.

In any case, just having a tool like that to extract the mech info from the game would make this trivially easy for me to keep updated, even after big patches. Going through the document and making the changes doesn't take me more than an hour or two, the really time consuming part was painstakingly going through the in-game buy menu and copying down the data. :P

Quote
Another nice thing would be a combobox (a popup list or whatever else it may be called) wherever one can enter a vehicle name. It is not always clear how to spell the names of vehicles correctly.
This is definitely possible, and I toyed with it in the past, but I ended up ditching it because dropdown lists don't work with Google Docs. Another problem I had with it was that the list got to be pretty long with all the vehicles that are in the game and could be pretty unwieldy. I messed around with adding drop down menus for Clan/IS selection and weight class selection too to narrow it down better, but after that it was just so much slower than typing it in manually.

If I could easily add some type of auto-complete where it would drop down a list of suggestions based on what you entered, I totally would do that, but I don't know of any way to do that.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on December 17, 2011, 08:26:02 AM
Yeah, something like this would be nice, but I have no idea what would be a good way to handle it. If I had knowledge of Python I might be able to modify the script you're talking about (although I didn't even know it existed, actually) to convert it to a more user-friendly format, but I'm not sure how else to do it.
It might even work by just changing the drop calculator in a way that enables it to use the file generated by the script.

In any case, just having a tool like that to extract the mech info from the game would make this trivially easy for me to keep updated, even after big patches. Going through the document and making the changes doesn't take me more than an hour or two, the really time consuming part was painstakingly going through the in-game buy menu and copying down the data. :P
Sadly the information in the buy-menu is not guaranteed to be correct. There are a couple of mistakes, the only sure way to get your information would be to actually buy every vehicle in the game. I did that for my little weapon group editor and it was a huge pain in the ass.

If I could easily add some type of auto-complete where it would drop down a list of suggestions based on what you entered, I totally would do that, but I don't know of any way to do that.
I thought about it the same way you did. A shortened dropdown-list in combination with an auto-completion-system would be nice. I'll take a look into the documentation, maybe there is a way to do it.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on December 17, 2011, 08:50:50 AM
The documentation for OpenOffice did not help. There was mention of a function to complete words but somehow that function was missing for me. ???

I made a little change, it might be somewhat ugly: I just added all the names of all the vehicles in a couple of hidden rows below the "chassis"-fields. That way the names get completed. They are contained in rows 100 to 134. If I come up with something prettier, I'll change that again. But until then this does the job quite efficiently.

PS: Words for auto-completion are only looked up within 2000 rows of the current cell, if the documentation tells the truth. So the data can't be put too far away.

[attachment deleted by admin]
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on December 21, 2011, 01:27:49 AM
I see 0.5.4 is out now, good timing I guess. Do you have a link handy to that Python script you mentioned for pulling data out of the game files? Or if you wanted to pull it yourself and post it, that would work too.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on December 21, 2011, 08:47:25 AM
I see 0.5.4 is out now, good timing I guess. Do you have a link handy to that Python script you mentioned for pulling data out of the game files? Or if you wanted to pull it yourself and post it, that would work too.
I looked it up again. From now on the link can be found in my signature. I already ran the script and will attach the vehicle-part. But since it was made for 0.5.3 I myself cannot guarantee correct data.

I already did a good amount of work in making your droplist easier to maintain, but because of that work it is not fully functional yet. I think it would be best to find a different way to display the vehicle-list-tabs (you know what I mean :P). The current format is not easy to automate.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on December 21, 2011, 09:32:42 PM
Thanks man, this helps a lot. I think I have a way to almost automate the process. Using Macros in Excel, I can convert the formatting of weapon names and mech names and such quite easily. Now I'm going to try to set up the asset spreadsheets I already have (I like the formatting I have going there) to fetch the mech data from the converted mech info (and hopefully that many VLOOKUP commands doesn't make it perform like trash). Hopefully I can make it all update with a few clicks of a mouse. Not fully automated, but pretty close. Not sure if the macros will work in OpenOffice though...
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 14, 2012, 09:49:19 AM
How is it going? I know somebody who is in real need of an up-to-date calculator. But I am hesitating to work on it myself since I expect to see a new version around here and I don't want my time to be wasted.

It would be nice to get information like vehicle prizes without having to check the game itself. If any of the script-gurus is reading this, he (or she) might know about this.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 15, 2012, 02:35:00 AM
It would be nice to get information like vehicle prizes without having to check the game itself.

It would be so nice the devs included a command for it in 0.5.0... which broke in 0.5.2 :'(. The bug report is here (http://forum.mechlivinglegends.net/index.php/topic,15845.0.html) if you want to bump it.

I've got a script that reports asset prices since 0.4.9 and it works well, however pretty much each MWLL updates exposed some flaw in error handling or wrong assumptions about the xml format that ended up confusing my script for one asset/variant or two. I can't be certain of its accuracy for each new version since I can't double check against dumpMwllPrices' results anymore.


I could dump the prices for 0.5.4 if you need it, it's still much easier and faster to double-check with the game rather than writing everything down then double-checking.

However the real fix would be to get that command's functionality restored.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 15, 2012, 05:39:19 AM
How is it going? I know somebody who is in real need of an up-to-date calculator. But I am hesitating to work on it myself since I expect to see a new version around here and I don't want my time to be wasted.

It would be nice to get information like vehicle prizes without having to check the game itself. If any of the script-gurus is reading this, he (or she) might know about this.
Hey, still working on it, just been kind of busy with friends/family over the holidays so I didn't get much done then. I've been trying to figure out a solution for ammo, but since it doesn't look like there's a way to fetch tonnage data from the game directly, it looks like I'll either have to do that manually, or just strip out the functionality all together. Otherwise I think I have everything else figured out, just need to make time to actually type it all out.

Do you know if the mech data on the wiki is current for 0.5.4? It'd be a lot easier for me to get tonnage info from there than trying to get it out of the game myself.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 15, 2012, 05:02:10 PM
I could dump the prices for 0.5.4 if you need it, it's still much easier and faster to double-check with the game rather than writing everything down then double-checking.
Which prices are included? All vehicles, weapons and ammunition? Or just vehicles?

Hey, still working on it, just been kind of busy with friends/family over the holidays so I didn't get much done then.
Nice to hear you're still working on it.

I've been trying to figure out a solution for ammo, but since it doesn't look like there's a way to fetch tonnage data from the game directly, it looks like I'll either have to do that manually, or just strip out the functionality all together.
Manually would be the best option for now, I think. Leave a wy to automate it later on, but the calculation of ammunition prices is extremely important and can't be left out.

Do you know if the mech data on the wiki is current for 0.5.4? It'd be a lot easier for me to get tonnage info from there than trying to get it out of the game myself.
I would not rely on it. You don't have to extract these information on your own. Just use a template value for the time being (20 tons for every asset), upload the calculator and somebody will do the work for you. If a group of four to ten people does this, it won't be that much of a hassle.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 15, 2012, 07:56:53 PM
Which prices are included? All vehicles, weapons and ammunition? Or just vehicles?

The dumpMwllPrices command did all that, neatly categorised in a CSV format. My script focuses on vehicles (and still gets the APC wrong) but prices for BA weapons and ammunition can easily be read from MechLists.lua.


I've been trying to figure out a solution for ammo, but since it doesn't look like there's a way to fetch tonnage data from the game directly, it looks like I'll either have to do that manually, or just strip out the functionality all together.

Asset tonnage is available for mechs only (just look for "tonnage" in the XML). Things like tonnage aren't supposed to change from version to version so I'm not sure you should worry about an outdated wiki :) but in doubt you can check www.sarna.net.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 16, 2012, 01:16:45 AM
Asset tonnage is available for mechs only (just look for "tonnage" in the XML). Things like tonnage aren't supposed to change from version to version so I'm not sure you should worry about an outdated wiki :) but in doubt you can check www.sarna.net.
I guess I should have specified a bit better, I meant free tonnage on mechs/vehicles for ammo.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 19, 2012, 07:50:44 PM
I guess I should have specified a bit better, I meant free tonnage on mechs/vehicles for ammo.

Well, I did suspect you were talking about free ammo tonnage, but I thought you used a script to read the assets' loadouts. And since free tons are listed with the rest of the equipment I found it strange you wouldn't have that information. Asset tonnage, however, is more or less hidden in the middle of the XML, and only available for mechs.


I took some time to post because I had to fix a nasty potential bug in my script, I also took that opportunity to add a dumpMwllPrices-like output you should be able to use directly in a spreadsheet, until that command is fixed.

The attached zip contains:

stdout.txt - my script's output.
It lists weapons, equipment (incl. free tons), total armor and price, for each asset variants.
The only exception is the APC, the XML is different from other assets and for a single, simple variant... it's easier to just fix the output.

stderr.txt - error output (for completeness' sake, I guess)

prices-051.txt - that's the 'prices.txt' generated by the game for version 0.5.1, as a reference.

prices_out_sorted.txt - my script's 'prices.txt' for 0.5.4.
It doesn't include the APC either, you'll have to add the line from prices-051.txt.


The script was accurate for 0.5.1, but I can't vouch for 0.5.4 as I don't have a way to check the prices automatically any more.
If you want to use my output, please double check the prices in-game. If the prices are good, so will be the load-outs. So you can use prices_out_sorted.txt to check, but don't bother with ammo and BA weaponry: I'm pulling those numbers straight from MechLists.lua.
Please tell me if you find any errors!
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 20, 2012, 12:58:27 AM
Aha! Excellent, thanks Az. I was using the data extracted with Hiruma Kai's script that theClaw posted earlier, but yours should work even better, I'll see if I can make use of it. I did a quick random check of a bunch of the prices against the dump I did in 0.5.4 using the "dumpmwllprices" command (or whatever it is), and they were all accurate.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 20, 2012, 03:27:20 AM
Oh gawd, I feel stupid. The fact the command worked for you led me to try it again, and this time I found the output file. But in a totally unexpected place, even though I used it twice before. Brain farts happen... but that one went on for months! I'm embarrassed. I even filed a bug report about it... I did consider the problem might be on my side, but nobody ever confirmed or infirmed it. If anyone has the prices.txt for 0.5.2 and 0.5.3, I'd be glad to have them.

Anyway...

With the correct prices I could check my script, and I found one error:
Morrigu B     Price mismatch error: calculated 118373, read 115873

It turns out I'm finding a Heavy Coolant Tank for that variant (noted simply "Heavy" in the equipment), but the game doesn't seem to account for it. It's strange really, since the parsing looks correct. I'll have to investigate further. In the meantime the price I report for the Scalpel is 2500 C-Bills too high.


I found another error, this time in the dumpMwllPrices itself:
Tracked, IS_PartisanF, 9000
Tracked, IS_PartisanG, 9000


Those variants ought to be commented out from MechLists.lua, as they don't exist in the XML. 9000 is the price of the Partisan's chassis.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 20, 2012, 08:03:24 AM
The profile-folder? Who came up with that? I ran the command several days ago and didn't find anything either. But there it is now.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 20, 2012, 09:14:31 PM
Hey Az, how hard would it be to change the formatting of the output for your script? As it is, I'd have to manually edit the file quite a bit to make it usable in Excel. Hiruma Kai's script had an output that I could import pretty much straight into Excel and go from there. If you could, for example, format it something like this:
Code: [Select]
Blood Asp A; 4 Clan_ER_Medium_Beam_Laser, 2 Clan_ERPPC, 2 Clan_ER_Large_Beam_Laser; EnhancedOptics, 16 DHS; 72000; 162500;It would be a lot easier for me to work with.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 21, 2012, 12:07:54 PM
I started a long reply but I won't have time to finish it, so here's the short one for now:

It should be easy to do, but I might have a better idea. I'm using a special definition of "better" that pretty much equates to "more work for you (and for me)", but we'll see...
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 21, 2012, 09:42:00 PM
Heh, fair enough. As long as the data is delimited somehow so that Excel can separate it into columns for me, it should work.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 22, 2012, 09:51:33 PM
Hey Az, how hard would it be to change the formatting of the output for your script? As it is, I'd have to manually edit the file quite a bit to make it usable in Excel. Hiruma Kai's script had an output that I could import pretty much straight into Excel and go from there.

It could range from really easy to moderately time-consuming. Actually I was going to ask you if you wanted me to tweak my output. Hiruma Kai's script is dedicated to number crunching and DPS calculations, so I guess it didn't make much sense for him to read the number of free tons. Mine was intended to help with the wiki, but by the time I had something roughly working Cygma had stepped in. So I just kept that plain text output, and maintained my script as a personal recreation only.


The format you suggest would be easy to implement, but there is something I'd like you to consider first:

I suggest that you use prices.txt only for the core functionality of your spreadsheet. And my script's output to fill the blanks. That way, you'd be able to build a valid droplist without having to rely entirely on my script.

'prices.txt' is guaranteed to be accurate and you can generate it yourself the moment your PC finishes updating MWLL. The devs implemented the function for your kind of application, after all.

On the other hand, even if I hand my script to you it still trips up on parsing every couple of versions, and in fact I could tell you of two announced features that will break it. I can't guarantee I will be able (or willing) to fix my script in a timely manner. I say "willing" because I only work on it from time to time, when I feel like it. I'm not proficient at coding, it takes me a lot of time to get anything (badly) done. I'm doing it for fun, the challenge of building something, but if I have to work on it when I don't feel like it... then it's no fun.

So I think you should make it so your spreadsheet works even with outdated data. Just in case.



That being said, here are the possible changes I could make to the output.

For a given variant (e.g. Blood Asp A) I can read:
- the asset's (file)name: CL_BloodAsp_Mech
- its display name: Blood Asp
- the variant: A
- its "id": CL_BloodAspA => you can match this one with prices.txt

For the weapons:
I used the names from the XML code (matching the weapon filenames) but I can also print the names as they appear on the HUD. It's neater, but not always shorter... "DualStreakSRM6" becomes "Dual SSRM6" but "Clan_ER_Medium_Beam_Laser" remains "Clan ER Medium Beam Laser".

I couldn't find a way to reliably read shortened names such as "CERMBL" from the data files. I do not want to hard-code those as it has been an important goal for my script from the beginning to avoid hard-coding things as much as possible and keep the maintenance minimal and purely parsing-related. If that's important for you, I probably can manage some sort of compromise.

I think I can link weapons to the ammo they use. I'm not sure how to present that data. Maybe as a separate table, or as a list of usable ammo for each variant.

And I think that's all. Ask if anything else comes to your mind.

With that in mind, you may want to suggest a different template (cosmetic changes as well as different columns). I didn't really know anything about Excel, so I'll leave the exact formatting to you.



I'm sorry for the little rant earlier, but when I saw the price in your proposed format, I really thought you ought to use the value given by dumpMwllPrices rather than exclusively rely on a third party script. But what I felt was "rather than rely on me". It's nice when one's work proves to be useful, but it also comes with the pressure of not letting people down. That's why I avoided releasing my script until now (along with the fact it is mostly an embarrassing piece of code). If an update breaks my code and I don't feel like coding... I don't have to fix it. Now, this might change...
But don't worry, I do intend to maintain it as long as I'm around. It's just that timeliness isn't my forte. Case in point, I intended to finish this post later yesterday...
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 22, 2012, 11:58:30 PM
Using prices.txt would be no problem at all, I can easily do that and that's what I was doing when messing with the output from Hiruma Kai's script.

For the mech names, all I need is a unique name and variant. "CL_BloodAspA" works just as well for my purposes as "Blood Asp A."

Weapons, again, don't really matter. What I was doing with the output from Hiruma Kai's script was writing a macro in Excel to automatically find and replace things like "Clan ER Medium Beam Laser" with "CERMBL", so as far as I'm concerned, "DualStreakSRM6" and "Dual SSRM6" might as well be the same. It's trivially easy for me to do, so go with whatever works best for you. Separating individual weapons with commas would be ideal, but not critical.

Linking weapons to ammo to generate tables for that would be very handy for me, but it's not terribly difficult for me to do manually. If you did want to try it, it would probably work best formatted into a separate table, for example:
Code: [Select]
CL_LokiA;Clan_LRM15;Clan_UAC5;MgunAgain, that exact format isn't important. I can just make a find and replace macro to tailor it to whatever I want as long I can somehow identify the mech, variant, and ammo/weapon type.

There is one other request I would have that could make things a bit easier on me. If you could put a separator character (I just use semicolons for example, but you could use whatever character you want) between the equipment like GECM and JJ and whatnot and the number of heatsinks, and then an additional separator for free tonnage, that would save me a bit of work. So, for example:
Code: [Select]
GECM, MASC;15;2or something like that.

I'm not an expert in Excel, but all that's really important to know for writing an output script I think is that some kind of unique separator character (; in my examples) is needed to separate columns, and each new line is a new row. So for example this:
Code: [Select]
Blood Asp A;4 Clan_ER_Medium_Beam_Laser, 2 Clan_ERPPC, 2 Clan_ER_Large_Beam_Laser;EnhancedOptics, 16 DHS;72000;162500
Blood Asp B;6 Clan_ER_Medium_Beam_Laser, 2 Clan_LBX10, 1 Clan_ATM6;GECM, EnhancedOptics, 5 DHS, 2 FreeTon;72000;132750
becomes this in Excel:
(http://i.imgur.com/H38nd.jpg)
tl;dr: With the above examples in mind, just tell me what type of formatting of the data would be easiest on you, and I can tell you if it'll work for me.

Also, if you don't really feel like working on this, just say so. I can go back to working with Hiruma Kai's script or whatever other scripts come along, I just took an interest in your script because it happens to output all the data my calculator needs in a fairly neat package, and since you formatted it so nicely already, I thought it might be simple to modify it to an Excel-friendly format too.

Thanks for everything, in any case!

Edit: Oh, as far as armor goes, I don't need that. I'd put it in the data spreadsheet if I had it, but it won't be used in the calculators so I don't care about it at all.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 23, 2012, 08:46:01 AM
Hi Snyp3r. Since I tried to build upon your existing drop calculator myself and I don't know how far you are with your work, I will attach my current drop calculator to this post.

It is by far inferior to your old version, but at the moment it would be quite easy to update if a new version of MWLL comes around. Just run Hiruma Kais script and dumpMwllPrices, copy the data, update the APC manually and everything should be fine. The free tons I just entered manually, though - as you know the output of Az' script isn't really suitable of Excel at the moment (but for some reason Hiruma Kais script contains a column for the free tons, without any values though - so that's where I entered them).

So here is my rough draft of a drop calculator. It does work but the ammo-selection isn't as nice as it is in your older version. I did not invest too much work into it, but it can be useful for you in any way.

To everybody else: Don't use the drop calculator attached to this post! I will take it offline once Snyp3r took a look at it.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 23, 2012, 09:37:20 AM
That looks pretty good! I can definitely use a lot of that stuff. The way you dealt with all those extra leftover semicolons in the equipment field is particularly inventive, I was having trouble with that. I do have a couple of questions for you though.

Code: [Select]
=IF(ISERROR(VLOOKUP(LOWER(SUBSTITUTE(B3&C3," ","")),PriceTable,2,0)),0,VLOOKUP(LOWER(SUBSTITUTE(B3&C3," ","")),PriceTable,2,0))In that line, is the LOWER() function really necessary? I removed it out of curiosity, and it still worked fine in Excel. Is OpenOffice case-sensitive with VLOOKUP?

I have to say, I do like how it can accept the raw data from the script, and I think that might be the way to go rather than using macros like I was thinking about doing.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 23, 2012, 10:15:11 AM
That looks pretty good! I can definitely use a lot of that stuff.
Happy to hear that.

Code: [Select]
=IF(ISERROR(VLOOKUP(LOWER(SUBSTITUTE(B3&C3," ","")),PriceTable,2,0)),0,VLOOKUP(LOWER(SUBSTITUTE(B3&C3," ","")),PriceTable,2,0))In that line, is the LOWER() function really necessary? I removed it out of curiosity, and it still worked fine in Excel. Is OpenOffice case-sensitive with VLOOKUP?
I don't think so. But I prefer to make sure this thing will work. It's not that much trouble to go through.

I have to say, I do like how it can accept the raw data from the script, and I think that might be the way to go rather than using macros like I was thinking about doing.
Well, I never used or created a macro - hence I don't know which would be the better solution.

How will you deal with the ammo-types? I couldn't think of an easy to implement way to get it working like your old drop calculator does. I was thinking about filtering out all the non-ammo-based weapons and then somehow splitting the rest and making use of the result. But I was uncertain about how to do the last step. There seem to be different ammo-types for ATMs, so it might be convenient to give the user some kind of choice in the future.

PS: I made room for up to 500 different assets, maybe you noticed. Whatever you do, don't forget about assets that might be added in the future.
PPS: There is a lot of hidden columns since I didn't want the code to get too complicated. I am not really proud of that.
PPPS: What's up with the Novacat F? Have noticed it many times now, but it isn't quite in the game yet.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 23, 2012, 11:40:30 PM
For generating ammo tables, I have a macro to do it in Excel, you might want to try it to see if it works in OpenOffice. Probably not, but it's worth a try.

Unfortunately I don't think there's any good solution to the ammo calculator. Dropdown selections don't work in Google Docs, plus they're kind of slow and unwieldy, and I always considered their absence to be an advantage of this over the web-based calculators out there. The macro here would let me preserve the functionality that I have in the old 0.5.1 version of the calculator, but would prevent the calculator from updating 100% automatically. It might be possible to automate it, but given the kind of sorting that would have to happen, it would probably require an excessive number of functions. I could just let the user type in the type of ammo, like I do with Mech names and variants already, but the ammo system is weird enough that that would be a pretty user-unfriendly solution.

Although if Az is able to sort for ammo weapons and output an ammo table like I mentioned in my post above, this will all be a non-issue.

Fake edit: Well I was going to post an attachment, but it keeps throwing this "upload folder is full" error at me, so I guess that's not happening.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 24, 2012, 12:26:03 AM
Just a short post to give you a glimpse of what I am doing.


Code: (dropammo.txt) [Select]
Weapon;HUD Name;Ammo
AC10;AC:10;AC10ammo
AC2;AC:2;AC2ammo
Clan_ATM12;ATM12;ATMStandard
Clan_ATM15;ATM15;ATMStandard LRM ATMExtended
Clan_ERPPC;Clan ERPPC;
Clan_Gauss;Clan Gauss Rifle;GaussAmmo

Note the bogus "ATM15", a test for weapons with multiple ammos. I plan for the future ^^.


Code: (dropcalc.txt) [Select]
Name;Asset;Variant;ID;Weapons;Equipment;Ammos
Loki Prime;Loki;Prime;CL_LokiPrime;1 Clan_StreakSRM6, 2 Clan_ERPPC, 3 Clan_ER_Medium_Beam_Laser, 2 Mgun;BAP, C3Radar, GECM, EnhancedOptics, 1 AntiMissileSystem, 6 DHS;StreakSRM, Mgunammo
Loki A;Loki;A;CL_LokiA;1 Clan_LRM15, 2 Clan_ER_Large_Beam_Laser, 2 Clan_UAC5, 2 Mgun;BAP, EnhancedOptics, 1 AntiMissileSystem, 1 DHS, 4 FreeTon;AC5ammo, CLRM, Mgunammo
Loki B;Loki;B;CL_LokiB;1 Clan_DualStreakSRM6, 1 Clan_LBX5, 1 Clan_Gauss, 1 NARC, 1 Clan_ER_Small_Beam_Laser;GECM, C3Radar, EnhancedOptics, 2 FreeTon;GaussAmmo, LBX5Shell, StreakSRM, NarcMissile
Puma Prime;Puma;Prime;CL_PumaPrime;2 Flamer, 2 Clan_ERPPC;GECM, EnhancedOptics, 5 DHS;
Puma A;Puma;A;CL_PumaA;2 Clan_LRM20, 1 Clan_Small_Pulse_Laser, 1 Flamer;GECM, EnhancedOptics;CLRM
Puma B;Puma;B;CL_PumaB;2 Mgun, 1 Clan_ERPPC, 1 Clan_LBX10;GECM, MASC, StandardOptics;LBX10Shell, Mgunammo

The list of ammunitions is not yet "unique" in every case. And I've not yet put the free ton in their own column, it will be tricky with my current parsing routines.


I hope to have something better to show tomorrow, I'm just pointing out I'm not dead :).
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 24, 2012, 01:07:28 AM
Looks great so far!

Could you separate the ammo types with columns, as well? Like this:
Code: [Select]
Puma B;Puma;B;CL_PumaB;2 Mgun, 1 Clan_ERPPC, 1 Clan_LBX10;GECM, MASC, StandardOptics;LBX10Shell;MgunammoChanging those commas to semicolons would make my job a whole heck of a lot easier, heh. It is important, though, to have the ammo types at the end of the string, if that makes sense. They need to be the last columns in the row, ideally.

I might be able to work around not having free tons in their own column, I'll mess around and see what I can come up with. Don't bother going too far out of your way to do it yet.

As far as dropammo.txt goes, it looks interesting, but I don't think I have any use for it. I'm not sure what you made it for exactly, so I just wanted to mention that. Might still be useful to have someday though...
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 24, 2012, 02:03:30 AM
Changing those commas to semicolons would make my job a whole heck of a lot easier, heh.

Will do. I didn't do it before because records in a CSV file are all supposed to have the same number of fields, and it is impossible for me to do that (I print as I read variants so I can't keep counters). It is trivial to change the separator to a semi-colon, I just had it set to ", " for the weapons and equipment before.


I might be able to work around not having free tons in their own column

"Yo dawg, I heard I could just parse my own parsing so I won't have to change my parsing."
Don't bother, I think I just found a (retrospectively obvious) solution. I'll run a regexp on the Equipment field and extract the number of free tons from there. Do I need to remove it from the equipment string, too?


As far as dropammo.txt goes, it looks interesting, but I don't think I have any use for it. I'm not sure what you made it for exactly

Oh, I'm just dumping stuff as I read it. It's from the function that reads and stores the HUD name and ammos for each weapon to be used later in the script. It's just debug output really, I included it because you seemed to want a separate table for ammo (but you used weapon names and not ammo in your example).

For both files I indiscriminately dumped columns I thought you might want, so you could tell me which ones to keep and in what order. I'll get rid of the header line when the format settles.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 24, 2012, 02:22:51 AM
Will do. I didn't do it before because records in a CSV file are all supposed to have the same number of fields, and it is impossible for me to do that (I print as I read variants so I can't keep counters). It is trivial to change the separator to a semi-colon, I just had it set to ", " for the weapons and equipment before.
Yeah, as long as the ammo columns come at the end of a row, it doesn't matter if the number of them differs. Doing it like that works fine, or exporting them to their own table would work too. Either way should be fine for me.

Quote
"Yo dawg, I heard I could just parse my own parsing so I won't have to change my parsing."
Don't bother, I think I just found a (retrospectively obvious) solution. I'll run a regexp on the Equipment field and extract the number of free tons from there. Do I need to remove it from the equipment string, too?
Nope, not a problem. It might look a little bit more tidy without it, but if it risks breaking future compatibility or takes you more than a few seconds, I couldn't care less.

Quote
Oh, I'm just dumping stuff as I read it. It's from the function that reads and stores the HUD name and ammos for each weapon to be used later in the script. It's just debug output really, I included it because you seemed to want a separate table for ammo (but you used weapon names and not ammo in your example).
Ah, yeah, a separate table for ammo was what I was using before, so that's why I mentioned it in my example. Also, I didn't know the exact names of ammo, and I didn't know if you could read out ammo types like that, so I just used weapons in the example instead. But the way you have ammo listed in the main tables right now is fine too.

Quote
For both files I indiscriminately dumped columns I thought you might want, so you could tell me which ones to keep and in what order. I'll get rid of the header line when the format settles.
Yeah it looks pretty good so far. The only things I'd like to see in dropcalc.txt that aren't already there are:
Thanks again man, this is incredibly useful.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 24, 2012, 11:40:34 PM
Done. Ammo types are now unique even if multiple weapons use them (missile launchers of different sizes), and I'm fairly confident my script is ready for weapons with alternate ammo types. Bring them on!

Done. I haven't yet removed it from the equipment field, I'll deal with the cosmetics later. It will take more than a few seconds, but if I'm doing something I might as well do it right (and tidy).

Done. Your earlier example suggested this but it left out one crucial information: HS or DHS? So I just created 3 columns for that, you'll pick the ones you want.
Actually, not all assets list their free tons either, that's why I said it might be "tricky". Identical problem, identical solution.
I have set the default values to "0" for HScount and "" for HStype and HeatSinks, but I could change that at your convenience, e.g. "none" for the later column. What I cannot do however, due to the way the data are (is this case, not) stored, is to specify the type of heatsinks for the variants that have none.


A sample of the result:
Code: [Select]
Name;Asset;Variant;ID;Weapons;Equipment;FreeTons;HScount;HStype;HeatSinks;Ammos...
Blood Asp Prime;Blood Asp;Prime;CL_BloodAspPrime;2 Clan_Medium_Pulse_Laser, 2 Clan_Gauss, 4 Clan_Heavy_Medium_Laser, 1 Clan_StreakSRM6;EnhancedOptics, 6 DHS, 2 FreeTon;2;6;DHS;6 DHS;StreakSRM;GaussAmmo
Blood Asp A;Blood Asp;A;CL_BloodAspA;4 Clan_ER_Medium_Beam_Laser, 2 Clan_ERPPC, 2 Clan_ER_Large_Beam_Laser;EnhancedOptics, 16 DHS;0;16;DHS;16 DHS;
Blood Asp B;Blood Asp;B;CL_BloodAspB;6 Clan_ER_Medium_Beam_Laser, 2 Clan_LBX10, 1 Clan_ATM6;GECM, EnhancedOptics, 5 DHS, 2 FreeTon;2;5;DHS;5 DHS;ATMStandard;LBX10Shell
Cougar Prime;Cougar;Prime;CL_CougarPrime;2 Clan_LRM10, 2 Clan_Large_Pulse_Laser;StandardOptics, 3 DHS;0;3;DHS;3 DHS;CLRM
Cougar A;Cougar;A;CL_CougarA;4 Clan_LRM10;BAP, LightMechJumpJets, StandardOptics, 1 AntiMissileSystem;0;0;;;CLRM
Cougar B;Cougar;B;CL_CougarB;1 Clan_ERPPC, 2 Clan_ER_Medium_Beam_Laser;C3Radar, BAP, LightMechJumpJets, EnhancedOptics, 5 DHS;0;5;DHS;5 DHS;
Cougar C;Cougar;C;CL_CougarC;2 Clan_LRM10, 1 NARC, 1 Clan_ER_Medium_Beam_Laser, 1 Clan_ER_Large_Beam_Laser;BAP, LightMechJumpJets, StandardOptics, 1 FreeTon;1;0;;;CLRM;NarcMissile
The rest of the file is attached.


Now let's address the minor details:
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 25, 2012, 12:38:37 AM
Done. Your earlier example suggested this but it left out one crucial information: HS or DHS? So I just created 3 columns for that, you'll pick the ones you want.
Actually, not all assets list their free tons either, that's why I said it might be "tricky". Identical problem, identical solution.
I have set the default values to "0" for HScount and "" for HStype and HeatSinks, but I could change that at your convenience, e.g. "none" for the later column. What I cannot do however, due to the way the data are (is this case, not) stored, is to specify the type of heatsinks for the variants that have none.
Ah, you're right, I overlooked the heat sink type. The solution you have to it right now is fine. The "heatsinks" column is the only one I'll use right at the moment, but "hscount" and "hstype" might be useful to have in the future. All the formatting is perfect.
Quote
I noticed the heatsink count was present in your spreadsheet's equipment field, so I don't have to remove it from mine like the free tons, right?
Yeah you can leave that in there. I actually like having it in both places so I can put it on the reference sheets in its own column, but still show up on the calculator too.
Quote
Your current weapon list uses "2x Weapon" as a format, I could add an "x" too. I could also remove the "1" or "1x" for single weapons. Whatever looks best in your opinion.
Having it formatted as "2x Weapon" would be best. I'd prefer to have 1x shown for single weapons as well. Working on it here and looking at them side by side, I actually prefer the way you have it now, sans x's. Keep the 1s, though.
Quote

I contemplated the idea of not printing the ammo list for variant without any free tons, but it may be better to do it on the spreadsheet logic's side.
Yeah I'd like to have ammo tables for all mechs, to allow players to account for in-the-field reloads using an APC or something, if they want to.
Quote
Some assets have CATM and CHLL listed in the XLS. It looks weird. They're clan weapons, alright, but they're clan exclusives. If that's from your macros, you can drop the "C".
If you're referring to the one thEClaw posted, that's just a quick and dirty proof of concept, I'll (probably) format it to just be "ATM" in the final version. I'll probably keep CHLL formatted as CHLL. It looks better to me, not sure why. Maybe just because every other Clan laser weapon has "C" in the name.

One last thing. I wouldn't mind if you kept the headers, just to help anyone who might look at these data sheets know what's what.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 25, 2012, 08:45:40 PM
I uploaded the alpha version I have to Google Docs if you guys want to look at it. I haven't really done any formatting yet, so it's all just plain text, but all the functionality should be 100%. Let me know if there's any glaring mistakes I missed.

https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdERQQ3FQaWJWd3dFT0R1djd5LXJkZEE&hl=en_US#gid=6
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 25, 2012, 09:59:22 PM
Nice to see you are making good progress. Two things I would have to address:

1. The "lookup"-sheet needs to cover entries up to "F", the Novacat F has been around in the XMLs for quite a while and may suddenly appear in the game.

2. How about making Excel/OO/etc. autocomplete the names of vehicles somehow? The quick and dirty solution would be to use column B of the "dropcalc"-sheet to copy available names below the actual calculator (like I did in the sheet I uploaded some time). There probably is a more elegant way, but it sure saves a lot of work to have all the names autocompleted.

Other than that, everything looks pretty much perfect. Or at least it functions perfectly, as far as I can see. Very, very nice work. :)
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 25, 2012, 10:23:39 PM
On #1: I'm not worried about that for now, but I'll add it to the lookup sheet anyway. There's no other F's in the game at this point, so it'd just be wasted real estate if I decide to pin it to the calculator.

On #2: Done, thanks for reminding me. Unfortunately it doesn't work that way in Excel or Google Docs. I haven't found a good way to make it work in Excel yet even, other than doing dropdown lists which I'm not a fan of.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 25, 2012, 10:32:24 PM
There's no other F's in the game at this point, so it'd just be wasted real estate if I decide to pin it to the calculator.
There is the Hawkmoth F. And it is definitely part of 95% of all the drop-lists you could ever imagine.
:P ;)

On #2: Done, thanks for reminding me. Unfortunately it doesn't work that way in Excel or Google Docs.
I know it works like that in Excel. Maybe you tried something different? (I meant this ugly idea (http://www.ozgrid.com/Excel/autocomplete-validation.htm).)
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 25, 2012, 10:42:21 PM
Ah, forgot about the Hawkmoth F, which is really stupid because there's no Hawkmoth D.

On autocomplete, yeah, that would work, but I'd need a full list of around 50 vehicles copied above every single mech name cell in the calculator. Maybe I'll mess with it though, it probably shouldn't impact performance too significantly. The thing about Excel though, I can't just put a single list somewhere in the B column like you did in your OpenOffice example (that was how you did it, right?).
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 25, 2012, 11:07:21 PM
On autocomplete, yeah, that would work, but I'd need a full list of around 50 vehicles copied above every single mech name cell in the calculator. Maybe I'll mess with it though, it probably shouldn't impact performance too significantly. The thing about Excel though, I can't just put a single list somewhere in the B column like you did in your OpenOffice example (that was how you did it, right?).
Not every cell, by far not. Excel and similar programs pull the autocomplete-suggestions out of the adjacent 2500 rows (or some value in that region) in the same column. For your current table that would mean you only need to put the names at one place. (maybe rows 500 and upwards so the user doesn't ever see them, or put them somewhere high up and hide them)
Just pull the data out of the B-column of the "droplist"-sheet, it doesn't really matter if they occur more than once.
Screenshot of the completion-process:
(http://www.abload.de/img/shot1c2khh.png)

There seems to be another possibility using some kind of validator, but I never got it to work properly.

PS: I don't think you need to worry about performance with this kind of spreadsheet. Lookup-functions are probably very well optimized in Excel, it shouldn't matter if you try to find one entry in one thousand or one in a billion.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 25, 2012, 11:42:44 PM
Yeah it works that way in OpenOffice, maybe it even works in other versions of Excel, but in 2007 at least it doesn't. Both the spreadsheet you sent me (the one in your screenshot there, I presume), and in my current one set up in exactly the same way, it won't auto-complete anything unless the mech list is directly above the cell I'm entering stuff into.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 26, 2012, 12:28:46 AM
Hey, hey, stop being so diligent both of you. You're making me look lazy ::).

Here's an update of my file:

I removed the FreeTons, added the 'x's, took them off when I saw your edit, and added the APC manually.
I also removed the header line, it doesn't really look neat and it makes "Name Asset" a valid asset name...
Maybe you can pin it to the top and exclude it from the sort and the formulas?
I understand your concern about the headers, but I doesn't take a genius to figure out what the fields are. Anyway I simply replaced that line manually when I added the APC, it's still in the output.

Well, I guess I'm done for now?


Oh and I you're going to allow for a F variant, add G as well. Variants usually come in pairs (cf. Partisan). The Hawkmoth D is a running joke in the forum and will never make it in-game. Except in an easter egg poster in one of the maps, but I can't remember where.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 26, 2012, 12:48:14 AM
Yep, looks good. Thanks a lot man! Hopefully this script works for a least a few updates before it gets broken by one of the MWLL patches. :P
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Az on January 26, 2012, 01:33:34 AM
It wont. The incoming DHS and chassis pricing changes will break it.

DHS were a special case already and the chassis will change not the values but the formula. Hopefully it will be straightforward and I'll be able to figure it out easily. I spent lots and lots of time on the armour pricing because of wonky floating point rounding...

The changes on the DHS may require me to change the way I'm parsing the heat-sinks, I might use this opportunity to retrieve the HS type even when there is no additional ones. I didn't mind adding a new output but I'm really reluctant to change the way the data is read and stored. I tried to keep things extremely generic, the next patch could add disco balls and holovid players and if they loosely follow the current template, they'll be picked up by the script. That very same flexibility prevents me to precisely find what I need in the data I gathered. It's more idealogical than pragmatical but if I can help hard-coding any assumption in the parser, I will. But I might have no choice with the heatsink technology, and have to store it separately.

I'm long overdue for a rewrite anyway, my script is full of old, unused or redundant code and I can barely reread it. Which is partly why it took me so long to get anything done. It will be a long and difficult job for me however, and unless I really am in a coding mood I'll probably settle for a quick 0.5.5 fix.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 26, 2012, 01:57:59 AM
Gah, that blows. Well at least my calculator can keep some minimum functionality with just the prices dump, hopefully it won't take more than a week for your or somebody else's script to get the rest of the data.
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: thEClaw on January 26, 2012, 08:30:54 AM
Maybe take a look at this old thing: Excel autocompletion (http://support.microsoft.com/kb/263552), and maybe take a look into the help.
more on autocompletion with Excel (http://www.bettersolutions.com/excel/EDZ483/LE216311611.htm)

If it doesn't work right now, there hopefully will be some way to make it work. I myself wouldn't mind a combobox combined with an autocompletion-system, but I don't think that's possible.

I'll just reinstall Office 2007, I only switched a couple of years ago because I didn't see why not rather use and support something free instead.

EDIT: Ok, the completion behaves different from what I knew from OpenOffice. Seems like you have to put the data directly above the table, with no empty lines separating it from the table itself. That way it works for me (for all cells in that column, so there is no need to paste the data several times).
Title: Re: Drop Calculator on Google Docs - Now with tonnage!
Post by: Snyp3r on January 27, 2012, 06:09:39 AM
Alrighty, got the more or less complete calculator uploaded now: https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdERQQ3FQaWJWd3dFT0R1djd5LXJkZEE&hl=en_US#gid=0

I set up the autocomplete the way you suggested, thEClaw, and it works in Excel just fine. Oddly, it also works in Google Docs, but only on the "Lookup" sheet. I'll have to figure out how to make it work on all of them, because that would be a great feature to get in there.
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: thEClaw on January 27, 2012, 09:18:56 AM
Finally! ;)

Thanks a lot for your work and effort! (And the same obviously goes for Az, the helping hand when it comes to needing random game-data. ;) )

EDIT:
May I suggest something? Would you add another invisible tab including short information about how to update the calculator? Just in case you ever decide to not keep it up-to-date. Maybe add a link to the forum-post including the script and things like that - just basic information so it isn't too much of a problem for somebody else to update the calculator.

And something else: I have played with one particular clan who used this to create his drop-lists. After a while they had come up with a good amount of drop-lists and started copying the whole file over and over to make room for all the created lists. That is not really a bad thing, but maybe there is a more elegant solution? More tabs? More tables inside the tabs? Something even better?

Last thing: Thanks for mentioning me in the "credits". :)
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: Snyp3r on January 27, 2012, 07:21:57 PM
Yeah, good ideas, I'll get to work on those.

I got auto-complete working in Google Docs now, too. For some reason I think having that one big merged cell (the one that says C-Bill calculator - x players) at the top of the calculator was throwing it off even though it was okay in Excel. I moved the hidden rows with the mech names to just below that merged cell, and now it works in both.

Edit: Updated the calculator again. 8 calculators per sheet now. I was going to have 16, but with that many, Google Docs really started to chug when trying to scroll the page on my netbook. I think 8 per sheet is enough though, by the time you've filled up a full screen of tabs, I think you'd have more than 100 calculators. I also finished the reference sheets, which are in a separate document here: https://docs.google.com/spreadsheet/ccc?key=0AiThWaDcTIbIdGtldHMxOXlLSWNWWkVvNjZ1c3lPdmc
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: thEClaw on January 27, 2012, 10:12:25 PM
Perfect, both of the files. Nice work, again. :)

Now I understand why you were thinking about performance before. I didn't really get that somebody might use this spreadsheet online - the downloaded version of course runs like a charm. And Google Docs feels very slow and unresponsive for me. It even looks strange, maybe I should use another browser if I ever wanted to use your calculator online...

PS: You might consider adding some version number to your calculator in the future. "0.5.4 Beta 7" would work. Or even "0.5.4.a". Just so that somebody checking the first post immediately sees whether there is a new version or not.

I really like how the new calculator basically only has two tabs/sheets. It is a lot easier to pick up and use than the older version, I think.
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: Snyp3r on January 27, 2012, 10:32:12 PM
Yeah, Google Docs runs kind of crappy, and the formatting doesn't import 100% correctly, but the reason we made it for GDocs in the first place was because (1) you could use it from almost any computer with a web browser and because (2) it made it really easy for drop commanders to share configurations in real-time. Anyway, I posted the .XLSX versions in the OP now. Might be better to use those if you just want a local spreadsheet, since I'm sure GDocs loses some stuff in translation.

On version numbers, I just left it BETA for now because I was updating it and tweaking it so frequently, once I get a little more testing and make sure this version is okay, I'll advance it to 0.5.4c.

I thought about adding the mini calculators back, but those are kind of unnecessary since you can just zoom in/out of the default ones are too big. And having just two, like you said, is a lot cleaner and easier to work with. I kind of got carried away with it before, heh. Decoupling the reference sheets (now that they're not needed for the calculators) is also nice, since that gives me more tabs to work with so I was able to split them into weight classes without worrying about the number of tabs.
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: Az on February 07, 2012, 01:23:56 AM
Data updated for 0.5.5.

Simple one-line change, the whole chassis pricing hasn't been tweaked yet. Don't forget to add the APC.
Title: Re: Drop Calculator on Google Docs - 0.5.4 Edition
Post by: Snyp3r on February 08, 2012, 09:55:13 AM
Updated the OP with new versions. Sorry this is late, they only just posted about the new version on Facebook, looks like I need to find a better way to get notified about new releases.
Title: Re: Drop Calculator on Google Docs - 0.5.5 Edition
Post by: thEClaw on February 08, 2012, 10:13:59 AM
Shame on you for not checking mechlivinglegends.net ten times a day!
;)

Thanks for the update.

PS: OpenOffice seems to be able to read the .xlsx-files. There is a message about links to files when I open the file, but everything else seems to work (if I click "No" in that message).
Title: Re: Drop Calculator on Google Docs - 0.5.5 Edition
Post by: Snyp3r on February 08, 2012, 10:54:53 AM
Hmm, can you copy and paste the exact error here for me? I'd like to make sure it's not some reference to another file that accidentally slipped in, although Excel should have given me an error about that.

I'm subscribed to this thread now, so I'll try to stay on the ball in the future.
Title: Re: Drop Calculator on Google Docs - 0.5.5 Edition
Post by: thEClaw on February 08, 2012, 01:32:59 PM
The error-message itself is pretty useless. And I don't know if there is a way to get more information.

(http://www.abload.de/img/erroretja6.png)
Title: Re: Drop Calculator on Google Docs - 0.5.5 Edition
Post by: Snyp3r on February 08, 2012, 01:46:09 PM
Yep, I did have some references to another file that made their way in somehow, should be fixed now. New files are in the OP.
Title: Re: Drop Calculator on Google Docs - 0.5.5 Edition
Post by: thEClaw on February 08, 2012, 01:57:48 PM
It is indeed. Thanks :)
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: Snyp3r on March 18, 2012, 03:39:18 AM
With Diablo 3, Mechwarrior: Online, moving to a new apartment and going back to school all coming up, I can't see myself keeping this updated anymore. I posted the master .xlsx file I used as an attachment in the OP, along with a few pages of documentation I typed up to try to explain the formulas and functions. If anyone wants to use it for their own project, feel free. I've still got email notifications for private messages and for new replies to this thread, so if anyone has any questions about anything I'll do my best to answer them.

Thanks to the other Aces guys, and especially Az and thEClaw for helping with this project. It's been fun, educational, and somewhat productive!
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: thEClaw on April 27, 2012, 09:47:49 AM
Thanks to you, Snyp3r for all the work you put into the final and previous versions of this calculator. It probably has served many people. Hope to meet you in MW:O some day. :)
(PS: Read your documentation for the calculator. Didn't expect you to go into that much detail - thanks for that.)

Now let's get this thing up-to-date.

Az? I haven't found a downloadable version of the script you are using. If there really is none: Could you please upload it or create new files for 0.5.6? I was about to update Snyp3rs DropCalculator, but the data I can get using other scripts is not compatible.

It seems updating would be really easy with the current DropCalculator. Copied the content of the prices.txt into the spreadsheet without a problem and I expect the rest to go as smooth as that. Snyp3r has done a great job. :) (Az, you still have to convince me of the quality of your work. :P ;) )
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: Az on April 27, 2012, 08:18:12 PM
Here's the 0.5.6 data. Amazingly enough, no errors showed up this time! So I didn't check anything in-game but it's probably all good.
The only price discrepancy I have is about the Morrigu B and its Heavy Coolant Pod.
Remember that you'll have to add the APC manually, as always.


I'd like to thank Snyp3r too not only for his work on the spreadsheet, but especially for the huge effort that went into documenting it before he left. Documentation is a tedious and often thankless task, coding is much more fun! Am I the only one that have a little nerd rage at every MWLL patch note that's a tenth the length it should be? :P

And thank you thEClaw for not letting that effort go to waste.


Az, you still have to convince me of the quality of your work. :P ;)

Well, I still have to convince myself of the quality of my work ::). But it works! That's the main thing.
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: Bloodycrow on April 27, 2012, 08:24:44 PM
Thanks for all the effort guys! We really appreciate the work you put into doing this and keeping it updated.
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: thEClaw on April 27, 2012, 09:04:45 PM
Thanks to Az I was able to (easily) update the drop calculator. Hopefully everything works - if not, somebody tell me.

I will attach two files:
The "USER"-version for all the people who only want to use the calculator. All the confusing calculations are hidden, no documentation is included.
Then there is the "DEV"-version: No hidden tabs, the documentation is included. I also added a page to the docs to describe how to update the calculator in a few words. Although this description remains somewhat useless until Az releases the script he uses to read out the data.

The USER-spreadsheets are available as .xlsx, .xls and .odf. Hopefully everybody will be able to use one of those. Also, I did not split the spreadsheet into information- and calculation-part. I wanted to minimize the amount of work necessary to get the update done.

Now I will sit down and start praying. Maybe Snyp3r will notice it somehow and add the updated drop calculator to the first post. ;)
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: Snyp3r on April 27, 2012, 10:46:18 PM
You should probably just start a new thread about it, especially since I already said I was abandoning it in the OP, and the new version isn't on Google Docs anywhere. I'll just update the OP with a link to the new thread and then you can do whatever you want with it. Glad to see the documentation helped you guys get it all updated! :)
Title: Re: Drop Calculator on Google Docs - Final Edition
Post by: thEClaw on April 27, 2012, 11:43:09 PM
I didn't even think of that. New thread started over in the section for community created content (http://forum.mechlivinglegends.net/index.php/topic,17371.msg302022.html#msg302022). Fits there, I think.

Your documentation was really extensive, Snyp3r. Although I had a good idea about how to update the calculator before ;). Another thanks for everything!