Software or Spreadsheets for automating speculative trades

[ericphillips]

Hi:

I love speculative trading, but we are about story and want to spend a little less time rolling and more time doing other stuff around the table. Is there any software or spreadsheets (Excel, OpenOffice or Google Docs) that automates the whole "roll to find a supplier, roll to find out whats there, roll to find the sale price...etc." routine?

Thanks for the look out.

 

[AndrewW]

Might take a look at this thread:

http://www.mongoosepublishing.com/phpBB2/viewtopic.php?t=39482

 

[BP]

Such would probably require MGP approval... (Yes - even if it is given away - the SRD specifically doesn't apply to software). Its ok to create for personal use and not distribute... its not too difficult a thing even without programming - i.e. simple formulas in Excel will do the job.

To do a 1d6 simply paste the following in a cell (including the equal sign):
=1+FLOOR(RAND()*6, 1)
Edit any other cell (press delete or type and press enter/arrows) and unless you've turned off auto-recalc the roll cell will refresh (F9 also causes I believe).

Likewise, d66 would be
=((1+FLOOR(RAND()*6, 1)) *10) +(1+FLOOR(RAND()*6, 1))

Make yourself some tables - like a trade goods table with d66, trade good, tonage multiplier, base price... then use
=LOOKUP(d66 cell, lookup cell range - i.e. 11 to 66, result range - i.e. Basic Electronics to Exotics)

Just using the first two formulas and the LOOKUP function along with multiply will say select random goods. I.e.:
=1d6 cell *LOOKUP(d66 cell, column cells 11 to 66, base price cells 10,000 to X)
Where I used X as base price for exotics so price will show as #VALUE!, though a conditional (IF) statement could handle this more elegantly.

Some people like to put the lookup tables on a seperate sheets (bottom tabs) naming them for each table (ModPrice, TradeGoods, CommonGoods, PlanetGoods) - p.s. I seperated avail, purchase DM, sale DM colums for each world type (i.e. 3 per) plus added a common column with my trade goods table.

It may seem difficult - but its pretty simple really, just a bit tedious (I tested all this for this post it took me less time than writing this )...

 

[Deniable]

Such would probably require MGP approval... (Yes - even if it is given away - the SRD specifically doesn't apply to software).

The SRD is licensed under the OGL and doesn't preclude software. The TLL does exclude software. It only covers the use of the Traveller trademark and dress.

This thread covers most of it.

 

[ericphillips]

I am the original poster.

Thanks all. I've been working with excel. I learned about LOOKUP even before you posted, thanks for the help. I am not a guru, but it is looking kinda good.

So far I have:

A sheet which gives me the selling price of speculative goods. I didn't automate the selection, so you have to put a "y" if it is in stock and the total rolls for an item if your roll more than 1. Then you put your broker, world into in, and so on, and it gives the selling price for all the items using random rolls from that broker. I think its sweet. I also did not put in any of the trade variables for Current or Destination planet, so I have to do that, but overall its like 20x as fast!

So then I made a sheet that generates the buyer prices.

So, then I got more into it: I made a sheet that you enter population and other info, and it does all the generation of passengers, freight, and if you have mail or not.

(I learned the IF command today, so it says "yes" or "know" depending if the mail roll is 12 or more. This is the coolest thing to me).

Next I am going to make a spreadsheet to keep track of the ship finances, which the crew can use. I was even thinking of using the spread sheet to assist with Space Combat.

I never knew Excel was so cool.

Anyway, I would like to post what I got so others can help to use themselves, or to make better. Any idea where?

 

[FreeTrav]

Anyway, I would like to post what I got so others can help to use themselves, or to make better. Any idea where?

Freelance Traveller's web site can host your sheet, if you like - and separately, if you like, we'll take an article about your development thereof and techniques that you - or others - can use for building various spreadsheets of your/their own...

 

[CosmicGamer]

Anyway, I would like to post what I got so others can help to use themselves, or to make better. Any idea where?

People have been sharing stuff at http://games.groups.yahoo.com/group/MGT-Aids/

 

[BP]

Such would probably require MGP approval... (Yes - even if it is given away - the SRD specifically doesn't apply to software).

The SRD is licensed under the OGL and doesn't preclude software. The TLL does exclude software. It only covers the use of the Traveller trademark and dress.

This thread covers most of it.

Whoa - I stand gladly corrected - many thanks Deniable! I had followed that thread, but missed its conclusion - specifically this:

Ok, I finally have an official response form Mongoose about software.

Using the OGL (and nothing but), you can do pretty much what you like, so long as you don't mention Traveller at all. And we would be okay with that.

Awesome! I found the faqs here very helpfull as well!

 

[ericphillips]

Okay, you guys that know Excel. How do you do this?

First I use rand to roll all the variables on my "for sale" list.

I would then like to have a column where the PCs enter the tonnage they are buying and it calculates the total Cr for the tonnage.

My problem is that if they enter the tonnage they want to buy, the whole sheet executes again, changing all the prices. How do i make it so it only calculates th inventory part, but not recalculating the prices?

thanks

 

[BP]

...My problem is that if they enter the tonnage they want to buy, the whole sheet executes again, changing all the prices. How do i make it so it only calculates th inventory part, but not recalculating the prices?

Turn off auto-recalc - which is version dependent - look in the help... (older versions use tools|options from menus and calculation tab - change to manual). Then use F9 (to manually recalc - see help for shift/ctrl variants) or a button on the sheet (such as VBA script...).

Warning - excel can seem powerful - but such easy power comes with caveats - don't get discouraged! Keep in mind its already doing more than you knew it could do before...

 

[ericphillips]

I did that, but then if I put the lot in and want it to multiply the lots by the Cr to get the final price i need to F9, which makes all the rands happen again.

I might just copy it as absolute values to another sheet, paste the values in, and have that sheet for record keeping.

Yep, Excel is deep, but it is pretty damn cool!

 

[BP]

I did that, but then if I put the lot in and want it to multiply the lots by the Cr to get the final price i need to F9, which makes all the rands happen again.

I might just copy it as absolute values to another sheet, paste the values in, and have that sheet for record keeping.

Yep, Excel is deep, but it is pretty damn cool!

I believe Shift-F9 (or some other combination with Ctrl) avoids the whole workbook being recalced - put things on different worksheets (as you mentioned above) and then just recalc by worksheet.

Later you might use macros/VBA scripts...

[Ironically, in my professional life - I consider Excel crap! No telling how much time I have spent correcting the multitude of mis-uses others have applied it to over the past decades! But, enjoy - and I'm more than happy to assist where I can!]

 

[Sturn]

...... Its ok to create for personal use and not distribute... its not too difficult a thing even without programming - i.e. simple formulas in Excel will do the job......

I know little about Excel and have switched to OpenOffice for almost everything a year ago. Does OpenOffice's version of Excel run Excel spreadsheets that include code? Does your (BPs) brief rundown of Excel code work within OpenOffice's program if I decided to make my own?

 

[Duroon]

Open Office does some great stuff, but it doesn't handle VBA code well.

 

[BP]

...I know little about Excel and have switched to OpenOffice for almost everything a year ago. Does OpenOffice's version of Excel run Excel spreadsheets that include code? Does your (BPs) brief rundown of Excel code work within OpenOffice's program if I decided to make my own?

The cell functions I listed should work in OpenOffice Calc (these are not code) - Excel macros use VBA (Visual Basic for Applicaitons) code.

So my examples above - which are simple cell functions - should work just fine.

I try OpenOffice every year just to stay in the know (since it was SUN's StarOffice) - but don't use it and have never encountered a client that did. It should support almost all the Excel cell functions - but it won't support Excel macros directly to my knowledge - the StarOffice had its own BASIC - however, conversions for most things should be pretty straightforward (I'm sure several programs exist to do this - though I wouldn't trust a 100% transfer - we are talking rudimentary programming).

I'm not a big fan of the 'spreadsheet in place of a program' concept - but the first corporate programmer I worked with got his introduction via Lotus-123 - so I don't want to discourage anyone...

 

[ericphillips]

I have completed four sheets. I am going to upload them to the Yahoo group when I am accepted.

I have found they do not work in Google Docs, I tried, but all the LOOKUP functions fail, a function that is used on my sheets a ton. It is a command that lets me look up results from an array. Sorry not Excel people.

 

[CaptnBrazil]

I'm actually testing my software for trade. It's CSV-file based and supports Classic, T5 & Mongoose trade (with the exception passengers so far, and the question I have in the rules forum). There's 1 XML-based file that contains the ship info (tonnage, version, trade DMs, cargo manifest, etc) but that too is plain text, if XML-formatted. I even added an RSS feed (there's a built-in travelogue that records jumps, cargo purchaes & selling, and whatever additional notes you may want to add, indexed by date & system) & reporting to it - it's a test bed for my Traveller class essentially, something I just do for fun. It also requires an SEC file which is used for loading systems (and the appropriate trade codes based on your current ship's version info).

I may actually release it with the instructions on creating the tables. The support tables (trade goods, actual price, etc) are all CSV files, one per version. So I think the loophole for releasing it would be that you have to write the actual tables for use, and that should require the core books. However - the rules ARE built into the program, so it may not be possible to release it.

It does require Windows and .Net 3.5, but that covers a big chunk of people.

 

[rust]

I'm actually testing my software for trade. It's CSV-file based and supports Classic, T5 & Mongoose trade ...

Please be careful with T5 material, you could get yourself into trouble by
publishing even parts of it without the consent of Marc W. Miller.

 

[CaptnBrazil]

I'm actually testing my software for trade. It's CSV-file based and supports Classic, T5 & Mongoose trade ...

Please be careful with T5 material, you could get yourself into trouble by
publishing even parts of it without the consent of Marc W. Miller.

hence my inability to release it - although it needs the external tables for trade goods & codes, so I'm thinking I could release it with the files cleared out except for the headers. You would then need to have the rules in order to write the tables for the program.

The trade codes are generated from the appropriate trade class file (even CT which only has 6 trade codes, so it is a small CSV file!), and the goods are all table based, including any built-in modifiers. While the trade code classification tables are the same format (I normalized them) the differences in trade for the 3 versions mean that the trade goods for each version are somewhat different. Same idea, but require different fields. All are still CSV/text, though. Allowing the end user control over pretty much everything.

I'm just not sure if releasing it w/o the actual data tables is ok. I've posted the question on this & the (*gasp*) COTI forum but have not received any responses either way. So it will probably never get released, but hey - it's a hobby program that I'm doing just for fun & improving my object oriented stuff (the main program just calls for searching for cargoes, and passes along the ship & world objects. The trade thing then calls the appropriate trade stuff according to the version from the ship file, and the world you are on. In CT/T5, you get a single cargo back [actually, a list but only 1 cargo object in the list] and Mongoose you get all the common, trade code-matching & the 1d6 random goods back. Just playing with separating out the logic form the UI). So there is nothing in the main program that even cares about the version, it is all handled in the class object. So you could have 3 identical ships, one for each version, and run the same routes & compare the trade results. Heck - as the the Traveller class has classes for the worlds & trade, you *could* write a program using that same class & run an iteration to automate testing...hmmm....sorry - programmer thought progression!)

 

[CaptnBrazil]

see http://webpages.charter.net/coliver988/TravDoc/output/index.html for the current class documentation to see how I handle stuff. No actual code/rules in that - just the methods & data structures.