Jump to content
Game-Labs Forum
Remus

Crafting Data and Calculator

Recommended Posts

Updated to Build 11 - but with errors (see first paragraph below link)

I've put crafting data into a spreadsheet:

Naval Action Crafting Data 11.xlsx

This patch has changed the calculation for ship trim ingredients, with ballast (but possibly not all of it) now being moved to the main recipe. If someone cares to post blueprints for a couple of ships in every trim, I'll see if I can work out what the new calculations are and correct the spreadsheet.

Taxes have been added in build 11. I haven't incorporated this into the spreadsheet. Everything in this post below this point is unchanged for the last few updates.

It's quite basic; the only calculations are on the first 'Calculator' sheet; the rest is just data. I think most of it is self-explanatory, but I'll describe each sheet below. I've saved the file in Excel .xlsx format; the Calculator sheet almost certainly won't work with OpenOffice as I use Indirect functions. There is a way round this which I may incorporate in a future update, but really if you find this sort of thing useful just pick up a cheap copy of Office on ebay and spare yourself - and people like me - the pain of using OpenOffice and ensuring compatibility. If you use an old version of Excel and want a .xls copy, just ask.

I've got a semi-automatic way of updating this sheet from the API - it's too convoluted to share, but it does mean I should be able to keep up with patches and hottfixes as they are released.

The sheets are:

  • Calculator. Primarily to calculate costs and how much you should charge for things, but it provides basic information on material requirements too.
    Write in the yellow cells. The four at the top are for adding your own mark-ups. I currently use 100 gold per labour hour so this is how you'll find it set, but you can use whatever you like.
    Make, Wood and Trim are all drop downs, and remember to type in the quantity you want to calculate. Wood and Trim are ignored if you aren't making a ship.
    Labour Hours you know all about. Building Hours are the real life hours needed to harvest all the craftable resources in a level 1 building. Crafting costs are those tied up with blueprints, mostly raw material harvesting but some upgrades have blueprint costs as well. Purchase Costs are based on the purchased ingredients (eg sabicu logs) base prices. The price you actually have to pay in a shop may vary, but if you buy from a seed port it won't be much different from the base price. There is currently no provision to enter actual purchase costs for items, but I may add this later. Note: Permits are free so far as this sheet is concerned - please add whatever value you wish to give them manually. The Total Cost column apples the mark-ups and the total in red is for the whole quantity. I have now added 5% and 10% markup boxes in the price for 1 row to cover port listing fees (10% for ships, 5% for everything else).
    The two tables below show the materials needed. The one on the left is a copy of the blueprint you see in the Craft tab in game, set for whatever quantity you want to calculate (which can be less than the blueprint quantity). The table on the right is the ultimate requirements in raw materials. Towards the bottom of these tables, the wood and trim materials are listed separately as I could not find an easy way to add them in with the main ingredients in a spreadsheet (in the database it is easy!)
    Ignore the numbers in grey, they are to help collect the correct data.
  • Items. Weights and base prices. These are from the API. There is duplication and a number of obsolete items appear in this list (I have tried to cull them from later sheets). If you don't know what something is, well, I dare say neither do I.
  • Crafting costs. This is what you see in the Calculator sheet without mark ups applied. For ships, it does not include wood and trim. I use this sheet as much as the calculator for a quick look up. All values are to make one of the item. As with the Calculator sheet, permits are not given any value.
  • Wood and Trim Costs. Like the previous sheet but for ship woods and trims.
  • Blueprints. These are actual blueprint values, straight from the API. Ingredients are on the next sheet, and woods and trims come later.
  • Ingredients. These are for the blueprint quantity, exactly as you see in game and also comes straight from the API,
  • Base Ingredients. This is going down through any lower level blueprints till you reach the base resources. These are per one, not per blueprint quantity.
  • Wood and Trim Multipliers. Needed for the next sheet. Wood multiplier is in the API (though not written as such). Trim multiplier is the ship mass.
  • Wood and Trim Ingredients. Multiply by the appropriate multiplier in the previous sheet, and if necessary round down. Trim ingredients aren't in the API at all, but I am pretty sure now these are correct. If someone has a first rate blueprint perhaps you can check.
  • Wood and Trim Base Ingredients. Just like the Base Ingredients sheet.
  • Buildings: You can work this one out, I am sure.
  • Building Materials. This one too.
  • Building Loot. This is from the API, but don't ask me how the last three columns are applied.
  • Marks Exchange. From the API. I am not sure everything here is in game, and some are PvE server only

Comments and suggestions welcome. Enjoy.

Edited by Remus
Updated to 11
  • Like 27

Share this post


Link to post
Share on other sites

 

[Remus] are you and [BLACK SPAWN] in a "Top God" off competition...? 

You two are seriously on fire...

 

Norfolk nClue

(VLOOKUP expert :( )

  • Like 1

Share this post


Link to post
Share on other sites

I've now updated the spreadsheet to 10.1 Hotfix 2 and incorporated a couple of very minor improvements.

The only changes for crafting that I can see are:

  • Canvas Rolls BP changes from 2 LH to 1 LH
  • Planks in all woods, LH reduces by about 1/3
  • Long and Medium guns up to 18 lb have reduced ingredient quantities::
    - 4 lb reduced by 50%
    - 6 lb reduced by 40%
    - 9 lb reduced by 30%
    - 12 lb reduced by 20%
    - 18 lb reduced by 10%
  • Carronades up to 24 lb have reduced ingredient quantities:
    - 12 lb reduced by 40%
    - 18 lb reduced by 20%
    - 24 lb reduced by 10%

There are the usual seemingly random changes to 'modules' with some items vanishing and others re-appearing after an absence, with a few brand new ones thrown in. I'm particualrly intrigued by TEST MODULE SPEED IN OW, and hope I get one drop from my next PvE kill :)

Enjoy!

  • Like 3

Share this post


Link to post
Share on other sites

Updated to yesterday's unannounced patch. Small traders now need carriages.

I've corrected a minor error I had in parsing the API, which may make it easier to check for changes in future as things won't go missing in the Items sheet.

  • Like 3

Share this post


Link to post
Share on other sites
1 hour ago, Jon Snow lets go said:

Hey Remus, thx for this massive list.

Ive noticed 2 very small mistakes tho: harvesting Iron Ore costs 60g (not 80g) and Oak log costs 73g (not 53g)

It's not my mistake, but the devs'. Base Price in the Items sheet comes straight from the API and appears to be what shop prices are derived from (though different items get different multipliers). It looks like it's meant to match the crafing cost, and for most items it does, but there are quite a number - mostly recent changes - which dont.

Use the Items sheet for weights and to get an idea of shop purchase prices, but I suggest you ignore it for anything else.

  • Like 2

Share this post


Link to post
Share on other sites

I've corrected an error. In the Calculation sheet, the Blueprint Quantities table did not divide ingredient quantities by the blueprint quantity. It does now.

  • Like 1

Share this post


Link to post
Share on other sites

love the spread sheet it helps me out a lot figuring out what to charge, though I'll admit right now with the way how things is no one is going to sale things for only a 5-10 mark up.   And you don't take into account the current market price of any of the marks for the ship permits.  The sad thing is I'm seeing folks post ships for way below cost and they are loosing money.   Than you have other folks charging 4 times the cost to craft cannons and than wonder why no one wants to buy them.

Share this post


Link to post
Share on other sites

I've given up playing but happened to see there was a patch today so I've updated the spreadsheet with new API data. I haven't checked what's changed.

On 20/06/2017 at 2:56 PM, Sir Texas Sir said:

love the spread sheet it helps me out a lot figuring out what to charge, though I'll admit right now with the way how things is no one is going to sale things for only a 5-10 mark up.   And you don't take into account the current market price of any of the marks for the ship permits.  The sad thing is I'm seeing folks post ships for way below cost and they are loosing money.   Than you have other folks charging 4 times the cost to craft cannons and than wonder why no one wants to buy them.

The 5% and !0% boxes are for sell contract fees (10% for ships, 5% for everything else). If you want to apply other markups use the yellow boxes at the top.

With the layout I am using it would be a major change to add values for marks and I doubt I will change it now (I have specifically excluded Permit costs so far as the Crafting Costs and Calculator sheets are concerned, even though they have a base price in the Items sheet).. However the spreadsheet I created for my own use before the wipe did allow this (and to add actual purchase prices for any item to use instead of crafting costs) but was harder to use. If I find time I'll maybe see if it can be adapted for the post wipe game, and put into a more user-friendly format..But don't hold your breath.

  • Like 1

Share this post


Link to post
Share on other sites

10.3 Hotfix 1:

There are no changes to anything except two of the Marks exchanges, so I haven't uploaded a new file. If you want to update your own copy, the changes are:

  • Furnishings was 50 for 300 Combat Marks; now 10 for 60 Combat Marks (still 6 marks each)
  • Labour Contracts was 1 for 10 Conquest Marks; now was 1 for 20 Conquest Marks (cost doubles)
Edited by Remus
  • Like 2

Share this post


Link to post
Share on other sites

@Remus again, thank you very much for your work. this great.

PS, disregard my questions on the old post you are no longer updating, I had saved an version of it and had questions about some of the data that this post renders unnecessary.

Share this post


Link to post
Share on other sites

Updated to 10.4

Build updates, so far as I can see:

  • Small ship ingredients reduced (Brig, Cutter, Lynx, Snow and Trader versions of the same - but not Pickle, Privateer, Navy Brig or Mortar Brig)
  • Wappen von Hamburg now has a blueprint
  • Blueprints for Ingermanland, Niagara, Rattlesnake and Rattlesnake Heavy (which were never removed, just made unavailable) how require a permit.
  • Blueprint for Trincomalee now requires a permit
  • Agamemnon now requires level 2 shipyard
  • There are changes to Marks exchanges which I have not attempted to identify

I have corrected the Marks Exchange sheet so blueprints now show.

  • Like 5

Share this post


Link to post
Share on other sites

Remus, i have a question for you, seeing as you are well versed in extracting info from the API. 

Do you know where i can find the speed and thickness stats on ships?

I see every ship template has a MaxSpeed entry, but it doesn't make sense to me. For example the Victory shows as MaxSpeed 140.976013183594

How do i convert that to the actual in game speed?

Thickness i can't find at all in the API.

 

Share this post


Link to post
Share on other sites
7 hours ago, Knobby said:

Remus, i have a question for you, seeing as you are well versed in extracting info from the API. 

Do you know where i can find the speed and thickness stats on ships?

I see every ship template has a MaxSpeed entry, but it doesn't make sense to me. For example the Victory shows as MaxSpeed 140.976013183594

How do i convert that to the actual in game speed?

Thickness i can't find at all in the API.

Other people have posted about API MaxSpeed, but I never really looked into it myself. I'd say the first thing to do is to take a few ships and compare MaxSpeed in the API with the in-game stats sheets to see if there is a multiplication constant. You'll need to remove effects of wood, trim, upgrades, knowledge and cargo. Since every wood and trim affects speed. probably the most reliable way would be to get a pair of ships with the same wood and trim, ensure they are empty (including guns), have no filled knowledge slots and no upgrades and divide in each case the API value by the stats sheet max speed. If both numbers are the same then you're off to a good start. You can probably then factor in the wood and trim enhancements to work out what the base multiplication constant is. It'll be around 12 from what I recall.

As for what this number represents, my guess is it is related to the units the game uses for distance and time. 1 knot is a nautical mile per hour but nautical miles (or minutes of latitude - it is the same thing) don't seem to tie in nicely with the x-z coordinate system the game uses for position (and therefore distance). It is also highly unlikely the game uses game-time hours for time measurement. It might use real-time seconds (or milliseconds or ticks) and then you have to allow for time acceleration, even in battle. I am sure you could while away hours trying out various combinations of units to get to a conversion factor to knots that matches the multiplication constant you've calculated.

Alternatively you might find there isn't a straightforward multiplication constant to get from the API MaxSpeed value to the stats sheet speed. Perhaps there is another value in the API you need to multiply by, which is different for each ship. Perhaps it isn't in the API. Perhaps the in game stats sheet is wrong (have people checked?).

As for thicknesses, I don't think these are in the API at all. For my crafting data spreadsheet, ship trim ingredients aren't in the API and I've had to work them out as best I can - but at least there seems to be a direct correlation with a value that is in the API (ShipMass); for thicknesses I cannot see anything likely, What you could do is take a pair of ships, find the ratio between thicknesses and see if there is another pair of stats in the API with the same ratio (this is what I did to find trim ingredients were based off ShipMass).

Have fun!

  • Like 1

Share this post


Link to post
Share on other sites

Thanks for your reply!

It seems that for this specific data the API isn't much use in reducing the amount of work of datamining... Looks like it needs to be doen by hand.

However after my post i did find out some very useful crowd-sourced work for the speed. link

In the end it's so simple i'm disappointing with myself to not have seen it... when you have a ship in port (or in the shop) the mouse-over text that says 'speed: 12.10 + 1.25" is actually the base speed. Same for thickness.

Share this post


Link to post
Share on other sites

Updated to 10.4 Hotfix 5.

Ship crafting levels and some Marks exchanges are the only real changes since I last updated the spreadsheet. The only blueprint change is the Rattlesnake Heavy now needing 5 fewer Provisions. The Wapen has lost a letter 'p' and some base prices have changed, but nothing of any importance that I can see.

  • Like 2

Share this post


Link to post
Share on other sites

Updated to build 11, but there are changes to ship trims I haven't incorporated. If anyone wants me to, please post blueprints of any two ships in every trim. Wood does not matter.

The spreadsheet does not incorporate taxes.

Building output changes significantly, both for base output and level 2 / level 3 multipliers which now vary between almost every building (including the level 3 stone mine which is exactly the same as the level 2 stone mine but costs you 150k for the privilege of owning one - surely some mistake. The level 3 fir forest multiplier also looks to be an error). The spreadsheet would probably benefit from a sheet allowing you to select which level buildings you own for calculating building hours, which may now become significant. At the moment all calculated values use level 1 building outputs. Unfortunately there isn't an easy way to change this.

I have not gone through checking what other changes there are, but there are plenty. Costs and LH change only moderately, and might be entirely due to small changes to lists of ingredients. As per the patch notes, weights decrease significantly, but the spreadsheet doesn't calculate hauling weights, merely lists them in the Items sheet.

Have fun.

  • Like 1

Share this post


Link to post
Share on other sites
On 9/12/2017 at 4:00 AM, Remus said:

Updated to build 11, but there are changes to ship trims I haven't incorporated. If anyone wants me to, please post blueprints of any two ships in every trim. Wood does not matter.

The spreadsheet does not incorporate taxes.

Building output changes significantly, both for base output and level 2 / level 3 multipliers which now vary between almost every building (including the level 3 stone mine which is exactly the same as the level 2 stone mine but costs you 150k for the privilege of owning one - surely some mistake. The level 3 fir forest multiplier also looks to be an error). The spreadsheet would probably benefit from a sheet allowing you to select which level buildings you own for calculating building hours, which may now become significant. At the moment all calculated values use level 1 building outputs. Unfortunately there isn't an easy way to change this.

I have not gone through checking what other changes there are, but there are plenty. Costs and LH change only moderately, and might be entirely due to small changes to lists of ingredients. As per the patch notes, weights decrease significantly, but the spreadsheet doesn't calculate hauling weights, merely lists them in the Items sheet.

Have fun.

Would it be possible to make it online, this way you may not even have to update it yourself, as in google docs

Edited by janat08

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×