Traveller Trade Tool

[myrmidon009]

Some replies to Terry Mixon:

• I figured out what the strikethrough is. It's an Excel setting, not the tool. When calculations are set to manual, the strikethrough indicates that the formula in the cell is no longer up-to-date. That is okay in this case, as we want to control when the workbook recalculates formulas. If you want to recalculate a sheet or workbook, you can just use those commands on the 'Formulas' tab at the top.
• Great catch on the 'Sale Mod' formula! I've got that fixed for the next version.
• Also good catch on the starport data. I will get that updated. That may take a day or two. I'll have to write some code to do that.

Also, that's a pretty cool use of ChatGPT.

 

[Terry Mixon]

Some replies to Terry Mixon:

• I figured out what the strikethrough is. It's an Excel setting, not the tool. When calculations are set to manual, the strikethrough indicates that the formula in the cell is no longer up-to-date. That is okay in this case, as we want to control when the workbook recalculates formulas. If you want to recalculate a sheet or workbook, you can just use those commands on the 'Formulas' tab at the top.
• Great catch on the 'Sale Mod' formula! I've got that fixed for the next version.
• Also good catch on the starport data. I will get that updated. That may take a day or two. I'll have to write some code to do that.

Also, that's a pretty cool use of ChatGPT.

Thanks. I’ll hold off on further poking about until the next iteration comes out. It’s pretty cool. I feel the stirrings of a few suggestions but nothing firm enough to express yet.

 

[myrmidon009]

Here is v1.1. The major changes are as follows:

• Added some clarifications to the 'Instructions' tab.
• Moved the modding instructions to the 'Modding Instructions' tab, which is hidden. Added details and illustrations for clarity.
• Fixed the labels for freight on the 'Sales' tab.
• Corrected the formulas for the sale price mod on the 'Trade Goods' tab.
• Corrected the starport data on the 'System Info' tab.
• Added formulas and an example on the 'System Process' tab. The formulas were missing in v1.0.

Thanks to Terry Mixon for finding the errors and his suggestions.

 

[Terry Mixon]

Most excellent. I’ll look at it once I get back home.

 

[Terry Mixon]

On the Input tab, in the selling phase, it should say Sell on Black Market, not purchase.

 

[Terry Mixon]

On the Trade Goods tab, the sale DM includes the PC broker skill as an add but does not subtract the NPC broker skill. The same is true for purchases. Shouldn't it?

Also, illegals reference the broker skill rather than streetwise.

 

[Terry Mixon]

For the purchase DMs, you have this:



That is ORG Sell DM - ORG Buy DM - PC Broker Skill.

What it should be, I believe, is ORG Sell DM - ORG Buy DM + PC Broker skill - NPC Broker Skill.

The same for illegals but use streetwise.

Sell DMs need similar updating.

 

[myrmidon009]

Here is v1.2. The major changes are as follows:

• Corrected the label for selling on the black market on the 'Input' tab.
• Terry Mixon, the formulas for the DMs are correct. If you look in the 'Roll' columns on v1.1, you will see that it uses all four DMs. The 'Known DM' columns are where the values of the DMs that the players know are calculated. However, it wasn't quite clear which DMs were being used. I added 'Total DM' columns and have the 'Roll' formulas use that instead, to make it more clear. I also added notes for the 'Known DM' cells to clarify what those values mean.
• Corrected the formulas for illegal goods to use Streetwise instead of Broker.

Thank you for the review!

 

[Terry Mixon]

Here is v1.2. The major changes are as follows:

• Corrected the label for selling on the black market on the 'Input' tab.
• Terry Mixon, the formulas for the DMs are correct. If you look in the 'Roll' columns on v1.1, you will see that it uses all four DMs. The 'Known DM' columns are where the values of the DMs that the players know are calculated. However, it wasn't quite clear which DMs were being used. I added 'Total DM' columns and have the 'Roll' formulas use that instead, to make it more clear. I also added notes for the 'Known DM' cells to clarify what those values mean.
• Corrected the formulas for illegal goods to use Streetwise instead of Broker.

Thank you for the review!

My pleasure. I'll let you know if I have more questions and comments. I'll also share it around on Facebook, with proper attribution, of course. Thanks for the tool.

 

[Terry Mixon]

I will reiterate that I think you should move the "Tons Sold" column from the purchases tab to the Sales tab. You're wanting people to commit to a sale without seeing the price they are offered.

 

[Terry Mixon]

You might want to verify that the sheet uses the manual override rolls correctly. I set them and it didn't seem to matter as I think it was still using the rolled numbers.

 

[Terry Mixon]

I messed around with moving the Tons Sold column to the sales tab so it wasn't a blind purchase.

Here is my suggested purchases tab.



Here is my suggested sales tab.



I added Total Tons, but all it does is link back to the column of the same name on the purchases tab. As I cut and pasted, the references to the Tons Sold column updated. I removed the now duplicate Tons Sold column that was there already and updated the Sales column to get the new column in its place.

It's a minor tweak, but it puts the sale where it needs to happen and the seller can see what they are being offered for the cargo without changing tabs.

The only thing I can't figure out is what is updating the Tons Remaining column. It has to be a macro or something. The rest works as I want it to.

 

[myrmidon009]

Here is v1.3. The major changes are as follows:

• Added check to see if jump distance exceeds ship's jump range on 'Input' tab.
• Added way to indicate manual generation of trade goods on 'Input' tab.
• Added conditional formatting on 'Purchases' tab when manually generating trade goods.
• Moved 'Tons Sold' to the 'Sales' tab; also added other information that would be helpful when selling.
• Updated formulas on 'Trade Goods' tab for manual overrides.
• Updated macros to reflect the above changes.

To address specific comments:

I will reiterate that I think you should move the "Tons Sold" column from the purchases tab to the Sales tab. You're wanting people to commit to a sale without seeing the price they are offered.

Fixed. I like your solution for the 'Tons Sold'. It shouldn't be on the purchase sheet, because you shouldn't know the sale price when you buy it. But you should know the sale price when you sell it. Moving 'Tons Sold' to the sale sheet fixes that.

You might want to verify that the sheet uses the manual override rolls correctly. I set them and it didn't seem to matter as I think it was still using the rolled numbers.

This should be fixed as well. I also added some additional capability for manual calculations.

The only thing I can't figure out is what is updating the Tons Remaining column. It has to be a macro or something. The rest works as I want it to.

This was done via a macro. It's now a formula, so it's updating with the rest of the sheet.

Thanks for looking it over.

 

[Terry Mixon]

Another suggestion. I added a "potential profit per ton" column on the sales sheet to give an idea of what the profit was. (On reflection, the word potential could probably come out.)

I also had to link a new "purchase price" column to the purchases tab to get the data I needed displayed for a potential sale. No more math in the head about profit/loss. It checks to see if there is any tonnage of each one and shows 0 as the potential profit for things you don't have to avoid cluttering things up.

I just can't help tinkering.

 

[myrmidon009]

I just can't help tinkering.

Please, tinker away! I hope people adjust this to best fit their needs.

Another suggestion. I added a "potential profit per ton" column on the sales sheet to give an idea of what the profit was. (On reflection, the word potential could probably come out.)

I'll have to think about adding that. My hesitation is that the listed purchase price may not represent the price at which they actually bought the goods. For instance, in the campaign I'm running, the players bought some goods but ended up not selling them in the next system. So they have these goods that have been sitting in the hold (for a couple of jumps, actually). If they went to sell them, using the purchase price from the current purchase system wouldn't be an accurate way to calculate their profit. I would like the information presented to be accurate for any sale.

 

[MasterGwydion]

Please, tinker away! I hope people adjust this to best fit their needs.



I'll have to think about adding that. My hesitation is that the listed purchase price may not represent the price at which they actually bought the goods. For instance, in the campaign I'm running, the players bought some goods but ended up not selling them in the next system. So they have these goods that have been sitting in the hold (for a couple of jumps, actually). If they went to sell them, using the purchase price from the current purchase system wouldn't be an accurate way to calculate their profit. I would like the information presented to be accurate for any sale.

They would know what the base sale price is, modified by the planet's trade codes. What they won't know is what the final sale price will be after the die roll. They would also know what they paid for the goods. They will know how far away the planet they want to sell the goods at is, so they will know (roughly) what their expenses will be. This gives them a basic idea of how much money they will make, if the opposing broker has the average of +2 and the average 3d6 of 10 (to estimate on the low side). The reality will be a bit different depending on how they roll and depending on if there are other factors of which they could not anticipate, but they should have at least some idea of what the average price would be.

Edit - Wait a minute. The purchase price actually paid on one planet has no bearing on what the base sale price for the trade good is on another planet. Remember, the players can decline to purchase trade goods if they don't like the offered price. Also remember that the sale price doesn't go up just because the goods spent longer in the PC's cargo hold. Given the same rolls, those same goods will be worth the same sale price having never been stored, as having been stored for 3 months. The only thing that will change is their profit margin. This is why people shipping things get rid of the goods as soon as possible. Paying for "storage" costs money that cannot be recouped in the simple Traveller system. Meaning that, the longer the PCs just hold that cargo in their cargo hold, the less profitable their cargo hold will be.

 

[Terry Mixon]

Please, tinker away! I hope people adjust this to best fit their needs.



I'll have to think about adding that. My hesitation is that the listed purchase price may not represent the price at which they actually bought the goods. For instance, in the campaign I'm running, the players bought some goods but ended up not selling them in the next system. So they have these goods that have been sitting in the hold (for a couple of jumps, actually). If they went to sell them, using the purchase price from the current purchase system wouldn't be an accurate way to calculate their profit. I would like the information presented to be accurate for any sale.

Maybe an override to allow them to enter their own price? I suspect most users will be buying and selling in a single run of the sheet, so it makes sense to allow for that.

The problem with me modifying it for myself is that I broke something. Probably a macro. After my changes, the sales price per ton no longer updates and I don't even know enough to troubleshoot it.

 

[Terry Mixon]

If you don't end up adding that functionality, can you look at the one I changed and tell me what is screwed up so I can start learning what I need to do going forward? Macros are an unknown to me and I'm sure that in adding columns I screwed it up as the sale price per ton doesn't change anymore. Help!

Yeah, it's pasting the sale price where the sales DM goes and that is because I added a column. I can't make head or tail out of the macro to even know what to experiment with to try and change it to the correct column. I updated my copy to put the DMs back to the values they should have been and made the one column Profit / Ton to match everything else.

Seriously, a little primer on the macros would help for those of us who have no clue what the specifics mean. Or an inline comment on what each line does and a pointer that says change here to add columns, moron.

Documentation, man! Documentation!

 

[MasterGwydion]

If you don't end up adding that functionality, can you look at the one I changed and tell me what is screwed up so I can start learning what I need to do going forward? Macros are an unknown to me and I'm sure that in adding columns I screwed it up as the sale price per ton doesn't change anymore. Help!

My Excel Fu is somewhere around the skill level of a toddler, so I feel your pain. lol...

 

[Terry Mixon]

My Excel Fu is somewhere around the skill level of a toddler, so I feel your pain. lol...

I've learned a lot messing with @Arkathan's ship design sheet, but marcos are new to me and looking at them, I went "Wut?"