Now, all the previous work I've been describing since yesterday exists in order to drive this table. It all comes down to the data you've inserted on the 'Input Data' tab, which is then converted into prices and availability.
Let me first say that this file is a mathematical disaster. Not only is it indecently complicated, but it represents several incarnations of data creation, each of which vary in some degree and each of which - I am sorry to say - tends to leave some bit of calculation that manages to survive into the next incarnation. So it goes. I do my best to update things when I see them, but the reality is that there's more here than I have time to update, or care to take the time, and in any event it can wait for the next time to come along that I make this file even MORE complicated. The subscriber will please observe that this table was created one patient entry at a time, starting about 2006, and has experienced now six overhauls since its original creation - one which I started and did not finish just this summer. At this point I don't even want to look at this table to finish that overhaul until after I can add Italy, India and the Low Countries to the Selected table.
So, things are unfortunately messy, but if you are patient you can pull them apart and probably make my life unhappy by bickering or identifying errors, things that don't seem to make much sense and outright unreasonable assumptions that are found there.
Here is the original problem. The first efforts were based upon the production numbers I had available from the United Nations International Statistics Yearbook and from the United Nations Food & Agriculture Organization's Agricultural Yearbook. Between the two, I could get an idea of the production of cereals, fruits, vegetables, oils, treenuts, crops, fish, livestock, metals & alloys, materials, chemicals, textiles, woods, foodstuffs and mining in the world at the time of the yearbooks. This was then modified in a rather ad hoc fashion to reflect the world in the 17th century, in a completely inaccurate way that really doesn't matter anyway, this is Dungeons and Dragons, and not my doctoral thesis for Harvard. Ultimately, all I really cared about from the beginning was getting kind of close. Since first mucking about with these statistics in depth (researching beginning around the year 2000), I feel justified in believing that I am close enough, and a damn sight better than some other perfectionist could probably manage. Where there seem to be irrational gaps in the prices template, you can believe the gaps probably exist there because there were insurmountable gaps in my research ... or possibly the later research hasn't yet caught some glitch from an earlier incarnation.
Am I getting across yet that there are many reasons why something might look illogical? Sorry, but as it is the only way I can tell the reason for a bit of illogic is by trying to remember why I did it a particular way four years ago ... and in my old age my memory is going. Usually the best thing anyway is to just try and fix it the best way it can be fixed.
With all that out of the way, let's start by looking at the 'References' tab.
This tab simultaneously groups and breaks down the raw data into convenient bits that can be addressed and reworked in the 'Raw Material Cost' tab. At one time this was supposed to give me a base price for everything ... now it is only supposed to give me a base price for commodities that don't experience any premier industrial development. However, there are things that still remain that are semi-industrial - like artworks and embroidery. Eventually the pricing for these things will be moved forward, but for the time being they can remain as priced on this page.
An explanation for the calculations on this page can be found, as ever, about three quarters of the way through this really useful post. The number of references for the specific market are divided by the number of total references and then multiplied by gold references to get their innate value, then adjusted for distance (the percentages in column K) and ultimately assigned a base "cost." Adamantite, for instance (the mineral and not the metal), has a base price of 98.36 copper pieces (column M, the coin not specified). All prices, except on the 'Market Page' tab, are specified throughout the table in copper pieces, then translated for convenience for the player.
The columns that refer to 'Second Tier References' is a development meant to compensate for the lack of proper reliable information. To see a complicated example of this working, see lines 967 through 1016 - FRUITS. From the encyclopedia I have references for sources producing 'fruits,' 'citrus fruits,' 'berries,' 'orchard fruits,' and of course a wide variety of particular fruits such as apples, apricots, oranges, grapefruits, raspberries, dates, figs, loquats, juniper berries, tangerines, guavas, pomegranates, olives, grapes, currants and so on. Obviously it doesn't work to have separate prices for 'fruits' and for 'tangerines,' so we must presume that references for fruits are distributed into whatever the local fruit of the area is ... and the local fruit is in turn also determined by the inputed data from the Selected table.
Thus, the 'Second Tier References' is the redistribution of more generalized references (citrus, fruit, berries) into more specific references (bananas, breadfruit, limes), according to the availability of these fruits. If the market is in a part of the world where there are more bananas than apples, the distribution of 'fruit' references will reflect that. If you understand excel, you can see how the numbers are thus redistributed.
Other places where this was necessary include tubers, metallurgy (where 'founding' could refer to any metal and not just iron), distilling and 'building stone' ... which could really be anything. A few places did not need this redistribution because of the way the table uses the data. 'Cereals,' for instance, could be applied to the general cost to fodder for animals, and because of this did not need to be sorted into its particular kind of grain ... the larger, cheaper price could be applied to groats and such to define it from grains which were specific and therefore eaten by people. Whenever I could generate a separate price that provided a unique item, I did this. It added depth to the general availability for the players.
Because the 'world value in oz. gold' column (column H) is determined by the number of gold references divided into the total number of ounces gold, when I rework this table to include new information from Africa, Spain/Portugal and England (from a reference I made on the encyclopedia post) there will be a dramatic change in many prices throughout the system. I admit that I don't know if it means the prices will rise or fall ... because there are so many changes that will come from the bulk Italy/India/Low Countries regions being separated out as well. In any case, prices will adjust and I expect the players will too.
For the moment I'll stop here, and pick up the next tab, 'Spells,' on the following post. I know that for subscribers this is a huge amount of information to take in all at once; but I am bringing you up to speed on a world that has been in a state of creation for 27 years. I began running 'Earth' in 1984. I've had a lot of time to think about these things and steadily bring them forward. You've had two days to see what's here. It will take time to understand what exactly you're looking at in the general picture. And once I get past the trade tables, much of the rest is quite simple.