Phew.
With respect to the redesign of my sources and production calculator spreadsheets, which will mean nothing to those not steeped in my trade system, I'm done. It's rebuilt, cleaned up, made to look pretty and now available on my Google Drive, for those of you who have paid the access fee.
Truth be told, some of you who have donated to me in the past have bowed out of my Patreon in the last six months. Your names are still on the system, when they shouldn't be ~ still, I'm relaxed about it. You were great about supporting me and I thank you. Still, I'm going to have to downgrade your access within a few days, so don't waste time if you want a last look.
For anyone who wants to see the completed table, you'll have to either pledge $10 to my Patreon account (in which case you'll get access the 1st of November) or donate the money to me directly, in which case I can set you up as soon as I see your email. I've worked many, many, many days and hours on this; I'd be grateful for a remittance at this time.
For myself, I'm in a position now to comfortably add Burma to the system, which was the straw that broke the back of the old set-up. Then I can add Great Britain, Iceland, part of Africa ... but all at my leisure. I may put down numbers for a while and work on something more, ahem, shall we say blog worthy.
Been a rough go, this change. Obsessive. Glad it's behind me.
Hello Alexis,
ReplyDeleteI'm quite impatient to have the time to dig in this, and your current crunching upgrade is a nice addition, that'll also profit to those that use the Excel for their own world.
My bank card is currently unable to make online payments, but should be unlocked soon. Until then, feel free to remove my access.
A question though, you said that we can give you money directly, do you prefer this to Patreon ?
Sincerely,
Vlad
If you pledge, you have to wait on Patreon's schedule, always the first of the next month; but a direct donation reaches me in about a day through paypal. This is what I mean.
ReplyDeleteHey Alexis,
ReplyDeleteLong time reader, first time poster. Even longer time Excel user, which brings me to my point: I think I can save you many hundreds of hours on your excel spreadsheet. The VLOOKUP function allows you to find a certain value in a table or array. For example, say you had one large table with one row per market town, and one column per trade good. Each cell contains the references for that market for the trade good in the column. For example, if the first row is Paris, and the second column is Wool, and paris has 1 reference for wool, you would put a 1 in that column. Use the "format as table" tool to make this huge array a table. By default this is called "table1".
On a seperate sheet, you can use VLOOKUP([city name],[table],[column#],FALSE) to look up any arbitrary reference for any market in your world. Always set the last argument for VLOOKUP to false or it'll just output nonsense. In this example, VLOOKUP("Paris",Table1,2,FALSE) would return 1, which is the number of wool references in paris. ("2" for wool because wool is in the second column of Table1). This way, you can have just one spreadsheet to calculate the price values of every market town. Say A1 is a cell in which you type in the name of the city you want to look up the prices for. This means that VLOOKUP($A$1,Table1,2,FALSE) will return the references for Wool in whatever city you type into A1. To pick numbers arbitrarily, 3 can equal bricks, 4 can equal horses, 5 can equal leather goods, etc. You can even have a separate table, Table2, with all of your goods in the first column, and their column number in the second, meaning that VLOOKUP($A$1,Table1,VLOOKUP($A$2,Table2,2,False),False) will return the references for the trade good typed into cell A2 for the market in A1. If you create one spreadsheet template for a market town and replace every cell that includes a reference with this formula, swapping A2 for a cell with whatever good you want to look up, you will be able to simply type in any market's name into A1 and it will populate the entire list with prices based on your database.
If you're interested, I'm happy to answer any further questions you may have.
Yes, I've had people just this last week suggest VLOOKUP already.
ReplyDeleteI have looked at two tutorials now. I think that people who are suggesting this solution may be vastly oversimplifying the complexity of the trade system.
For example, David, you say that I can use just one spreadsheet to calculate the price values of every market town. That is interesting, since the price values for every market town are a composite of the distance of that town from every other market town in the system, with similar products of all the towns added together. With more than 1000 market towns, this is 1,000,000 possible city-to-city comparisons. Now, with more than 900 different sorts of goods and services, now I need a database with 900,000,000 known totals, though most of these will be zero.
The vlookup function will "look up" data, but it won't "calculate" data, not that I have seen so far. This means I'll have to precalculate all this data before it can be looked up. Sounds like a pretty big file.
On the other hand, the file I built that enables me to do the calculations requires only 2.8 mb of space. And runs instantly. Moreover, it can be VIEWED with human eyes and instantly comprehended, because it does not look like a computer programming mess. It is clear and humanly accessible, which is what I want. I need a table that, in the middle of a running, when a player says, "where does most of the building stone come from in this place," I can glance at thirty or forty figures and say, "Apparently, a part of Sweden near Falun," WITHOUT needing to have the file do that for me.
This isn't just a calculator; this is also a game service engine, which I think many just don't understand. I appreciate the suggestion, David, but while it is clear that VLOOKUP will be good for something in my future, right now I don't see it managing this. And at any rate, the work is already done.
I'll try to explain this a little further. If you've been to the wiki and downloaded the file there, to look at it, you'll have noticed that the first tab, marked "data," has a string of numbers connected to each city. These numbers are only relevant to one city, Stavanger, in Norway. These numbers are computed by an algorithm that determines the shortest distance between Stavanger and every other city, but it does not also, at the same time, determine the shortest distance between, say, Paris and Constantinople. If I put in the right numbers, it will calculate the distance between Paris and every other city, or Constantinople and every other city, but not both at the same time.
ReplyDeleteThere is a way to do that, but then I run into a file size problem. See, if I set it up to calculate every trade city I have at the same time, it makes a huge excel file that not only breaks the limit of my computer to handle the 1,000,000 calculating cells, along with another 2,000,000 non-calculating cells, the actual calculation is so slow it is completely useless. Basically, the calculation is a decision tree model and it is best to do this one city at a time.
Therefore, it just isn't practical to generate all this information at one time, particularly when I don't even need a trade table for every city, I just need one when the players go to a town. I have it down to the point where I can generate a pricing table that I need, for any market city, within about 2 minutes of game time. That's all I need. It would be a waste of my time to build a VLOOKUP table for market cities I don't expect to ever sell a good out of.
My apologies. I'd followed your tutorial on the wiki under "Trade System" but haven't looked at your actual, substantially more complex trade system. As a result, I'd been under the impression that you had a separate excel page for every single market, which to me seemed like it would untold weeks slaving away to create. I didn't realize that you only create such a table as you actually need it, which would, as you mentioned, amount to massive time savings.
ReplyDeleteI suppose you could set things up so that it creates the pricing table using VLOOKUP when you enter the information of the city you want to calculate distances for, but there's not a lot of advantage to tossing out the system you've already created to do that.
While I've got you, I was wondering if you could help me understand a small bit of your trade system, which is how you decide what qualifies as a full step in manufacturing. For example, in your tutorial on the wiki, you split wool manufacturing into "greasy wool" which is cleaned and carded and becomes "cloth." However, you could just as easily input a calculation and calculate labour for spinning the wool, then weaving the wool, for dying the wool, and again for manufacturing cloth goods out of the wool. The finer you break it down, based on your calculations, the more times the references of the city come into play, meaning that a city with lots of wool-related references will make the five-step cloth only somewhat more expensive than the two-step cloth, while a city with a very low reference number for related industries will have dramatically higher prices. So my question is: what constitutes a step?
Thanks for the help, and keep up the good work! This blog is a joy to read.
In the case you mention, the greasy wool is not treated as a manufacturing step, but as the total unprocessed by-product of all sheep in the world. Before we can start doing anything to wool, we have to start with a base amount that we have to process; the cost of greasy wool is, therefore, based on the price of the sheep themselves, and not on any process that has been applied. The shearer is the equivalent of someone who has produced ore from the ground or cut wheat from the field, and is therefore not considered a manufacturer.
ReplyDeleteNow, I have made some changes to my price system since the tutorial was made, but basically the principle we want to work on is this: what could the players conceivably want to either buy or sell?
They will quite probably someday own sheep (in my world at least), so we want to know how much they will receive for it at the market. They may want to improve that wool themselves, by washing it and spinning it (which I now treat as one measure of improvement), so we want a price for that. They may want to buy cloth not made into clothing, so that needs to be another step. And, finally, we want the players to buy clothes, so that is a last step.
You're right, we can subdivide it further, but it only matters if the players might want to buy cleaned, uncarded wool as opposed to cleaned, carded wool. That seems unlikely, especially as many don't know what carding means; so it isn't worth including that as a step. Incidentally, it's probable that Orson Scott Card's ancestors were involved in that business.
My references are driven by an encyclopedia, not by my choices, so my system includes a lot of weird parts that a totally fanciful system wouldn't include. But it really depends on how deep you want to personally go.