Tuesday, October 24, 2017

Calculating Scarcity in Market Items

A small number of my readers will be happy to learn that I'm replacing parts of my trade pricing table with excel's vlookup feature.  I have found a practical application for it and I'm incorporating the function.

One problem I have had with the trade system is the question of availability.  It is all well and good to have things with prices that vary from place to place, but that still leaves the question, "How many can I buy?"  Or, for that matter, if there is anything on the shelf at all.

I have tried a number of methods for this over the year, one of which is still a part of the system as it is located for patreon supporters right now.  But it is based on random numbers and I must admit, for some things, random numbers just suck.  It was because of random numbers that I conceived by sage abilities system as things you can absolutely do vs. things that you absolutely cannot do.  There are some things that still incorporate random numbers, but the principle of knowledge is not one of them.  I know that the Serbians were massacred and driven out of their homeland during World War I.  There's no random chance that I will forget that.

Of late, I've figured out a way to get the random chance out of the availability of items.  Though it drastically flattens the likelihood of finding a given object, or how many are available, because the number of items is so large and the algorithm is hard to predict, that is only noticeable to the designer.  Eventually, the players will grasp the idea for some items, but I doubt they would for the whole list of goods and services that can be purchased.

It works like this.  Imagine what it's like to buy things in a general store.  You want to buy something very common like a torch and you find there's a big basket of them.  But then you want a flint to light that torch and it turns out there's just one left.  Or the store sold the last one this morning and you're out of luck.

That's what I'm reaching for.  That common items will always be there, or virtually always; and less common items will tend to be missing from the shelf marked "telescope" or "silver holy symbol."  Unless, of course, the player happens to be in a town where telescopes are made.  Then there will be a basket-full.

I'll just trust here that readers are vaguely aware of my trade system.  Every time I want to talk about it there's too much to explain and I have already been down that road.  Still, basically there are a set of references that I express to the fourth significant digit.  The number of references for any given object, from extraordinarily rare gems to general foodstuffs ranges from 0.0025 to 20.0000.

I'm beginning with the premise that if we're at a particular market, say Stavanger in Norway where the online party is, a total references of >1 for that object means that every possible version of that object is available.  If Stavanger's shipbuilding industry is >1 reference, then ships of every size can be purchased or built from scratch at that port.  Of course, the boat might not actually be in the harbor, but an agent in Stavanger can get it for you in a period reasonable with the 17th century, say a month or two.

From there, we create a series of tiers.  >1 reference equals Tier 1.  Dividing that number by four, 0.25 - 0.9999 references equals Tier 2.  Dividing it again by four, 0.0625 - 0.2499 equals Tier 3 and 0.01563 - 0.0624 equals Tier 4.

We then rate every object that is sold on a scale of 1 to 4 "sets."  Set 1 includes anything that is grossly common: ordinary stone, iron bars, a cloak, chickens, torches, a stay at an inn, ordinary wine and beer, whatever.  Things we absolutely expect to always find in any market town.  Set 2 is one up the scale: things that are a little less common but could still be expected to be found in any civilized society: a holy symbol, a metal candelabra, butter and cheese, hard boots as opposed to soft ones, books, a vial of salve for wounds, a stay at a nicer inn, etcetera.  Set 3 would mean low level luxuries; the sort of thing that peasants would never buy but would find its way into a merchant's household: porcelain, stained glass, a rare liqueur, a fur coat, a particularly nasty poison, a carved oaken bureau, a pet war dog and so on.  Finally, Set 4 would include every rare thing that conceivably existed.

For Set 4 things to be found, the number of references would have to equal the Tier 1 level.  At Tier 2, at best we would find Set 3 objects.  At Tier 3, no better than Set 2.  Finally, at Tier 4, only Set 1 objects would exist.  If the number of references isn't high enough for Tier 4, nothing of that particular good or service is available.  If we're so far from the makers of weapons that less than 2% of a reference exists, then no, there's nothing here, not even a dagger.  You'll have to make due with a club you find somewhere.

I know it sounds to some people that the Tier number should equal the Set number, and that's fine for them if that's how they want to do it.  It's just reversing the scale.

I like that this system narrows the import reach for objects that ought to be unusual, while maintaining commonality in areas where the object is actually made.  It may be hard to find a fur coat in Italy, but in Russia everyone is wearing them, even though they are a set 3 object.  You may have the money to buy everything in existence: but if it isn't on the shelf because it is never imported (its just too far away), then there's no point in waiting to see if something will come in next week.  No one ever brings it here because it isn't wanted.

This gives me ideas for how to manage the starting question, how many are there - but I haven't quite solved that one yet.  I'm patiently working on creating a page that gives everything a set number, then compares that with the automatically generated tier system ~ which is where vlookup is coming in handy.  Yes, it was a good suggestion and now that I'm starting to get used to it, I'll be using the function more often.  I'd like to thank those readers who poked me about it and to promise them that yes, even an ornery grognard can change.

I should be able to update the pricing table on the private drive for patreon donations in a week or two: sooner if readers clamor for a beta version that's only partially made.


  1. This sounds really good. Just a really good thing.

    I would be immensely interested to see how Constantinople (or whichever city contained the most references, I made a guess) stacked up against the far east and far west of your trade system.

  2. Pandred,

    I had considered writing a post about this, actually. So far, I have identified about 31-32 thousand references for my world, with a guess that after the new world this will equal around 35-40 thousand.

    2,500 are in Britain, 2,900 in France, 3,000 in Germany and about 5,000 in Italy and Spain. Scandinavia and the Baltic represent about another 2,000. So, in fact, MOST of the trade is in the circle surrounding the Netherlands (about 500 references in a very small area), nearly half, all within about 30 days of one another.

    Regardless of what I might do to adjust the system, the prices will always be set by the massive collection of references in a comparatively small area, all of it terrifically connected by sea. Compare that to India, which has about 3,000 references, few ports and most of the references being inland, often not even having the benefit of river travel. Too, most of the references in India are agricultural, while more than half the European references are industrial. Of industrial products, easily 80+% of them fall into the small area of the North & Baltic Seas, with quick access to the western Mediterranean.

    Weirdest of all, almost no part of Britain is more than 10 days from any other part, while the core of the industry is within 4-6 days of every other part of industrial Britain. Prices, therefore, for many industries are almost completely controlled by the very narrow travel sink that is the British Island.

    Interesting, nyet?

  3. I'm glad to hear that vlookup has been of some use! All hail vlookup! It's by far my favourite excel function.

    On to the subject of today's post, this is a fascinating way to determine item availability. It's not where I thought you were going with this: what seemed to me the most likely was that you would just say that the reference number was the % likelihood that the item was in stock at the moment (>=1 reference = 100% likely, 0.25 references = 25% likely). If it was out of stock, it would take something like 1/references days to locate one (that is, 1/0.25 references = 4 days, and so on). That wouldn't remove the randomness from the system, though, and so wouldn't achieve your goals.

    As always, I'm very interested to see where this goes.

  4. omg, David, I have tried that % system so many times, in so many ways; it seems so obvious a solution!

    The problem is the pesky nature of large collections of random numbers. Imagine an audience of 1,000 persons being asked to flip coins to produce a tails result. Each person who produces a head result sits down. There will always be that one person who produces a tails 15-20 times, flipping and flipping as everyone watches - but unlikely the same person each time we try the experiment.

    The problem isn't that weird things crop up on the table; it's that multiple things with a 95-99% chance of showing up don't. And with 1,500 items on the table, often the results contradict each other: why is it I can find a giant fur coat made of sable, but fur beaver gloves don't exist? Why is there butter and cheese but no milk for purchase? That kind of thing.

    I found it so frustrating. As you say, I'm looking to achieve a different goal.

  5. A way around that issue might be to randomly determine the presence of raw products only. But then you'd have to tie raw products to finished goods, say through a recipe system, and I can't recall without looking it up... is your system set up for recipes? You decide if goat's milk is available in a given market and if it isn't, then neither is goat's cheese. If a raw product is available, you'd run a second check to see if a finished good is present.

  6. The system is entirely based on recipes. However, any given product might be used in vastly different things. Take salt, for example: if there was a chance of salt not being present, that would massively cut down the total available products, including tanned leather, preserved food, rations and salt licks for animals.

    It makes MORE sense that salt would be held back for specific items, which themselves were more common than salt might be; it might be part of the recipe for making preserved food, but an artisan making dried meat would bring in salt especially for their product, which they would not sell on the general market. This means that what might be available to a customer would have nothing to do with what might be available to a specific vendor. Not a good system for determining availability.

    Besides, MORE random numbers, with considerably more work, would not reduce the aberrations in huge random number generation.

    Not actually looking for a better random number system, here. I'm about a sixth the way through my products now, having started yesterday, and it seems to be producing beautiful results.

  7. This is awesome, and I'm looking forward to seeing what you come up with for amounts available. This is spurring me to to figure out vlookup!

  8. I'm also glad to hear VLOOKUP is working for you. If you find it (or HLOOKUP) too constraining at times for certain arrangements of data, you can return results from tables in similar ways with INDEX and MATCH: as an example, I rewrote a formula on the Biology page of the old Character Generation Sheet as follows:
    In this particular case, I'm looking up the column in K5:Q5 (a list of heights) in relation to the column in $K$4:$Q$4 (a list of races), based on the position of Race (a labelled cell) in $K$4:$Q$4. So if Race is Dwarf, which is the first item in $K$4:$Q$4, then the MATCH returns 1 and the INDEX returns K5.

    On topic of course, I'm very excited to see these new tables. It feels more intuitive this way to begin with, since using a resource system to design the trade tables gives the impression that goods would be produced where the resources (be they natural or industrial) are located. In some ways, the sets seem to also speak to political powers of the world or the ascendancy of a particular region's goods: English wool or French wine (on a larger scale) might be available in many more places than English wine or French wool, emphasizing the trade power of those parts of the world, which could be an impetus for an adventure (what if the players want to disrupt that hegemony?)

  9. This way of using the number of reference to determine availability is very nice !

    But most of all, the way your trade system work and how it generate the prices, the Baltic Sea importance and the effects on the whole, all of that is really exciting to consider.
    Maybe the system is far from realistic, perhaps it's only a tool for the game, but damn, it works so well for that ! You haven't waisted your time doing it, clearly :-)

  10. I always enjoy your posts on your trade system. I think a robust trade system is the main thing I've always missed in games.

    Your system for determining scarcity sounds great.

    What sort of impact does your system have on where the great trading cities are?

    As I understand your system, you start with references which are places where an element of your trade system is produced, i.e. Stavanger being a place of ship-builders.

    So Stavanger and places near to it presumably have lots of ships available and as you get further away, there are fewer ships available (and the price goes up).

    How does that effect something like pepper which as I understand it was something that in our world in the era your game is set was both fairly commonly traded in for example London but was grown in India and shipped to Europe via Venice?

    Constantinople and Venice were major trade cities in our world because of their location on major trade routes.

    Are they still major trade centres in your revamped system? Or does the distance from the source make some goods incredibly rare even there?

  11. I was wondering the same thing as as Ian. Another example of that was the British imposing a tax on every merchant ship that came into port had to have a load of bow staves. This created a huge market in Italy and I believe Poland, which had the best wood for making bows.so while the wood was grown and cut and milled, it waz produced to sold in Italy or Poland it was sold in England. Another later example that is outside of your games time frame is the amount of German made sword blades for foreign markets, especially for India and the middle East. The British had a large cutlery market as well, al large number of Howie knives were made i n England with the express purpose of being sold in America.

  12. One has to be careful about reading history; the bow stave tax happened in the 16th century, a hundred years before my world takes place. The cutlery market in Britain followed the Industrial Revolution, a hundred years after my world. I can't find any history on line of the Howie knife, but from the looks of it, that's a 19th century phenomenon, with a few late 18th century American references. Again, way after the existence of my world. In 1650, the Dutch still own Manhattan.

    Venice lost its monopoly on eastern Trade when the Portuguese rounded the African continent in 1498. The Portuguese had seized most of the west coast trade of India by 1600 and Venice was being starved out. Venice was beginning its decline, though it continued to be a wealthy part of Italy until the unification. Constantinople's trade did not originate with India, but with Russia, as it was the chief route through which Russian furs, meat, gems, gold and timber flowed into the Mediterranean. It was also a route between Anatolia and southwest Europe - but that route did not become more than a military by-way until the founding of railroads. Still products from the interior of Turkey did find their way into European and Russian markets.

    None of this, however, is really relevant to my world. As I've said, I base all my products and prices on what I can prove out of an encyclopedia. Stavanger, as it happens, is not an origin for shipbuilding; however, it is less than eight days from four shipbuilding markets, and 15 days of about 30 shipbuilding markets, so if you want to buy a ship in Stavanger, it's no problem. There would probably be some outsiders who had set up shop in the town, who were working there temporarily before returning to Bergen, Aberdeen, Aalborg, Grimsby or wherever. Stavanger is a huge fish drying center; that is its principle industry. But that was my fault. The post was misleading on that account.

    As regards how shipbuilding affected the pepper trade, the ships were built in Europe and returned to Europe again after picking up loads of pepper - and a lot of other spices. No one from Europe appearing in Asia is going to sell their ship at that point, since they would not want to be stranded in India or fail to buy that pepper. Thus the arrival and use of ships in Asia in no way adjusts the cost of ships in Asia. If that's what you meant, Ian.

  13. Having built the trade system one small part of the world at a time, it is strange how things fit together. Venice and Constantinople are major trade cities; however, Barcelona and Genoa are bigger, as both have a greater and more powerful relationship with France and northern Europe. Barcelona is the shortest route to France's west shore; Genoa, to central Germany and to the Rhine. Venice is also a fast route to Germany.

    Because I haven't mapped the whole shore of Africa, the pattern of trade in my world still reflects the routes that were active in the 1400s. When I add Africa, and the shortcut from India, I expect the whole world to "get smaller" ... although that distance around Africa is phenomenal, and it might happen I've overestimated the cost of overseas travel. Right now I rate it at 40% the cost of land travel; the distance between Portugal and India is about 400 days. I have mapped the African coast as far south as Gambia, and the distance between Lisbon and Banjul is 52 days. This makes it hard to guess what it would be if I knew the whole length of the coast. I continue to wonder about it.

    The actual comparable distance just has to be close.

    My world does NOT reflect the real world, because of adjustments I've made to sea travel and shipping. For example, I don't have a major sea route between Oman and India. There ought to be one there, but as I invented this completely arbitrary system that limits the distance of shipping between minor ports, there's no port in the Persian Gulf large enough to bring goods in from India. India, however, brings in lots of goods from the Persian Gulf, as its trade cities are larger. So this means there's a lot of trade going west to east, rather than the other way around; most of the European from India trade goes overland through Pakistan, Afghanistan, Turkestan and Iran ~ and yes, it does go through Constantinople. That is greatly inaccurate, however; if it reflected the real world, it should be going through the Persian Gulf and out the Palestinian ports on the Mediterranean.

    Ah well, doesn't matter to me. When I remake after, that line through Constantinople will vanish and be replaced by a European route.

  14. That should read, when I remake "Africa" ... weird typing failure there.

  15. I've just had a revelation, which may be evident to everyone else but will certainly influence my system going forward: none of this necessarily speaks to the politics or human (or otherwise) influence on trade.

    When I decide that iron ore is mined in a certain region and makes its way into the trade network, that doesn't mean there is no iron ore in the neighboring region. It just means that, for whatever reason, region 1 is producing and region 2 is not. Perhaps there is an untapped resource in region 2, waiting for someone resourceful enough to take it and sell it. And maybe the reason it hasn't been touched is a natural barrier, the presence of monsters, the distance to a profitable market, or some other challenge. Or maybe someone is working on that prospect, right now, and in a few in-game months, I can add a new reference to the system.

    By building scarcity into the core assumptions, you also create opportunity to flesh out the setting and encourage players to adventure.

  16. I knew that players could influence the system, but it never occurred to me that, indeed, actions could add to the number of references, and that an absence of reference is not an absence of potential.

    Blind, so blind ! And so many adventure seeds ! Trying to grasp the market situation, sabotaging references to sell higher or advantage a patron, seeking and securing alternate / new reference sources, etc.

    Alexis, how could a player get a better idea of the references / market situation, and do the people managing a commercial venture get XP for the earnings (and losses ?) ?

    So inspiring ...

  17. Vlad, to answer your question, How could a player get a better idea of the market. Well, first, the player could pay me $10 on Patreon and see the system in its entirety.

    Alternatively, Mercantilism is a sage ability: http://tao-of-dnd.wikispaces.com/Mercantilism

  18. Do you see other sage abilities encroaching on Mercantilism? For example, could a high level ranger identify a market for a particular animal pelt when traveling through a new area? Or is that ability limited to those who have knowledge of economics?

  19. Sorry, I feel I have to be a bit short here.

    Does the merchant's knowledge of the value of pelts improve the merchant's ability to trap them?

    Okay, a bit more. The ranger ought to know what he can sell his pelts for at the place of sale, but I don't see how that gives him additional knowledge about movement of pelts through the whole system.

  20. Keep in mind that there's nothing to stop the players from systematically increasing their knowledge by exploration; I don't see personal experience needs to be co-opted as a sage ability.

  21. "Thus the arrival and use of ships in Asia in no way adjusts the cost of ships in Asia. If that's what you meant, Ian."

    It wasn't but your next comment covered most of what I meant very nicely though. Thank you.

    As you say it would be amazing if your system did spit out the same trade routes and major trade centres and there is no reason it should.

    My question about pepper was trying to ask what the effect of your new scarcity system is on the availability of goods that come from far off in very large cities with good trade routes.

    In other words, based on history I would expect that a commodity that is in high demand, such as pepper, would be available in a major city like London quite commonly, albeit at stupidly expensive prices - even though there are no references for it anywhere nearby.

    Does your new system have that effect or does it mean that goods only travel so far before they become so extremely rare that they are effectively unobtainable?

    I suppose I'm asking in a very cack-handed way about the effect of demand.

    Your new system deals with supply very nicely. Goods that come from far away are scarce because they have to travel so far. But it might be worth shipping them all that way rather than selling them closer if the demand in London or Moscow or Hamburg or wherever is high enough.

    If the system does end up with goods only travelling a certain distance before being unavailable I recall that in this post:


    you spoke about listing "market" as a reference but I'm not clear what effect that has other than indicating that the place is a 'market town' and therefore goods are available to be traded there rather than being entirely bound up in local use.

    Could the number of "market" references be used to 'pull' rarer goods to that place? Perhaps by adding the number of market references to the goods reference number for that location?

    That way the little market town in the middle of nowhere still gets very little in the way of rare items (only one market reference) but major market centres like Antwerp, Hamburg or Barcelona (to take those from the Market Towns post) would have much higher chances of access to rare and exotic goods.

  22. Ah, gotcha Ian.

    The greatest distance between two points in the system thus far is 400 days. One reference divided by 400 equals 0.0025. This means that any product - pepper, say - that occurs with 7 references or more must produce an availability of at least Tier 4. Since pepper would be a Set 1 product (anything in its raw form would be a set 1), there is pepper in London. And if some references for a product are closer than 400 days, then the number of references needed to provide the product in London is less. The world has 11 references for pepper (so far, there are still places in the Orient I haven't added), so that's plenty.

    It is only the truly, truly rare things that disappear at long distances, like civet or ambergris, that have as few as 1 reference in all the world. These are things that a buyer for a company might have to go to the orient in order to obtain.

  23. "Since pepper would be a Set 1 product (anything in its raw form would be a set 1), there is pepper in London."

    Phew... I was worried about how the Worshipful Guild of Grocers would survive.

    Seriously though, this is one of the things I love about your system and your world.

    It makes it perfectly feasible to have characters whose search for adventure consists of say, trying to finance (and then carry out) an expedition for ivory or ambergris or whatever article happens to be fantastically rare and valuable or to find (or clear) a quicker, safer trade route to the Indies or China or seize the Banda Islands for their state sponsor.

    Wonderful opportunities for scams by claiming to have found the North-West Passage (or whatever mythical route makes sense under your trade system)or a faster route to China via the Underdark (assuming you have that) or the Terra Australis (full of easily exploited natives wielding primitive weapons made of solid gold, obviously).

    The opportunities are endless and far more interesting than the usual "There are some orcs causing problems in the next village, go kill them".

    Even with that sort of thing, you can actually say what problems the orcs are causing and why anyone would care, i.e. the village they've razed sits on the trade route to Pfaffenburg and they're not getting their raw hides or timber or whatever. Prices for leather goods have gone up threefold. There's a wheat shortage. People are complaining to the Aldermen.

  24. Obviously,I'm proud of this accomplishment.

  25. I've climbed over this site for several days and have been unable to find the "straightforward" answer. Been looking at your equipment tables and am trying to put together my own spreadsheets (and even making some satisfying progress, while acknowledging I'll NEVER be done!)

    I'm trying to figure out how you "calculate" your "workmanship" number. So in [THIS post][https://tao-dnd.blogspot.com/2016/04/calculation-table-for-minerals.html] it appears it's almost always 1. But in [THIS example][https://tao-dnd.blogspot.com/2010/07/lets-try-it-from-beginning-again.html] it appears you just assigned a number. "let’s just assume that chance means the foodstuffs pricing references = 3.00."

    Can you provide any guidance here? I've tried using local references for the service in question calculated using global references and shipping distances et al but I'm coming up with widely divergent unsatisfactory results.

    Also - welcome back from your recent travels. Was sounding like it was much needed!

  26. I did just assign a number. It works. It only matters to me that it works, not where the number comes from. A larger number doesn't work. A smaller number doesn't work. This number, which works as a constant, works.

    Don't overthink it.


If you wish to leave a comment on this blog, contact alexiss1@telus.net 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.