These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

Blueprint ME multiplier for spreadsheets

First post
Author
Previous Topic Next Topic
Lestial Thara
Imperial Academy
Amarr Empire
#1 - 2014-06-02 21:37:23 UTC
Hey fellow indy peeps.

A little frustrated and could use some help. I'm using excel spreadsheets to track profit from building and every SINGLE time I research a BP I have to go back in and change the base requirements for minerals/components. It's not so bad if I'm bldging an iteron but when doing more complex construction such as T2 and capital it's a serious pain in my ass.

I'm looking for a multiplier for BPOs that determines what the mineral requirement is per ME lvl researched. I know it's out there when I see web pages like the ones below that obviously never Researched the BP that far but can give a mineral requirement for high lvl ME reseach.


http://games.chruker.dk/eve_online/market.php?group_id=2

http://zofu.no-ip.de/bpo

Any help is appreciated. Thx.

Mr. Thara
Tau Cabalander
Retirement Retreat
Working Stiffs
#2 - 2014-06-02 22:01:27 UTC  |  Edited by: Tau Cabalander
Total Material = ROUND(Base Amount * (1.25 + Base Waste / (ML + 1) - Material Efficiency Skill * 0.05))

Base Amount = ROUND(Material amount on ML 0 BPO / (1 + Base Waste on a ML 0 BPO))
[The Base Amount is what's in the database.]

EDIT: This will change Jul 22 with the Crius expansion.
Lestial Thara
Imperial Academy
Amarr Empire
#3 - 2014-06-02 22:41:52 UTC
Tau Cabalander wrote:
Total Material = ROUND(Base Amount * (1.25 + Base Waste / (ML + 1) - Material Efficiency Skill * 0.05))

Base Amount = ROUND(Material amount on ML 0 BPO / (1 + Base Waste on a ML 0 BPO))
[The Base Amount is what's in the database.]

EDIT: This will change Jul 22 with the Crius expansion.



Base waste is the wastage factor?
Tau Cabalander
Retirement Retreat
Working Stiffs
#4 - 2014-06-02 22:45:20 UTC  |  Edited by: Tau Cabalander
Lestial Thara wrote:
Tau Cabalander wrote:
Total Material = ROUND(Base Amount * (1.25 + Base Waste / (ML + 1) - Material Efficiency Skill * 0.05))

Base Amount = ROUND(Material amount on ML 0 BPO / (1 + Base Waste on a ML 0 BPO))
[The Base Amount is what's in the database.]

EDIT: This will change Jul 22 with the Crius expansion.



Base waste is the wastage factor?

On a ML 0 BPO, yes.

Wastage = Base Waste / (ML + 1)
Plleasure Hub
Municorn
#5 - 2014-06-02 23:52:59 UTC
Thanks, Tau! I have been looking into automating this sort of calculation, too. I have a master sheet with 340 items, though I haven't put in the material data on all of them yet. I did finally get price data automatically flowing in from multiple hubs. Big smile

I really need to find a way to import a data dump into a spreadsheet, then use that as a source for materials needed. Getting that kind of system online would put me in a position to quickly adapt to Crius.

Unfortunately, my internet is horrible right now, but I am upgrading it in a few days. Maybe I can post a write-up and some template spreadsheets once I have a chance to tinker with dumps.

I plan to have my EVE spreadsheet do some of this stuff before Crius:

  • pull in base material costs from data dumps,
  • automatically calculate material costs and production times based on ME & TE levels,
  • pull up-to-date skill data from API (relevant for building & invention).

Some graphs would be cool too..

"There's no meaningful difference between a real and a virtual world. It's pointless to ask anyone who they really are. All you can do is accept and believe in them, because whoever they are in your mind, is their true identity." — Kazuto Kirigaya
Nevyn Auscent
Broke Sauce
#6 - 2014-06-03 00:00:58 UTC
I'd suggest just changing your spreadsheet to use post Crius maths and keep a copy of what you currently have to work with in the next month. Doing it all with the current maths when the horrible calculations are going away but new modifiers are coming in is just doubling down on your work.
Tau Cabalander
Retirement Retreat
Working Stiffs
#7 - 2014-06-03 00:33:03 UTC
My spreadsheet (soon to be useless in Crius) was manually entered, and has recently had a lot of errors discovered in it (from recent major changes to the layout and how the calculations are done).

Basically it looks like this (from memory):
* Name
* NPC Price
* Current ML / PL (colour coded: red = ML 0, yellow = needs more ML, green = perfect, magenta = okay as-is)
* Ideal ML / PL
* Number of runs
* Cost of N runs
* Volume of materials for N runs
* Cost of 1 run
* Volume of materials for 1 run
* Materials for N runs (multiple columns, colour coded)
* Materials for 1 run (multiple columns, colour coded)
* Wasted materials (multiple columns, colour coded)
* Base materials (multiple columns, colour coded)
* Extra materials (multiple columns, colour coded)
* Summary: Total NPC value of all BPO
On another sheet I have material prices, also manually entered.

I also have sheets for ice, ore, T2 components, T3 (several sheets), and other things I build that require more detail.
Lestial Thara
Imperial Academy
Amarr Empire
#8 - 2014-06-03 01:59:40 UTC  |  Edited by: Lestial Thara
Tau Cabalander wrote:
Lestial Thara wrote:
Tau Cabalander wrote:
Total Material = ROUND(Base Amount * (1.25 + Base Waste / (ML + 1) - Material Efficiency Skill * 0.05))

Base Amount = ROUND(Material amount on ML 0 BPO / (1 + Base Waste on a ML 0 BPO))
[The Base Amount is what's in the database.]

EDIT: This will change Jul 22 with the Crius expansion.



Base waste is the wastage factor?

On a ML 0 BPO, yes.

Wastage = Base Waste / (ML + 1)


This is really going to help me if I can get my equation right... I think i'm confused on the waste part.


Wastage factor is always 10%. So I'm using (material amount on ML 0 BPO/1.1) ? That doesn't look right.

I'm kind of close to just saying forget it and wait for the change to come out in July and then come back to this topic. lol

To make this easier Maybe you can use isogen in the capital computer system BPO as an example with Max skills ML 150. If you can put that into an equation that would be awesome. Thanks again for helping on this.
Tau Cabalander
Retirement Retreat
Working Stiffs
#9 - 2014-06-03 04:59:54 UTC  |  Edited by: Tau Cabalander
Lestial Thara wrote:
Wastage factor is always 10%.

Not true. On some things it is 5%, for example: Core Scanner Probe I.

Lestial Thara wrote:
o I'm using (material amount on ML 0 BPO/1.1) ? That doesn't look right.

If you are looking at a BPO in-game with ML 0 that is 10% wastage:
Total Materials = Base Materials * (1 + wastage) = Base Materials * (1 + 10%) = Base Materials * 1.1
So to find the BASE MATERIALS:
Base Materials = Total Material on ML 0 BPO / 1.1
Rounded of course.

Lestial Thara wrote:
To make this easier Maybe you can use isogen in the capital computer system BPO as an example with Max skills ML 150. If you can put that into an equation that would be awesome. Thanks again for helping on this.

ML 0 BPO = 6515 Isogen @ 10% wastage, and 6515 / (1 + 10%) = 5923 = base material

Base Material = 5923 Isogen
Base Waste = 10%
ML 0: ROUND(5923 Isogen * (1 + 10% / (0 + 1))) = 6515 Isogen
ML 1: ROUND(5923 Isogen * (1 + 10% / (1 + 1))) = 6219 Isogen
ML 2: ROUND(5923 Isogen * (1 + 10% / (2 + 1))) = 6120 Isogen
ML 3: ROUND(5923 Isogen * (1 + 10% / (3 + 1))) = 6071 Isogen
...
ML 150: ROUND(5923 Isogen * (1 + 10% / (150 + 1))) = 5927 Isogen
...
ML 1183: ROUND(5923 Isogen * (1 + 10% / (1183 + 1))) = 5924 Isogen
ML 1184: ROUND(5923 Isogen * (1 + 10% / (1184 + 1))) = 5923 Isogen

That last one is < 0.5 units of waste at ML = FLOOR(Material on ML 0 BPO / [(1 + base waste) / (2 * base waste)])
or ML = FLOOR(Material on ML 0 BPO / 5.5) for a 10% base waste BPO.

"Perfect", or no waste on a Capital Computer System BPO depends on Tritanium, as that has the largest amount on the BPO. Of course that is a silly amount of research time to save a few ISK.

Does this help?


https://zofu.no-ip.de/bpo
https://www.fuzzwork.co.uk/blueprints/
http://games.chruker.dk/eve_online/item.php?type_id=21036
http://eve-online.itemdrop.net/eve_db/items/blueprint/capital_construction_blueprints/capital_computer_system_blueprint/
Plleasure Hub
Municorn
#10 - 2014-06-03 07:54:23 UTC
Looks like this thread is going in my scrapbook for some coffee-fueled dissemination as I delve deeper into all of this. Those Materials and costs for N runs of item sound very practical for day-to-day production, especially when figuring out what to load into your POS for your next jobs.

I do not care much if some of my work until July 22 is wasted due to changes. I just learned how to use APIs with xmlImport() in the past month, so it is all a great learning experience to go through. God, I remember my old system of manual price updates. I even had a "last updated" script to show me what prices to update next. It is just embarrassing.

Live price data on the other hand? Amazing. +1000 to Burnout Resistance. One of these days I will get this thing to tell me what to build each day in a sexy British voice.

Another thing I would like is a system to alert me on what has sold out and which materials need to be reordered soon.

"There's no meaningful difference between a real and a virtual world. It's pointless to ask anyone who they really are. All you can do is accept and believe in them, because whoever they are in your mind, is their true identity." — Kazuto Kirigaya
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#11 - 2014-06-03 12:01:08 UTC
https://www.fuzzwork.co.uk/2012/10/27/eve-sde-sql-blueprint-details/ might be of some interest, though it's talking about database stuff, rather than spreadsheet (though you can use a database as a source with excel)


jeveassets http://eve.nikr.net/jeveasset is your friend, for managing stockpiles of materials.


If you want /live/ price data, then you'll want to look at either EMDR or a local endpoint for the price uploader in evemon. Both aren't something to do using a spreadsheet though. evecentral's probably close enough. (if you're not using it already, consider using the percentile price, rather than the lowest)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Lestial Thara
Imperial Academy
Amarr Empire
#12 - 2014-06-03 21:18:57 UTC
Tau. That was a little more then I needed LOL however it made it very easy. Took me a couple hours but the formulas are working and matching material requirements. ThAnk you again.