[XLS] Ship Spec Calculator 3.2 (Update 02/10/07)

The place to discuss scripting and game modifications for X³: Reunion.

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

Post Reply

How do you rate this tool

Helpful, wish we had it sooner
24
77%
I'm Leet, I don't need no stinking Tools
1
3%
I don't have anything to use this with, but sounds good.
5
16%
Yo, homey, why'd you waste your time?
1
3%
 
Total votes: 31

Jakesnake5
Posts: 2874
Joined: Fri, 17. Feb 06, 05:55
x4

[XLS] Ship Spec Calculator 3.2 (Update 02/10/07)

Post by Jakesnake5 » Mon, 1. Jan 07, 12:24

WARNING: This utility requires Excel or Open Office's Calc (Free) or similar programs to use. Excel Viewer will not work.

This little utility was originally created by Observe, of the Xperimental Fusion Project (XFP) here. It can be used by ship modders as a reference to calculate ship specs of ships they create. It will show an average of how it compares, in which ever field they use, to the base ship. Ships should compare to 110% or less, to stay within reasonable limits.

First off, before I go on a complete tangent, the Link:

Link->Ship Spec Calculator<-Link

Red are selector fields. Click the field, and an arrow will appear to select the entry you want.
Yellow are not directly editable by the user, but other choices will change them.
Green are editable fields.
Grey fields are display only.

This little project was my first major (read: first EVER) Excel workbook activity. I didn't know jack about formulas, or how powerful they can be. Little did I know how much I'd learn in the process.

The Tangent
Observe asked for someone to update his workbook to incorporate, properly, information covering the Bala Gi update. I, insanely, volunteered :o.

Seriously, I didn't think (an understatement) it would take a whole lot to update Observe's workbook. Unfortunately (or fortunately, you be the judge) it didn't work out that way.

I had ideas, things that would be nice additions to the already useful sheet. Things I thought of as missing from the original.

My first objective, was to get the data organized. What Observe had in the data sheet was ok, but, besides being a reference, wasn't used by the worksheet. (handy as it was, it wasn't usable for my purpose).

To complet objective 1, I needed the data from the ES TShips file in the data area. This could be done in 2 ways: 1) Type in ALL that date by hand, or 2) import the TShips file directly. 1) was out of the question, which left 2) to deal with.

This turned out not to be quite as straight forward as it sounds. TShips ship data is riddled with variable data pertaining to Cockpits, Turrets and Guns. This made for unstable record lengths, many that wouldn't import as there were more than 256 fields in the records. I had to find a way to normalize TShips for import purposes.

I found, that if I used DoubleShadow's X3 Editor to remove all the cockpit entries from the ships, that it would also remove turrent and gun data as well. This reduced the fields per record to 64. Quite managable.

I then told Excel to import this modified TShips, skiping fields I ddin't want. The result was rather impressive. After removing non-ship data, I had 179 records of ships, which I placed in the Raw Data sheet of the workbook.

I then, after taking a gander at Merroc's Construction Calculator .xls, created several tables. These were used by 1 field to gain information referenced in the imported dataset, to display the ships Race, Class and Variant. I created a second field that had the actual ships name, which was from Observe's original workbook data sheet.

I then created a worksheet to look up this data in a meaningful way. Stealing more from Merroc's workbook, I created 3 lookups, that the user would select Race, Class and Variant of ship they wanted to use as a reference for creating ship specs. This part was rather easy, once I got to know how to manipulate the formulas.

Next, came the need for an workarea for averaged ship comparisons. The original had averaged all ships of a specific class. This, as you may have guessed, wasn't quite as good as it could have been, since some of the variants of a class can mess up the averages very heavily.

So, to deal with this, I created, in the data sheet, an array of entries, supported by formulas, to get the average information of specific ships Class AND Variant. This would mean there'd be records for M3 Basic (non-variant), Vanguard, Sentinel, Raider, Hauler and M3+ (Yes, M3+ is a M3 variant, not a new ship class).

There was 2 areas where this didn't have a straight forward way to acquire the needed data: M0's and M7's. As there are no X3 class M0's in the TShips (there is an X2 one for the KM0), I had to guestimate what this information would be.

Here, I used M1/M2's as a reference, and did some math with the numbers. The results are not too bad, but not quite what I'd call perfect.

The M7 gave me fits. Mind you, the Hyperion M7 is not a good reference for the M7 class. Because of it's status as a Prototype ship, its stats are a bit wild. Eventually, I did got average information between M1-M6's and M2-M6's. I then choose the appropriate data from those 2 comparisons to use in the M7 average. The data was fairly good. Later, in version 3 of the workbook, I edited some of the stats to be a bit better than they were.

All this work, gave me my first version of the Ship Calculator

Now, something you must know. I had been a betatester with XFP before its original release. Then I became a betatester for Egosoft, and my XFP status faltered to normal user. And that's where it stayed, until THIS sillyness.

I presented my workbook to the XFP community. They liked it. I continued to tweak it, all the while the leaders of XFP were plotting my punishment :D. I logged in one day, to check the messages, and threads I had posted in, to discover a rather shocking occurance: I no longer saw the X2 Avatar I normally had, plus I had 4 blue stars and the rank of Developer! :o

They had decided my work deserved a promotion. I received a PM stating as much. All from what I considered a makework tedious (I love tedious) project just to pass the time.

I then decided to create a new thread. Since my workbook was quite a bit different from Observe's original, I didn't want people thinking it was his, and faulting him for any bugs found (I did find a few myself, which I corrected later). Here I kept them up to date on my progress, and Saint Ashley threatened me with Sticky status. :D

Next Magical Trick
After completing my first phase of creation, I went into the next phase, which entials creating a workarea for calculating variants. This would use the Averaged data I had collected, as well as information gleened from the varius ship types, comparing the differences of the basic and variant's.

Acquiring the variation data took awhile. And, I only concentrated on TP, TS, M3-M5 class variants. TP and TS variants used different ratio variations from the M3-M5's, and M3, M4 and M5's variation ratios tended to be slightly different as well.

Once I had the data, I created the work area. First, the user would use the selector boxes to select the original Class, then the Variant they wanted to use as a comparison, for making their own ship specs. I had to do some checks to make sure the user selected the right variants for the ship class. Ie: You can't have a TS Vanguard or a M3 Superfreighter L :D.

Once finished, I decided to use the averaged M3 variant ratios and apply them to M6 and M7 averaged ship data. Now the user can create variant information for M6 and M7 class ships if they want. It was also, at this time, I tweaked the M7 averaged data to be more appropriate.

I discovered, after all that, that the Paranid Hyperion M7 was closer to a M7 Raider, than any other variant. And so, my first useful bit of information gleened from my work.

In the whole of this insanity, I only use 2 macro's (I HATE Visual Basic), both are used for clearing cell entries quickly for reuse (another thing I stole from Merroc's workbook :D).

Those who look at my formulas, may have serious problems with their heads exploding. I suggest Duct tape and Asprin be handy, in case you decide to take a gander.

My method of formula creation is basicly this: Brute Force. Nothing subtle here, sledge hammer and muscle. I'm sure it could be done neater and faster with macro's, but I HATE Visual Basic. So, I did everything possible in formulas, even though some of them are cryptic as all getout. (Did I mention I HATE Visual Basic? :D).

Thanks
My thanks go out to Merroc, for allowing me to snaffle some ideas from his workbook
Observe for the original work and giving me this insane idea to start with
DoubleShadow for his X3 Editor, which allowed me to figure out some calculations I needed to use
Last edited by Jakesnake5 on Mon, 16. Jun 14, 05:53, edited 5 times in total.
Perfection is in the hands of God, we bags of dirt can only do the best we can©
Image
Modders are a source of ideas to help the Game Makers improve what they have made. Cherrish them, for they are the fruit of thy labors.©

Bedsheet
Posts: 13
Joined: Tue, 30. Dec 03, 21:16
x3tc

Post by Bedsheet » Tue, 2. Jan 07, 02:05

... Pretty much all that needs to be said. Now to figure out how to use it.
P.S. Nevermind turns out I only have viewer.

Jakesnake5
Posts: 2874
Joined: Fri, 17. Feb 06, 05:55
x4

Post by Jakesnake5 » Tue, 2. Jan 07, 06:08

I put a link to Open Office's web site. This suite is free, and fits most OS's. It only works with Windows NT, 2000 and XP, not 95/98/ME.

The Calc software of OO can open the .xls file, and the fomulas work. What doesn't work is the Clear buttons, because OOC doesn't know VB macro's.

Regardless, it works just fine, even though you have to manually clear the editable cells. :roll:
Perfection is in the hands of God, we bags of dirt can only do the best we can©
Image
Modders are a source of ideas to help the Game Makers improve what they have made. Cherrish them, for they are the fruit of thy labors.©

Jakesnake5
Posts: 2874
Joined: Fri, 17. Feb 06, 05:55
x4

Post by Jakesnake5 » Sat, 6. Jan 07, 17:41

Updated

Created a Main page with instructions. Hopefully this will help

Fixed bug in clearing macro that was clearing wrong cells

Added M3+ Variant calculations

Changed Weapon Recharge to Weapon Recharge Percent.

Added Weapon Recharge Rate field. (Calculated based on Weapon Recharge Percent * (Shield Type * Max Shield Count))
Perfection is in the hands of God, we bags of dirt can only do the best we can©
Image
Modders are a source of ideas to help the Game Makers improve what they have made. Cherrish them, for they are the fruit of thy labors.©

Jakesnake5
Posts: 2874
Joined: Fri, 17. Feb 06, 05:55
x4

Post by Jakesnake5 » Sun, 11. Feb 07, 00:59

Updated.

Corrected a few errors in the formulas.

Adjusted M3+ Weapons Energy levels for Variant Calc's
Perfection is in the hands of God, we bags of dirt can only do the best we can©
Image
Modders are a source of ideas to help the Game Makers improve what they have made. Cherrish them, for they are the fruit of thy labors.©

Post Reply

Return to “X³: Reunion - Scripts and Modding”