open All Channels
seplocked Science and Industry
blankseplocked EVE manufacture and reprocessing "mastersheet" (Mar 2010)
 
This thread is older than 90 days and has been locked due to inactivity.


 
Pages: [1] 2 3 4 5

Author Topic

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.08 21:03:00 - [1]
 

Edited by: Akita T on 09/03/2010 09:33:45

Just input all relevant skill level and installation details in the black-on-yellow spaces.
Quick-search for desired product in the red-on-yellow field on the top.
You can search for the item name (best match attempted) OR you can force an item ID (leave field blank for no forcing).
Update prices as you see fit in the second worksheet (buy and sell order prices).
Useful data is automatically updated in the white-on-black fields.
___

AUTOMATICALLY CALCULATES:

* Manufacture costs and waste
* Actual refine yield
* Production time (per batch) and units manufacturable per day per slot
* Reprocess income selling to buy orders
* Reprocess income by putting up sell orders
* Manufacture cost, placing buy orders for mats
* Manufacture cost, buying mats from sell orders
* Income selling item on market at sell order price
* Profits per manufacture slot per day (four version depending on how you buy/sell, and an expected average if you combine them all)
* sorted list of items by profit/day/slot (assuming you can move all you can manufacture in a day)

Also contains quick hyperlinks to "THE FORGE" prices (XML marketstats dump) from EVE-CENTRAL.COM in the "PRICES" worksheet, so you can update rough/estimated prices even without being online (opens in new browser window).
You can change the region ID if you prefer to see/use prices from somewhere else (have to know region IDs for that though).
You can update buy and sell order prices for all components individually as you see fit anyway.
___

NOTES :

* most prices are set at 0.01 ISK buy order and 0.02 ISK sell order, so you have to manually update prices for the items you are interested in (and the materials it's made out of). Only very few "demonstration" prices are set to current Jita levels.

* BPC cost per run support included
This means you can calculate profits when building off purchased BPCs, or even feed in average invention price per obtained run to see actual invented manufacture costs.
___

KNOWN ISSUES:

* still contains some non-manufacturable items, even if I manually removed most of them ; do not remove them yourself unless you know how to alter the rest of the sheets too, or else you might have problems

* actual revenue for reprocessing T2 items/ships has quite a bit of estimation error when handling minerals coming out of the corresponding T1 item (it would take quite a bit longer to do properly, so it will have to do "as is" for now)

* visibility : there's a lot of displayed data and not very much space available, so sheet is best viewed at a high display resolution (feel free to resize any fields to make it fit on smaller monitors)

* T2 module manufacture quantities are a bit "off"**. T2 ships and T2 rigs are fine.
** Apparently, for T2 module manufacture advanced materials and components are NOT affected by waste, since they are considered "extra materials", as opposed to "raw materials". However, I am unable to locate the proper attribute anywhere in the database dumps.
___

Feel free to change/update/improve on it any way you see fit.
Useful versions from other people will be linked immediately below.

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.08 21:05:00 - [2]
 

Edited by: Akita T on 09/03/2010 09:32:26


IMPORTANT !!! A warning for Excel users of the later versions of the spreadsheet.

If you want to update the "prices" sheet, it would be a good idea to disable auto-re-calculations first, otherwise there will be significant "lag" due to the whole sheet updating (especially the sorting according to profit, that's the most CPU-intensive section).
HOW TO DO IT : IMAGE LINK (42kb GIF)
Once you have finished updating prices, you can set it to auto-calc again in the same place.
Depending on Excel version (or other spreadsheet program you prefer to use), location of option may vary.

__

v1.00 : Linkage (391 KB zip, 2.01 MB XLS inside)

v1.01 : Linkage (387 KB)

v1.02 : Linkage (388 KB)

v1.03 : Linkage (388 KB)

v1.04 : Linkage (2.29 MB, 9.95 MB XLS inside)

v1.05 : Linkage (2.29 MB)

v1.06 : Linkage (2.09 MB zip, 9.80 MB XLS inside)
*** NOTE : 1.06 has auto-calc disabled by default (press F9 to calculate after changes, or re-enable autocalc).
___

Versions from other people :

"Eve Master Sheet v1.06.2 with extras" (Linkage) by MegabitOne - see page 4 of this thread for the long version of the explanations.
Only guaranteed to work for Excel 2007 users (not tested on older versions), the "extras" are automatic price import macros from eve-central feeds.

"EMS 1.07" (Linkage) by Licinius CrassusFilius
Based on the above 1.06.2 sheet, with build data updated for the Dominion patch.

Kazuo Ishiguro
House of Marbles
Posted - 2009.02.08 22:09:00 - [3]
 

Suggestion: set up xml maps to import prices directly from eve-central, at least for the most commonly used items. It's possible to query up to 20 items per request iirc.

It's a good resource, but the hard part is choosing an item to build rather than working out what the margins are once you've found it. Could you create a macro that works out the profit per slot-hour on each item and dumps it to a table?

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.08 23:38:00 - [4]
 

To be quite honest, I tried, but... let's just say I don't know nearly anything about using XML in Excel, and not really in the mood (nor with enough free time) to learn it now. And the macros and external files or such, not even that much.
If you think you can do any of that, feel free to modify it any way you see fit (it's not like this is the first XLS file we worked on alternatively, heh, there was the invention sheet too).

Anyway, getting data directly from eve-central is probably NOT a good idea... even when filling the few things I filled, you sometimes hesitate on what exactly to use (min/median/max) for each field, and sometimes a quick look in-game is necessary to set a reasonable pricetag.
So, you know, not so sure on the merits of automatisation on this one. I'd rather have it use local market dumps instead, but I have no clue how to do that either.

By the way, there were some errors with the 1.00 version, fixed them, and added some minor extra functionality (and some more prices, mostly T2 components and advanced materials).

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.09 03:57:00 - [5]
 

Version 1.02
Added support of "per run BPC cost", so you can now determine profit for manufacturing from purchased BPCs (or, in case of inventors, enter your average invention per run cost here to properly determine prices).
Also added 5 new calculated fields, regarding "profit in ISK per day per manufacture slot" (red numbers in paranthesis means a loss instead of a profit), depending on how you obtain the materials and how you sell the product, plus expected average in case you use all input/output options equally.

Saira Jin
Posted - 2009.02.09 15:06:00 - [6]
 

Awesome work, must have taken some effort, and yet it's given freely.

Thanks much, Akita, and I am not the least surprised I am the first to thank you! Weird are the ways of the pod pilot!!!

Avalon Champion
Black Thorne Corporation
Posted - 2009.02.09 15:45:00 - [7]
 

Very nice, and is better and more accurate than what ive been using to date (ML Calc with, manual market price checks for materials).

Do you accept Isk Donations for all the hard work you've done?

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.09 17:19:00 - [8]
 

Originally by: Avalon Champion
Do you accept Isk Donations for all the hard work you've done?

Heh, as if I could refuse Wink
Thanks are enough, but ISK doesn't hurt Laughing

Saira Jin
Posted - 2009.02.09 18:29:00 - [9]
 

Akita, there seems to be no calculation for the "sell to buy orders" price that takes into account transaction/broker fees, unlike the "sell via sell orders" - am I correct in thinking this, or am I way off?

Thanks again.

Braaage
Ministry of Craft
Posted - 2009.02.09 18:31:00 - [10]
 

Akita do you ever log into EVE Very Happy there be a message waiting from me.

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.09 22:53:00 - [11]
 

Originally by: Saira Jin
Akita, there seems to be no calculation for the "sell to buy orders" price that takes into account transaction/broker fees, unlike the "sell via sell orders" - am I correct in thinking this, or am I way off?

Reprocess income selling to buy orders -> accounting only matters for sales tax, there are no broker fees on immediate orders

Reprocess income by putting up sell orders -> both accounting and broker fees matter here

Item maket buy order price -> just looking up the price per portion, no accounting/broker adjustment needed

Manufacture cost, placing buy orders for mats -> broker fees calculated for materials bought with buy orders, there is no sales tax so accounting doesn't matter

Manufacture cost, buying mats from sell orders -> trade tax and broker fees do not matter here at all

Income selling item on market at sell order price -> both accounting and broker fees matter since you put up a sale order

Item market sell order price -> just looking up the price per portion, no accounting/broker adjustment needed


Originally by: Braaage
Akita do you ever log into EVE Very Happy there be a message waiting from me.

Every now and then, I try to get in once a day, but it's not always feasable Laughing

Yes, of course you can use it, and you don't even need to ask for permission, since I gave it in the OP for anybody else (of course, provided they cite the source, but that's self-understood, I hope).
But since it's a work in progress you might want to make sure you have the latest version AND maybe also link back here in case you're not around.
I mean, I hope I'll get around to tweaking it, but I can't promise anything.

Tashiell Gao
Caldari
FinFleet
Raiden.
Posted - 2009.02.10 07:03:00 - [12]
 

nice saved me 0.193845m on my shiny new drake which i ll use to get to party with all the models in bling style after my old got blown up.

will use again Very Happy

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.10 08:03:00 - [13]
 

Originally by: Saira Jin
stuff

By asking me the above stuff, after writing exactly what I said above, I realized that I wasn't taking into account the sales tax everywhere I should (i.e. when using buy order price of item anywhere in the profit/slot calculations), so that is corrected in 1.03 now : Linkage
Also in 1.03, added an "units/day" (per slot) indicator right after the "actual production time" (seconds) result for your convenience (no point building lots of items just because "slot/day" profit seems high if they're barely traded, heh).

Tweaked some prices here and there in 1.03 too (up to current Jita IV-4 orders), but that you should re-tweak constantly anyway, so not really worth mentioning.

Finndibaenn
Posted - 2009.02.11 21:16:00 - [14]
 

Very nice work :)

For those of us, who like me don't have excel, I just tried uploading it to google docs spreadsheet and it seems to work like a charm !
Took a bit to import (few mins) but performance is very reasonable now, and it seems most formulas are working which I was not expecting.

Might actually be possible to keep the whole thing in google docs and make it public, then people would see the modifications made without the need for publishing a new version. Not sure if it's possible to lock some sheets as in excel though, which would be needed for preventing mistakes/misbehaviour.

Again, very nice work, spares us entering the mineral compositions for everything we want to produce :)

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2009.02.11 23:50:00 - [15]
 

Hey you forum warrior. Akita T is of course whom I am addressing (does that work? I've never used the word 'whom' before?), I wonder if you could help me understand the following equation.

Mineral cost = Base cost + (Base cost * (0.25 - (0.05 * Production Efficiency))) + (Base cost * Mineral wasteage factor)

Production efficiency and mineral wastage (there's enough e's in the english language thank you) are obvious as is Base cost. Already got those numbers from the data-dump.

I was just wondering what or where the 0.25 and the 0.05 numbers come from, or are they arbitrary amounts set by CCP?

Coming soon a mineral calculator in Access format! 2007 or 2003 hopefully, but when I say soon, I mean soonish, like...when it's done and polished.

Brock Nelson
Posted - 2009.02.12 00:05:00 - [16]
 

You wouldn't happen to be a fan of Boston Bruins would you?

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2009.02.12 00:13:00 - [17]
 

Originally by: Brock Nelson
You wouldn't happen to be a fan of Boston Bruins would you?


who me? nah UK Packers fan

Kiay Stryx
Gallente
Phoenix Mandate
Phoenix Virtue
Posted - 2009.02.12 04:20:00 - [18]
 

Edited by: Kiay Stryx on 12/02/2009 04:31:57
Originally by: Sidrat Flush

Mineral cost = Base cost + (Base cost * (0.25 - (0.05 * Production Efficiency))) + (Base cost * Mineral wasteage factor)

I was just wondering what or where the 0.25 and the 0.05 numbers come from, or are they arbitrary amounts set by CCP?



ProductionEfficiancyModifier = BaseQNT * (0.25 - (0.05 * SkillProductionEfficiency))

WasteIfNegativeME = BaseQNT * (abs(ME) + 1) * (BaseWaste / 100)
WasteIfPositiveME = BaseQNT * ((BaseWaste / 100) / (ME + 1))

BuildQNT = round((BaseQNT + Waste + ProductionEfficiancyModifier))

That's what I use if it makes it simpler for you.

http://myeve.eve-online.com/ingameboard.asp?a=topic&threadID=977647
http://phoenixmandate.com/PhoenixToolsManufacturing.php
Full T1, T2, Capital production calculator.

BaseQNT * (0.25 - (0.05 * SkillProductionEfficiency))
Your basically adding the extra % of minerals to the base, based on your skills.

*EDIT*
Akita T
Little tip, when building T2, all T1 items required for the T2 build (E.G. Thorax for Deimos build) do not use any equation. They should be set to BaseQNT, Same for R.A.M.

~Stryx

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.12 05:39:00 - [19]
 

Originally by: Kiay Stryx
Little tip, when building T2, all T1 items required for the T2 build (E.G. Thorax for Deimos build) do not use any equation. They should be set to BaseQNT, Same for R.A.M.

The T1 item is already taken into account at base quantity (but only in the "cost" column instead of the "manufacture quantity" column - the "if" statement with the "r?" column (that indicates wether the item is automatically reprocessed internally at 100% by the manufacture process)... but that statement is only necessary when the manufacture cost goes above 150% (so, over 50% waste)... granted, an invented BPC can get you there, so that "if" should stay in.
However I didn't yet include such a condition for the cost of R.A.M. - it should be possible if I use the "DPJ?" (damage per job) column, which is only <1 for R.A.M.s as far as I can see... didn't notice that since I didn't enter any RAM prices yet, heh.

Oh, and by the way, RAM consumption is really weird... rounded upwards after number of production runs IIRC ? I seriously can't recall for sure wether you can have partially damaged RAMs or not (my guess is you can't, but without current testing I'd better ask). This should be weird to implement cost-wise, since it would be more expensive for single runs than multiple runs.

Also, any idea what OTHER materials are NOT affected by waste normally ?
You know, except those mentioned so far, the base T1 item and the R.A.M.s ?
Because I have a nagging feeling there's more, but can't remember which.

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.12 11:08:00 - [20]
 

Edited by: Akita T on 12/02/2009 11:18:51


Ok, so... for 1.04, there's good news, and bad news.

Bad news first.
It's HUGE compared to the previous version.
Also, eats up A LOT of processing power when you make any changes in it (some older machines might hang for a while until they're done).

Good news ?
Well, on top of several minor tweaks... TWO NEW WORKSHEETS AVAILABLE.
One of them displays an overview of manufacturables in item ID order (using the skill/blueprint values in the master worksheet), with best/average/worst profit/day values, so you can just browse and look.
The other one auto-sorts items by average profit per day value.

Yup, you can now just enter prices and look what item makes you most cash/day (assuming the BPC costs zero, at least in this version).

__

P.S.

Obviously, a -4ME/-4PE list order won't be the same as a +20ME/+20PE list order, so don't get excited when you see "Golem profit 100 mil ISK/day" after you have high me/pe values listed in the main sheet (and the BPC costs are assumed zero, which is obviously not even close to the truth)... so be careful to ignore such items and look slightly lower.
Also, don't get excited if you go with 20ME/PE and see 500mil/day profit for some rigs... I doubt you can manufacture and sell how many you can produce in a day.

Also, there's a small problem with sorting, if two items have the exact same profit/day, only the first one (by item ID) is properly listed - this should almost never occur if you fill ALL prices properly, but it does occur quite often now that most prices are set to 0.01/0.02 ISK.

Sidrat Flush
Caldari
Eve Industrial Corp
Posted - 2009.02.12 13:03:00 - [21]
 

thanks Kiay Stryx.

Only when I was about to fall asleep in bed did I realise that it's two seperate equations, which is easily sorted out.

Oh I'm doing a database not a spreadsheet.

Akita' ss is very good though, I haven't looked at the newer version.

Kiay Stryx
Gallente
Phoenix Mandate
Phoenix Virtue
Posted - 2009.02.12 17:31:00 - [22]
 

Originally by: Akita T

Also, any idea what OTHER materials are NOT affected by waste normally ?
You know, except those mentioned so far, the base T1 item and the R.A.M.s ?
Because I have a nagging feeling there's more, but can't remember which.



As far as I know that's it.
If you show info on the blueprint in game, anything without a [You] will use the BaseQNT from the database.

~Stryx

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.12 19:27:00 - [23]
 

Originally by: Kiay Stryx
If you show info on the blueprint in game, anything without a [You] will use the BaseQNT from the database.

Nah, you can't trust that alone, you need some kind of precise attribute flag/value, just like the "recycled" or "damage per job" ones.

Pwett
QUANT Corp.
QUANT Hegemony
Posted - 2009.02.12 19:52:00 - [24]
 

Originally by: Akita T

Nah, you can't trust that alone, you need some kind of precise attribute flag/value, just like the "recycled" or "damage per job" ones.


There's one thing R.A.M. and tech 1 items have in common:
AttributeID #9: HP

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.12 20:25:00 - [25]
 

Well, they also have "recycle : 1" for T1 items and "damage per job : <1" for RAMs.
I was talking about other items (if any) that are unaffected by waste and their "special" attribute.

Pwett
QUANT Corp.
QUANT Hegemony
Posted - 2009.02.12 20:42:00 - [26]
 

Edited by: Pwett on 12/02/2009 21:07:40
Edited by: Pwett on 12/02/2009 20:43:23
Well, take a look at this query


select distinct(t2.typeID), typeNAme
from typeActivityMaterials as t1
left join (typeActivityMaterials as t2, invTypes)
on (t1.requiredTypeID = t2.typeID && t2.typeID = invTypes.typeID)
where t2.typeID is not null
order by invTypes.marketGroupID, typeName;


This is a list of everything in the materials table that can be built and can be used in something else. Which looks like the list.

If CCP does what CCP does, it might go through the loop and only apply wasteage on base construction items. Funny thing is, and I'm admittedly very tired, but why isn't this query picking up capital components?

Pwett
QUANT Corp.
QUANT Hegemony
Posted - 2009.02.12 20:45:00 - [27]
 

Edited by: Pwett on 12/02/2009 21:07:56
updated above

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.12 21:39:00 - [28]
 

My database skills are minimal, I must shamefully admit.
They extend basically to "save database tables as XLS sheets, and figure out what most of the fields mean" Laughing

Loaby
Amarr
Imperial Forces
Posted - 2009.02.15 08:55:00 - [29]
 

where is morphite in the prices sheet?

Akita T
Caldari Navy Volunteer Task Force
Posted - 2009.02.15 18:38:00 - [30]
 

Edited by: Akita T on 15/02/2009 18:39:52

Prices sheet is sorted by item ID, and Morphite was only introduced when T2 was introduced. It's on line 609.

11393 Retribution
11399 Morphite
11400 Jaguar

P.S. You might want to use the search function (CTRL-F in MSExcel) to quickly locate the item you want to change price for.
Item IDs are close to eachother for the same "class" of items, with very few exceptions.


Pages: [1] 2 3 4 5

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only