[REL] Trade Price Spreadsheet

[liam1]

Hello all,

First off, I hope I have posted this in the correct area.

I thought I would make my trade catalogue available to the community.

The game doesn't allow any way for the player to compare the trade prices of two settlements together, I found this quite annoying as it would take a good while to keep switching from one settlement's trade price sheet to another in order to find what item will bring in the most money.

My solution was to create this Excel document that doesn't just compare the two prices but calculates which items will bring in the most profit per unit and per cwt.
I find this file very useful as I like playing as a trader, I just hope that there are others who will find this file as useful as I do.

Download: Version 0.25 TradeCatalogue.xlsm (529KB)

Screenshots:

The main screen: http://screencast.com/t/ZCUvBtqrmzs
Settlement price screen: http://screencast.com/t/J3NpqNDCEaN
Main Menu: http://screencast.com/t/wF23j2ob

Instructions:
This file is created in Excel 2007 and is saved as a xlsm file. This is a macro enabled Excel workbook.
Every time you open this file you may be prompted by a security bar with an options button, please click this options button and select the "Enable This Content" radio button.

In order for the file to work you first need to fill in some data, unfortunately I am unaware of a way that the prices can be exported from the game so you will have to type them in to each settlements list.
First click the menu button and then select the settlement that you want to update, I have grouped them by the nation that owns them so that finding a settlement is a little easier.
Once in the individual settlement's catalogue just replace the zero's with the relevant price, the Buy column is for the cost of buying from the trader and the Sell column is for the money that the trader is offering you.
Please put zero's into the items that are contraband doing different will probably break the calculator.
The date cell is for you to enter the in game date when the price list was last updated, this is obviously useful so you know how old your current list is.
Once you have prices in at least two settlements you can go to the main screen and select the departure and arrival ports from the drop down lists.
the Profit per cwt and Profit per Unit columns will now fill up and you will presented with the Profits that can or cannot be made for each item.

I believe that covers the basic usage of the file.

All the item names and weights are linked to one sheet so that changing the order/names/weights of the items are not too difficult.
In order to alter this information please click the Goods button from the Menu.

The calculator does not yet support adding more items and removing items will just be done by leaving their prices on 0.

I may have missed some obvious information but this is the first time I have uploaded anything to anywhere so I apologise that I am new to this writing instructions business.

Additional but important information:
This file has not been tested very much beyond my normal use of it, if you find any bugs please let me know and I will try to sort them out.
Any other suggestions are also welcome.

This file is set up to fit the AOP:CoAS GOF1.1 mod with realistic weights and costs enabled, so all the items and weights are, as default, set to fit this set up, as mentioned in the instructions the names and weights of items can be changed by clicking the Goods button in the Main Menu.

I don't think I have missed anything out but if there is anything that you get stuck with please let me know and I will update these instructions accordingly.


I hope you enjoy this Trade catalogue and I hope it is as useful for you as it has been for me.

Thank you for reading and downloading
Liam1

 

[Officerpuppy]

I've moved your mod to the appropriate forum.

No brothel rates? :

 

[buho]

There is a way to export the trade data, but with a lot of caveats:

a) A lot of code need to be written. It can't be done before GOF 1.2 comes live due to compatibility reasons.

b) The code will be exported to the logs files where the error and trace messages are sent too. The exporting will polute the logs files beyond imagination (and they are polluted just now).

c) I'm not sure the data can be recoverd with Excel, probably we'll need to write an external program to get the data ready to be imported to Excel. (See below.)

An in-game interface to make what you are doing with Excel is in my "dream list". But it is a very long take, I have a very long "to-do list" which comes before my "dream list", and this particular mod calls for a lot of new screens wich are horrible cumbersome to make.

If some day I manage to get to that point of my dream list I'll get in touch with you, I can write the code based on your know-how. But is not going to be next week or even next month.

Or may be after GOF 1.2 coming live we can try to put up a way to export the data. Are you able to program Excel in VB? To import the data we need to:

1) Open a text file and check line by line to pick up the trade data, wich will be mixed with the other info the game is logging

2) Parse the lines we picked up.

3) Preferably delete the trade data from the log file to keep it clean for the modders (who rely on it).

- o -

Nice work! The trader aspect of COAS calls for a lot of care.

Cheers.
buho (A).

 

[liam1]

Thank you officer puppy, I was contemplating putting it in the modding section but then I thought it wasn't really a mod but thank you for moving this to the correct forum

To buho:
My knowledge of VB and VBA is somewhat old now, haven't really used VB properly for about 5 years but I am sure I can figure out and get all those points working.

Obviously getting the export to work and how easy this would be would depend on the formatting of the trade price within the log file.

I have done similar things before but I don't think I have ever then removed the data, but again I imagine I can figure it out.

 

[buho]

The parsing will be easy, say a line format like:

#TRD-EXP #town #item #buy #sell

Where:

- #TRD-EXP: the prefix to pickup the line.
- #town: town name.
- #item: item (good) name.
- #buy: buy price.
- #sell: sell price; -1 for contraband.

EXAMPLE: #TRD-EXP #Charlestown #Rum #5 #7

File processisng:

1) Load all the file in a string list/collection (most string lists have a method to load a whole text file in one swop).
2) Loop (with while, we are going to delete lines and change the lines total) looking for lines starting with #TRD-EXP:.
2.1) Parse the picked line, searching the # markeres and cutting the values. Save them in some data structure (see below).
2.2) Delete the picked line from the list.
3) After ending the loop, overwrite the file with the list/collection (again, most strings lists have methods to save all the list as a text file).

How the system works:
- We assign a key in COAS to trigger the internal exporting code.
- After hitting the key, the player TABs out and pops the Excel sheet.
- Excel opens the file, imports the code, do the clean and produces the data sheets.

Or something like that, take or give a few details.

Note: the importing code needs to cope with the likelihood of the player hitting the exporting key more than one time before importing. If the importing code parses the lines and collects the data in another collection/array indexed by town and item the problem is solved: when the code finds the town for 2nd time (or 3th time or whatever) it overwites all the old items data. There is no dups and the final values are the last ones.

Lets wait for GOF 1.2 coming live and we can start working on it.

Cheers.
buho (A).

 

[liam1]

buho,

I have been thinking and I do not believe getting the information out of the text file to be difficult.

The only problem that remains is saving the log back to the program files location. Having never programmed like this for Windows 7 I do not know how standard UAC controls that prevent the user from saving to program files will affect the re-saving of the log file.


By the way I have updated the spreadsheet, got rid of a few bugs and added a "Swap" button so you can easily swap the arrival and departure ports around.

 

[buho]

It is not advisable to have the game in Program Files or any other Win restricted area.

I'm not sure if the game can even write a log being there.

And you are right: if the player have the game there we can't change the file.

Cheers.
buho (A).