Wednesday, January 22, 2014

The Trade Process

I am not ordinarily the type to talk about my dreams, and I don't like to listen to others tell theirs. They don't make good stories and people over-describe what seemed to happen. I usually employ an agreement where they must tell me the circumstances of their dream in three sentences or less. I believe I'm going to break that rule and use four.

I was playing a violent game of hockey. The game paused long enough for the officials to clean the ice of blood, whereas I and a teammate went outside into the Southern California hinterland. There, I spent some time explaining to my curious companion the vegetative qualities of the chaparral and the manner in which the sun changes aspect with reference to the earth, producing the seasons. Then we returned to the violence of our hockey game.

The dream is so me. I don't play hockey anymore, but when I was young, I did, and violently. I took the part of a defenseman enforcer, not great on skills and finesse, but mean and energetic. This should surprise no one.

The other half of the dream, though, that speaks to something I love more; I love explaining things, answering questions, getting people to know something they didn't know. I feel that everyone should know what chaparral is, even if they've never seen it, even if they're never going to see it. And the change of seasons is just damn cool. I'm aware many people don't 'get it,' they can't picture the way the earth moves, or they didn't pay attention in school and no one has actually bothered to explain it to them since.

So why wasn't I a teacher? Honestly, it's because teachers are allowed to educate only upon very rigid lines and in rigid subjects, to a select number of persons yearly. I didn't like the idea of anyone peering over my shoulder, deciding what subjects to which I should limit myself. No, no, the preferred profession was writer ... which I am, and which allows me to be unlimited in my choice of subject. Potentially unrestricted, too, in the number I can reach.

Yesterday's post was of little interest to many readers, no doubt. Economics is not a major role-playing game driver, and my particular take is unwieldy for most and apparently purposeless. Why not just make numbers up? Still, a number of people did show an interest, and this post in particular is being written to answer the questions of one who commented on my facebook yesterday.

The arc of adding a market to the trade system is a multi-step process. This is a good thing, as when I get tired of working on one step, I proceed to another, and slowly work my way around the world one region - and one task - at a time. I don't think I've ever written down the full arc before, so honestly there's something pleasant in doing so, even if it bores three quarters of my audience stupid.

1) Research the encyclopedia. This is the stage at which the references quoted on yesterday's spread sheet are obtained. I'll post a small example from the encyclopedia, to give a feel for what data is being parsed:

PLYEVEN, a city, formerly known as Pleven, in north Bulgaria, the capital of the district of Pleven. The city is located on the important Sofia to Varna road, and is connected with the city of Somovit on the Danube, about 25 mi. to the north. It is an important Turkish commercial center. It has a large textile factory, and is a market for wine and livestock. Pop., 38,997.

There are five references here. Plyeven's commerce is mentioned twice, so it counts as 2 references for market. I use 'cloth' instead of textile (sounds more Medieval), while wine and livestock are plain. This is recorded, along with any other references to Plyeven's economy that might occur on some other encyclopedia entry, then I move on to the next location.

Here are a partial list of locations in South America that I haven't yet looked at:

... Managua, Manaus, Manizales, Manzanillo, Maracaibo, Maracaibo, Maranon, Mar del Plata, Marianao, Marie Galante, Martinique, Masaya, Matanzas, Mato Grosso, Mayan Architecture, Mazatlan, Medellin, Mendoza, Mercedes, Mercedes, Mercedes, Merida, Mesopotamia, Mexico, Mexico Federal District of, Mexico Gulf of, Mexico City, Minas Gerais, Mollendo, Mona Passage, Monterrey, Montevideo, Montserrat, Morelia, Mosquito Coast, Nassau, Natal, Netherlands West Indies, Nevis, New Granada, Nicaragua, Nicaragua, Niteroi, Neuvo Leon, North America, Oaxaca, Orinoco, Orizaba, Orizaba, Oruro, Ouro Preto, Pachuca, Paita, Pampa La, Pampas, Panama, Panama Canal, Panama City, Para, Paraguari, Paraguay, Paraguay, Paraiba, Paramaribo, Parana, Parana, Paranagua, Paricutin, Parnaiba, Pasto, Patagonia, Paysandu, Pelee Mount, Pelotas ...

At present, I don't know what might be included in any of these places. It is still a mystery. By the way, to get this list, I went through the encyclopedia page by page some 17 years ago.

2) Add the data to the Sources document, the one posted yesterday. This step doesn't need much explanation. Usually I record the actual material in word before adding the information to excel.

3) Identify cities. Using the map from the Collier's Enyclopedia, I create a list of cities that needs to be researched in order to identify if they existed or not in 1650, how many times they were destroyed or diseased (affects the present population), and who is in possession of the city at the time of my world. For an area like Spain, this would be upwards of 700 places. I also obtain the latitude, longitude and elevation for these cities from a website called The reader can find the Spain Cities Workbook on my wiki, if they want to take a look. I haven't finished gathering all the location numbers for these, but they've been researched.

Cities that are not in existence are removed from the map AND from the sources document. Cities that are mentioned in the Sources document (and from the Encyclopedia) but do not show on the Collier's map are combined with the region in which the city lies. Thus, if there is a town named Vuy in Provence, and it does not show on the map, then Vuy's produce is transferred to Provence in general, to simplify it. The alternative would be to create Vuy on the map, and since there are many, many of these places (A single French department may mention up to ten such locations) it would only increase the general workload. One draws the line somewhere.

4) Create the regional map. I still have the series of images I created on the blog for Switzerland long ago when I mapped that area. The purpose of the map, apart from contributing to the running, is to establish a consistent measure for the distance relationship of one city to another. Since the map is measured in hexes, and the trade system is based on the number of hexes (and elevation) separating cities, the map is essential. Because the map is multi-purpose, I complete it entirely before moving onto the next step. There are also reasons why this is helpful, as elements that matter in the distance measurement includes the existence of rivers, other towns, where the highest hexes are and so on.

There are plenty of examples of my maps around.

5) Update the cities table. This is the other table shown on the wiki link above, the one that says "Cities 19feb13." This is a list of all the cities I've researched to date that have been finalized, plus some notes here and there on other tabs. The population for the regions is based upon the individual adjusted population for the cities compared to the population in 1952. Some of the regions have the area measured in hexes; I haven't settled down to do nothing but count hexes in quite a while. It isn't that important, but there are reasons to do so that have a relationship to the character generator. That's another story.

6) Draw roads. The roads are the actual representation of the distances between trading cities. A trade city is defined according to whether or not there is a market there, such as there is at Plyeven. I only record the distances between trade cities, those establishing the price for goods for the region they are in. These road distances are recorded on the nightmarish distance maps ... also included on the wiki link, as "Central," "East" and "West." This helps me build the total distance calculation between cities, which is based on the shortest distance between two trade cities.

7) Actual distance table is created. These tend to be immense ... and they are obsolete almost as soon as I create them. I've included one on the wiki link called "Group 03_HOM to MOD_21jan12" - which tells the last time I created one. Warning: if you don't set your iterations to 1, you'll get a circular warning error the moment you open the page. I described how that's done here, but that may not be useful if you have a different operating system than me.

I say obsolete because I only generate the page so that I can steal the meaningful data from it. These are calculation pages, which run the numbers until they stabilize and give me the answers I need. I'm not a computer programmer, I don't understand macros, so I have to do it this way, which is a work around that doesn't actually take much time. The relevant data on the table is highlighted orange.

Every time I add a new market city to the table, not only do I have to calculate that city's distance from every other city, but I have to calculate every other city's distance from the new city. That means, with some 600+ trade cities so far, one new city means 1200 new distance calculations. And since I tend to add multiple cities all at one time, that means tens of thousands of calculations to add the 30 to 60 cities I add at a go. That's the reason I haven't done this since 2012. I only want to do it when I really have every new place I want to incorporate, as I don't want to go through this process monthly.

8) The distances are combined in the All Zones table. This is the most critical table in my whole trade system. The table has been included, as well, in the wiki link. The first tab, Market Provinces, shows the distance between the trade cities (side column A) from the 'zones' represented by other trade cities (top row 1). Some zones have multiple trade cities, but only the nearest trade city in that zone is relevant to this table. Thus, Gilead (Jordan) transports out of Amman and Aqaba - but those cities nearest to Amman import from Amman, and those nearest to Aqaba import from Aqaba. The actual distances inside the trade zone are discounted for marketing purposes (again, gotta draw the line somewhere).

By copying any line off this tab, I can then paste it on the 'Input Distance' tab. This tab, you will note, has a yellow line at the top; the numbers from the first tab must be pasted as values only on that yellow line, where they are then calculated against the source references listed below. These references come from the MASTER tab of the source document I posted yesterday. The numbers here are then divided by the distance and recorded on the 'Calculation' tab of the All Zones file. Take a moment to compare the population of Croft divided by the distance between Croft and whatever line of numbers you've chosen to copy over. For example, if I grab Sarajevo in Bosnia and paste it on the yellow line of the Input Distance tab, the Calculation tab adds 1 to every number (so there is no dividing by zero) and the population of Croft is divided by 105.1 hexes (the distance between Croft and Sarajevo). Answer: 3,197 persons in Croft affect the economy of Sarajevo.

This is done for every single item described in the previous post, from markets to turtles. The totals are listed in green on the Calculation tab.

9) Transfer the All Zones totals to the Prices Table. This, finally, is also on the wiki link. I cut and paste the green highlighted numbers onto the 'Input Data' tab of the Prices Table (again, as VALUES). Instantly, new prices are generated on the 'Market Display' tab.

The process to generate a brand new market from the All Zones data, starting from grabbing a city of choice, copying those numbers, grabbing the result, posting it on the Input Data tab of the Prices table, takes less than 30 seconds. It takes longer to transfer the numbers to another computer, or onto the net, than it takes to generate them.

I hope that explains the process straight through, at least in general.


  1. So awesome to see this sheet updated, it's always interesting to peruse through.

  2. Hello Alexis,

    First, thanks a lot for sharing this ! I think I understand the general inner working of the whole process now – by which I am amazed. It was already fantastic to see parts of in in action, but getting a little peek the whole picture ? Mind-blowing...

    In reference to your remark on Facebook that some programmer could implement your trade system, I was curious and gave the article some further thoughts. I don’t know if this is an interesting post for others, maybe it’d be better as a private mail, but seeing as there is no other comment, and on the off chance that some other IT madman see this…

    From what I understand :

    1) Research the encyclopedia : no automation, User Interface useful ? Human mind needed - but an helping interface could pe proposed ?

    2) Add the data to the Sources document : no automation, UI useful. However, a specific interface could/should be added to the application.

    3) Identify cities : no automation, UI needed. Here again, a specific interface for creating Regions and Cities, and their associated data, is needed. A good plus would be lists of all available Provinces, Fiefdoms, kingdoms and Overlords, their mutual associations / restrictions for ease of selection, and an interface for managing the whole.

    4) Create the regional map : no automation, UI maybe useful but too costly. It could be made into part of a dedicated Map app, but that'd need an extensive amount of work, and one that’d be counterproductive if the app cannot use your existing maps to generate its owns (and that would take far too much time to develop for now). Still, if someone want to make a “new” world from scratch with your system, that would be useful.

    5) Update the cities table : no automation, UI needed. City creation would have been done during Step 3, you’d only have to input the data for each in the interface.

    6) Draw roads. No automation, UI useful. Actual drawing is still “by hand”, as would be the input of distances between the trading cities (both from and to). However, flagging a city as “Trade Hub” would automatically add it to the distance table, and update things accordingly (while also telling which data are missing). It is also here that the “Trade Region” association would be decided (even if it’s a functionality available both when editing a city and Trade Region).

    7) Actual distance table is created : total automation, limited UI. Once distance from a trade city to all its neighbors is known, everything else can automated. I am curious to see the algorithm used for the calculations, things perhaps could be processed faster, but I think it’d still take long enough to only warrant an “update” when desired.

    8) The distances are combined in the All Zones table : Total automation, limited UI. “Market Provinces” would be done during step 7. Calculation of the “Input Distance” tab for each could be done at the same time too. Selection of two Zones would then generate the “Calculation” result. Other tabs are left unexplained, but “Birthplace Number” could also be done at step 7.

    9) Transfer the All Zones totals to the Prices Table : total automation, limited UI. Selection of a market would generate all this by itself.


    My thoughts :

    There is nothing really difficult in making the automation part of all steps except 9. Data manipulation is quite easy once the formulas are explained (and the Excel do that nicely). User Interface would take more time, and there is a definite need to an “auto-import” feature, feeding your Excel and Publisher files.

    Step 9 however has lots of tabs with different kinds of tables and formulas, they are not all monolithic tables like the others, so no “easy” import can be done except some. This part would need some more work, specific displays for each part, probably also a final interface for the whole of available stuff.

    Do you have any comment on the whole ?

    I do not promise to work on this – life has a way to get me off track in surprising ways again and again – but I’d like to give this as much thoughts as I can.

  3. I have conceived of those things, Vlad. The prices table would just have to be incorporated into the whole, with one program that ran from the automation point to the actual prices. Manually input the source data, manually input the distances calculated from the maps, create the programming that manipulated the data and leave room for upgrades that would allow for seasons, famines, bumper crops, etc.

    Ultimately, I mean to replace the prices table (above the one posted) because it needs an upgrade too, in order that it could be more flexible. Right now, it is becoming calcified and behemoth-like. I never thought this format would get unwieldy, but I'm doing new things with it now.

  4. I was considering it from a web interface point of view.

    The only part that has to be pretty is the displayed market prices results.

    Setting up the results so they can be reliably retrievable would make it so that uploading for sharing would become a non-existent problem. Just share the URL. The downside would be it would have to be hosted somewhere.

  5. Luke,

    Are you considering a whole web interface with an "ugly" back office with all the data import, input and manipulation system and interface, and a light and pretty front office part for the market prices ? Or just the front office, using data pre-processed before (in another app or from the Excel, perhaps) ?

    Any preference on what technologies to use ? I'd go with C# myself as it is my current language at work (and it is advantageous for Excel manipulation), but I know Java and Php, and both could do the trick - and I'm not even considering those I do not know.

    I hesitate between online and offline app however. As long as it can export the end results in a usable format, an offline app can be as good for sharing them as online one.

    To Alexis

    Do you already have some food for our thoughts on the subject ? What you have already conceived, some elements that could help better define the project ?

    Perhaps we'd be able to make some thinking on this, and produce some document, models and stuff for IT madmen to gnaw upon...

  6. I'm absolutely opposed to an online solution for the programming. I know that's not popular, but my personal feeling is that nonsense like Chrome's Cloudplatform is the doom of reliable data management. If this nixes any possibility, so be it.

    Beyond that, I have no opinions whatsoever.

  7. Alexis, I have a question about your pricing table.

    I am looking at the "carpenter" section of the table. It has different listings for "existing" and "designed" houses.

    Can you please explain the difference? From the description, my guess is that the former is for improvement work, while the latter is for construction from the ground up.

    Also: I sent you an email but I wasn't sure if I had the right address, so I'll quickly ask here: would it be possible to purchase some (or even all) of your hex maps?


  8. Hello Maxwell,

    I did get your email. Thank you.

    Existing houses are those you buy as is and move in. Designed houses are built to specifications.

    Sorry, Maxwell, I'm not selling any maps at this time. It turns out I may have a use for them.

  9. Regarding step #5 and region population: What is your algorithm for reverse engineering the population of cities? I know you take into account things like plagues and wars, but I don't remember seeing any specific factors for such.

  10. Ah, my error Shelby.

    Start with the 1952 population of a city (A). Determine the date of founding (B). Let's say Vitebsk, Belorus, population 167,424, founded in the year 974.

    The algorithm is =(1650-B)/4000*A

    If the city were founded in 2350 BC, the population of the city in my 1650 would be the same as it is in 1952 - arguing that where it comes to really, really old cities, existing in those parts of the world that had resisted technological development in the early 1950s, the population has not changed the last 400 years. All the places on Earth that had been strongly affected by technology by 1952 did not have cities founded as early as 2350 BC.

    I admit, it's a bit of an ad hoc number, set in the moment just prior to the rise of Sargon of Akkad . . . but it seems to have produced good numbers for my city populations.

    The population of Vitebsk in my world? 28,295. About the same as Paris, Texas; Mason City, Iowa; or LaGrange, Georgia. Not a big place in 2010, but a significant settlement in 1650.


If you wish to leave a comment on this blog, contact with a direct message. Comments, agreed upon by reader and author, are published every Saturday.

Note: Only a member of this blog may post a comment.