Thursday, September 28, 2017

Trade Number Crunching

So.  This is hard to explain.

For more than two weeks, I have been putting all my energy towards a rebuild of one key part of my trade table.  I'm not changing any of the math, I'm only adjusting the manner in which the excel file is built.  The part I'm fixing corresponds to the calculation of transport against references.  See the link for a file that can be downloaded; the key page is the "New Master" tab.

Now, why anyone would care, I don't know; but I've been working on this day and night to the exclusion of all else, as long as 12 hours a day when I have the whole day.  I started on Friday the 15th.  I'm still working on it.  I guess this has been 62-68 hours of continuous work.  Why?  Because it is manually rewriting more than 40,000 excel entries.

Previously, the "master" page that accounted for all production for all market cities was a simple chart, 892 products across the top of the page and 1,067 market cities down the left side.  This made a chart of 951,764 cells, mostly empty, with about 5% of the cells filled with references.  When I wanted to calculate the distance effect on references, as shown on the wiki link, I had to multiply the whole list of cities against all the cells (because it wasn't feasible to pick and choose cells is such a large field), which was getting incomprehensibly slow and file crashing for my computer to manage.

And, of course, the problem was only going to get worse, because I had to add more markets: Burma, all of Great Britain, Iceland and part of Africa - a total of another 150 markets.  Something had to change.

So, now I have built the table so that it sorts each kind of product into its own individual chart; the calculation is made automatically when the distances are input into the file (as can be seen on the downloaded link) and added together.  I will then add another page that correlates the individual products so that it can be compared with the prices table.

This actually reduces my mechanics by one whole file, as it compresses two files into one (don't worry about it, it would take me too long to explain those files).  Plus it adds information, as now the user can go down the list and see where the largest amount of imported value is coming from.

I offer this for geeks only. Most of you just won't care, won't get anything out of the excel file, won't understand why I'm doing this.  But it is definitely better.

I'm pretty close now to getting all the data recalculated; but most of that is on another file, not linked, sorted but not actually pasted onto the new folder.  It has been a long, long effort.  I'm pretty tired of just crunching numbers.  But this is the reason I haven't been writing much; unlike making monsters, which screams blog material, rewriting numbers into new fields and organizing data, not so much.

I'll be done this soon, however, and looking for something else to do.  I think, in the future, it will be easier to add new markets to my trade tables, and that is what I want right now.  Earlier, I had to pretty much do big chunks of new data all at once, basically waiting to collect a chunk before updating everything.  Now I should be able to do one city at a time, very easily.

I'm really glad about this.




9 comments:

Shelby Urbanek said...

I for one am super excited about where you're going with this - a fantastic innovation!

Silberman said...

Do you ever use the VLOOKUP function? If not, try this as an experiment and see if it might be helpful. Change cell b5 of the New Master sheet to this formula:

=VLOOKUP(A5,Data!A:B,2,FALSE)

then change c5 to this:

=VLOOKUP(A5,REGIONS!A:D,3,FALSE)

If you copy/paste these into the entire Distance and Resource columns (or drag the corner of the cells for the same effect), all the pointers to the DATA and REGIONS sheets will automatically update without needing to be manually set whenever you add new regions. This also quickly points out any inconsistencies in region names (ANDO/Andorra vs. ANA/Andorra, Thangwe vs. Thandwe). The age of your computer will determine if this incurs an intolerable performance hit.

Having looked through some of your Excel work, I think this one function would take less than an hour to figure out and could save you a lot of time in the future.

Alexis Smolensk said...

I don't want to seem ungrateful, Silberman, nor stubborn ... but I'm very uncomfortable with features I don't understand and have never seen. But I will go look up a tutorial.

Not sure I will employ this with this table at this time. I'm within a day of being done in my fashion and I'm comfortable with the methodology I'm using. I have had people point out things with excel before that I did not know about: and unquestionably, they proved wonderful ... but there has always been an adoption process, as I play with the idea first in smaller files to get the hang of it before trying it with something as big as this.

Ozymandias said...

Knowing that is one reason I haven't offered much in the way of Excel advice. I know how difficult it can be to learn a new programming feature.

That said, what if someone used your work as a baseline and created an updated version, with references and streamlined formulas? Would that be of any use to you?

Silberman said...

I completely understand Alexis, especially in light of just finishing a major overhaul of the workbook. Just keep it in mind for some future project. VLOOKUP is great for taking a RANDBETWEEN die roll and looking the result up on a table. In the past (i.e. character background generator), I've seen you resort to amazingly-nested IF functions for this effect).

Alexis Smolensk said...

Ozymandias,

Not as much as I would hope - and I do mean "I". If my files were static and never changing, that would be one thing, but I'd have to understand the programming or else I couldn't update it, could I?

Silberman,

Yes, well, nesting if statements seems to get under the skin of many an excel professional. I do my best. The funny thing is, the times I've worked in the commercial world with databases, I always seem to know more about excel than my peers ~ and yet I know I am still just a baby.

connor mckay said...

Thanks for sharing this. I am trying to get the motivation together to try trade tables rev 4 for myself. The other ones seemed to clunky to me when I tried to make them. I created an automatic weather generator based off of your Weather Generator Mark 6 series and it works beautifully, so I am feeling up for trying to tackle the trade tables again.

I will also encourage you to become familiar with VLOOKUP. I turned my original weather generator from about 10,000 calculations into about 100-150 by consolidating the calculations and using VLOOKUP to populate the numbers needed. It cut the wait time for recalculation from about a minute to less than a second.

Also want to say that the 'Measuring Yourself' post was spot on. Thanks fro writing it.

Alexis Smolensk said...

Thank you connor, I'm glad to hear that about the weather system.

I just finished the re-entry of the data, just now. All that's left is to sort it into easily read columns.

Justin said...

Alexis - these tables, whilefar too MASSIVE in scale and scope for my own game, are tickling some gross number crunchy nerve in the back of my head.

Do you have any suggestions or resources for a much lighter, plug and play, version for something like a hexcrawl?