Excel Traveller Trade

[Terry Mixon]

Not sure if I added it to the public one, but mine has two columns after the sales where I have the number of tons bought and projected sales price assuming all sold.

But wouldn't that change if you have to roll again for the sales side? I guess the more I think about it, the only way to use this cleanly as things are as an end-to-end trade generator is to generate the buy side, copy the needed parts to a separate spreadsheet, and then reroll for the selling side, copy that off and let the other spreadsheet figure the final CR/Ton and profit/loss if the sale is completed.

Not being an Excel expert (and being grateful to those who are) I have no idea if two separate rolls could be made at the same time, but that would make this spreadsheet a better tool. If it is at all possible, I hope that happens in a future iteration, though I'm grateful for what is there now.

 

[Arkathan]

But wouldn't that change if you have to roll again for the sales side? I guess the more I think about it, the only way to use this cleanly as things are as an end-to-end trade generator is to generate the buy side, copy the needed parts to a separate spreadsheet, and then reroll for the selling side, copy that off and let the other spreadsheet figure the final CR/Ton and profit/loss if the sale is completed.

Not being an Excel expert (and being grateful to those who are) I have no idea if two separate rolls could be made at the same time, but that would make this spreadsheet a better tool. If it is at all possible, I hope that happens in a future iteration, though I'm grateful for what is there now.

Yes it would change, but the column saying how many of what you bought would not. so roll to buy, record the purchase, roll for sale. see how much selling all will give you.
Will look into downloading deHap's and changing those two things. Separating the rolls and recording what is in the cargo hold.

 

[Terry Mixon]

Yes it would change, but the column saying how many of what you bought would not. so roll to buy, record the purchase, roll for sale. see how much selling all will give you.
Will look into downloading deHap's and changing those two things. Separating the rolls and recording what is in the cargo hold.

I appreciate it! Thanks!

 

[Arkathan]

I appreciate it! Thanks!

On second thought, I'm doing it to Jet Black's submission so we have the Trojan Reach in there as well.

 

[Terry Mixon]

Even better.

 

[Arkathan]

I appreciate it! Thanks!

Oh... Now I remember why I didn't separate those out. Every time you enter another number or tab to the next line, it recalculates the random number. Every time I enter tons bought, it would change the prices. So I set formulas to Manual updates, buy, record, then sell.

Up in the Ribbon, I went to FILE, then down the left side bar to Options (towards the bottom), Formulas, and then set Workbook Calculation to Manual.
Once you do that, in order to get the sheet to update, you have to hit F9.
Unfortunately, that means you have to hit F9 when you select the subsector, then again when you select the planet. Then again when you change tabs. Then again when you want to buy or sell.
So it just depends on which way you find more annoying.

 

[Terry Mixon]

Thanks for looking. I appreciate it. I'll leave it the way it is and copy and paste. It seems like less of a hassle.

 

[Arkathan]

Thanks for looking. I appreciate it. I'll leave it the way it is and copy and paste. It seems like less of a hassle.

Too late... or rather I saw your post too late.

New Version - Exceedingly optional.
This is the way I use the sheet in my game.
Added cargo section at the end of buying and selling chart. I roll for buy, record purchases, and the sales price updates when I roll for selling.
>>>You have to use Manual calculations (F9) to avoid going mad.<<<
Then you have to remember to reset to Automatic calculations before using another Excel file.
Instructions for doing that in the Instructions Tab.

 

[Terry Mixon]

I just ran a cargo set and happened to note that it was offering 21 tons of radioactives. I'd just looked at the chart in the book and it should only have been 1D. Looking in the hidden section, I saw that two different spots had added 7 each to the total, and even without them, the total was still 7 but I see that the population of 9 adds 3 tons to to the rolled total.

Hitting F9 a bunch, I saw it get as high as 27. What is it that is adding to the rolled totals? I'll assume the other kinds of cargoes are getting the same treatment and just want to understand why it is different from what the book is saying it should be.

I'm calculating from Glisten to Sorel and have had set the PC broker to 5 and Streetwise to 5 to test something.

 

[Arkathan]

I just ran a cargo set and happened to note that it was offering 21 tons of radioactives. I'd just looked at the chart in the book and it should only have been 1D. Looking in the hidden section, I saw that two different spots had added 7 each to the total, and even without them, the total was still 7 but I see that the population of 9 adds 3 tons to to the rolled total.

Hitting F9 a bunch, I saw it get as high as 27. What is it that is adding to the rolled totals? I'll assume the other kinds of cargoes are getting the same treatment and just want to understand why it is different from what the book is saying it should be.

I'm calculating from Glisten to Sorel and have had set the PC broker to 5 and Streetwise to 5 to test something.

OK. You roll to see how many items a broker has, then you roll to see which ones they have. If you get multiple items alike, they get added together. So if a broker has 5 batches, and three of them are radioactives, you will get 3d6 =/- 3x the pop modifier.
In that section you saw adding several together, at the top of that table above the index numbers, the first is the number of lots the broker has, every other along that row checks to see if it is less than or equal that number. If it is, it generates a random 1-36 (d66) and the result is the item.

 

[Terry Mixon]

OK. You roll to see how many items a broker has, then you roll to see which ones they have. If you get multiple items alike, they get added together. So if a broker has 5 batches, and three of them are radioactives, you will get 3d6 =/- 3x the pop modifier.
In that section you saw adding several together, at the top of that table above the index numbers, the first is the number of lots the broker has, every other along that row checks to see if it is less than or equal that number. If it is, it generates a random 1-36 (d66) and the result is the item.

Ah. I didn't realize there should be a total number of lots and that there was some randomization in what they were. Good to know. Thanks.

 

[Terry Mixon]

Ah. I didn't realize there should be a total number of lots and that there was some randomization in what they were. Good to know. Thanks.

I can't find in the core book about how many total lots there are. Is that spelled out anywhere that I could read and familiarize myself with?

 

[Geir]

I can't find in the core book about how many total lots there are. Is that spelled out anywhere that I could read and familiarize myself with?

Freight has Lots from the table on page 241, but speculative trade goods don't technically have lots.
On the updated Core Rulebook it's the section beginning on page 242: Determine Goods Available

It's a little convoluted and might benefit from a flow chart:

First check for Common Trade goods (item 11 to 16 on the chart on page 244-45), rolling on the Tons chart with a possible DM of -3 or +3 for Low and High population worlds, respectively.
Then roll (same DMs) on every row from 21-56 that has an Availability that matches the world's trade code (with the same potential DMs).
Only roll on columns 61-66 if you're also looking for illegal goods.

Then take the world's Population code and make that many more D66 rolls on the Trade Goods chart and for each result, which may be the same item multiple times. And those illegal goods may come looking for you, but you don't have to buy them.
For each positive result, roll the 'Tons' amount of goods - that is sort of a 'lot' if you hit the same item multiple times as a result of the random roll..

For Speculative trade, it's not all or nothing like a freight lot, but whatever amount you choose to buy.
(But I house rule it that you can't break down an item below the die multiplier (20, 10, 5, or 1), because containers)

 

[Terry Mixon]

Freight has Lots from the table on page 241, but speculative trade goods don't technically have lots.
On the updated Core Rulebook it's the section beginning on page 242: Determine Goods Available

First check for Common Trade goods (item 11 to 16 on the chart on page 244-45), rolling on the Tons chart with a possible DM of -3 or +3 for Low and High population worlds, respectively.
Then roll (same DMs) on every row from 21-56 that has an Availability that matches the world's trade code (with the same potential DMs).
Only roll on columns 61-66 if you're also looking for illegal goods.

Then take the world's Population code and make that many more D66 rolls on the Trade Goods chart and for each result, which may be the same item multiple times. And those illegal goods may come looking for you, but you don't have to buy them.
For each positive result, roll the 'Tons' amount of goods - that is sort of a 'lot' if you hit the same item multiple times as a result of the random roll..

For Speculative trade, it's not all or nothing like a freight lot, but whatever amount you choose to buy.
(But I house rule it that you can't break down an item below the die multiplier (20, 10, 5, or 1), because containers)

Ah. Yeah, I think a flow chart might have made me understand it better. My eyes seemed to skim over important parts of that little section multiple times and I don't know why. Thanks for the detailed explanation. I appreciate it.

 

[Terry Mixon]

I found an error in the calculation of Purchase/Sales DMs. When I was looking at radioactives (what is it with me and radioactives?) and selling to Sorel where they are Ni (-2) and Ag (-3), the result should have been negative (is it the best of the two -2/-3 or the worst? Seems like it should be the worst.) What I got was 0.

Looking at the formula in the hidden area =MAX(IF(P11,3,0),IF(P9,1,0),IF(P15,-2,0),IF(P2,-3,0)) the first two options are false, so 0. MAX takes 0 as the highest value and the negative DMs are lost.

In addition, going to the Sales DM in the main chart for speculative goods, this is the formula: =$V$2+W30-V30

It takes the difference in broker levels between the buyer and seller, adds the Sales DM and subtracts the Buy DM. A negative number in either will screw this up when the hidden area is fixed.

The above also will affect the Buy DM in the main chart.

 

[Arkathan]

I will have to think on that. In a vacuum, fixing that line is easy. However, it is a piece of a larger calculation and not taking zero ADDS 2 to the DM. So the totality of the calculation string needs to be rethought.
Trying to retrace thought processes from two years ago is a pain. Today, my mind is not wrapping around it.

 

[Terry Mixon]

I will have to think on that. In a vacuum, fixing that line is easy. However, it is a piece of a larger calculation and not taking zero ADDS 2 to the DM. So the totality of the calculation string needs to be rethought.
Trying to retrace thought processes from two years ago is a pain. Today, my mind is not wrapping around it.

Understood. I just added more to the post. Thanks for what you're doing. It'll make my life so much easier as a GM.

 

[Terry Mixon]

Thinking about it, in the sheet, the purchase DMs in the main area are currently calculated using the origin world and selling DMs using the destination world. That needs to be looked at as well as I think Buying and Selling DMs should be calculated for BOTH the origin and destination separately as using the destination DMs should have no effect when purchasing, and vice versa.

The logic being that the purchase and sales DMs on the purchasing world, both reflect conditions there that might reduce the purchase DM (or increase it, I suppose). Glisten has a +2 for purchasing radioactives (there they are again!) because it has the asteroid trade code, and they are easier to get. It also has a sales DM of 3 because it is industrial. That changes the purchase DM to -1 when the sales DM is subtracted because the local industry is consuming the output and is hungry for more. The same logic holds on the destination world.

I modified the sheet I downloaded to add two extra columns for the origin side sales DMs and the destination side purchase DMs, and altered what the total DM calculation was pointing to in the main area for testing purposes and it looks like it fixes that part.

 

[Terry Mixon]

I will have to think on that. In a vacuum, fixing that line is easy. However, it is a piece of a larger calculation and not taking zero ADDS 2 to the DM. So the totality of the calculation string needs to be rethought.
Trying to retrace thought processes from two years ago is a pain. Today, my mind is not wrapping around it.

Hey, it's been a couple of weeks so I thought I'd check back in to see if your brain was being more cooperative.

 

[Arkathan]

Brain yes, Life no.
Been dealing with a frivolous lawsuit from a litigious inmate that is currently over five years old. Coincidentally, I've been retired for nearly that long.
Since the legal system does not charge him money for making false allegations, and instead appoints lawyers to assist him, the fact that he has no case does not deter him from a campaign of harassment while he sits in prison for at least another ten years.
The inmate did not like an agency policy that prevented inmates from getting mail soaked in K2 and Raid (yes, Raid - we had eleven inmates go to the hospital in one night from a bad batch, and in lieu of dying, one inmate admitted to what they'd been smoking) so he sued me personally over the agency policy, and a liberal appellate judge reversed the sane judge's decision and allowed the suit to continue. At least the judge DID drop the part where we took his cane after he checked himself out of the medical dorm against medical advice. (Can't have weapons in general pop. That's why we have a medical dorm so that they can have those accommodations).
TMI, I know, but that is what is keeping me from attacking more important problems like RPG trade and skip and hop drives in the ship designer.