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.
12Next page
 

Invention and Manufacturing Spreadsheets v1.4 from Caltech Shipyards
Author
Previous Topic Next Topic
Lore Varan
Caltech Shipyards
#1 - 2012-07-29 20:55:03 UTC  |  Edited by: Lore Varan
Caltech Shipyards Invention and manufacturing spreadsheets


Version 1.4 of Caltech Shipyards spreadsheets are for Eve Online Inferno 1.2

Invention and Manufacturing Spreadsheets v1.4



Requirements

Open Office 3.2 ( Earlier Versions may work , but have not been tested )

350Mb Free memory recommended ( The "Everything" Sheet is a bit of a beast )

Eve Skill Production Efficiency level 5
Sorry this sheet is not suitable for new industrialist who have not trained lvl 5 PE yet.



Security

Macro execution permissions are required for some features

Open office Macro security has an option to allow macro's in spreadsheets that are located in a certain folder ( Very High security ).

If you decide to allow macro execution I strongly recommend that you use this option rather than enabling macro's globally ( Low Security )

This is located in
Open Office > Tools > Options > OpenOffice.org > Security > Macro Security > Trusted Sources > Trusted File Locations > Add Folder
( Create a folder if neccessay first and move the spreadsheet to that folder )

Basic spreadsheet functions do not require macro's so you can still use this sheet even if you do not wish to grant macro permissions



Purpose

Calculate the best way to Invent any item.
Calculate list of required purchases for invention attempts + Cost
Calculate list of required purchases for T1/T2/T3 manufacturing + Cost
Calculate total build cost of items T1/T2/T3* ( bpc costs for T3 not included )



Features

Quick List T2 Items by Invention+Build Profitability or Profit per Item
Quick List all purchases | subcomponent builds required
Download Prices from Eve-Central
Customisable Look



Usage

The sheet is primarily designed to work out the best ( cheapest invent + build cost ) method of inventing T2 items.
It also includes functionality to enable you to quickly work out what you need to purchase to make these invention attempts as well as make T1 items , T2 items ( bpo or invention ) and T3 items.
You will need to spend a little time setting up some values in the sheet before you will get accurate results.

Market price information has been pre-loaded for each item where possible - Data supplied by Eve-Central.
BPC price information has been entered by me and in each instance is a guestimate.
The manufacturing parts of the sheet are designed and are easiest to use if you are building subcomponents yourself.

You will need to enter the following information before you will get accurate results.

M.e. level of any blueprints you are building from
--> For Item Build Cost Calculations as well as material purchase requirements.

P.e. level of any blueprints you are building from
--> For build times and profits per day

Your Invention skill levels in the Invents Sheet
--> For all calculations involving invention.


A quick note on price evaluation.

Invention & Manufacturing formulas in these sheets take the higher of ( market price or build cost ) for subcomponents when calculating build cost for items that require these subcomponents.

Customising the sheet.

It is not possible to remove unwanted item rows from the Make or Buy sheets, this will break some calculations.

It is possible to hide unwanted rows and should also be ok to move rows up or down.
Adding columns for your own calculations should also be fine.

Do NOT rename the subsheets if you are using the macros, they will break.



The SQL scripts used to generate these sheets are now included in the zip file download.
You do not need to do anything with these files unless you want to make customised sheets.



The original forum post has lots of Questions and Answers.
Lore Varan
Caltech Shipyards
#2 - 2012-07-29 20:58:09 UTC  |  Edited by: Lore Varan
INSTRUCTIONS

The Make Sheet

This sheet is for manufacturing.
Items are grouped in tables based on there market group.

1st Column
This shows the item name - Table headers show Market Group

Profit/Day ( Formula Cell )
Shows you how much isk you will make per production line per day making this item.

To Build ( Formula Cell )
Shows the number of production runs you need to start ( taking into account Batch size ) to produce the amount of item requested in the Quantity Field + any sub component build requirements.

Quantity ( Input Cell )
Specify here the number of final items you want to build.
You do not need to specify subcomponents needed for other items, this is done automatically.

M.E. ( Input Cell )
Specify in here the M.E. level of the blueprint you are building off.

Sorry it is not possible to specify different me's for different blueprints of the same type.
IF you are in this situation then use the me of the least researched blueprint to ensure you always purchase enough materials

* For T2 items this field can be populated automatically from the Invent Sheet by pressing the "Copy best to make sheet" button.

Misc Costs ( Input Cell )
you can specify here any other costs you incure per item build.
This field is used for example to put in the npc charge for a manufacturing job.
* For T2 invented items the Invention cost is put in here
* This field is also automatically populated from the Invent Sheet by pressing the "Copy best to make sheet" button.

Value ( Input Cell )
The Market Value of an Item.
This can be populated automatically from the Eve-Central subsheet by pressing the "Apply Prices" button in that sheet.

Build Cost ( Formula Cell )
The Calculated cost for constructing this item from subcomponents or raw materials

When calculating the invent costs or build costs of an item that requires subcomponents to build
The highest of ( Value and Build Cost ) is used as the value of the item.

Required ( Formula Cell )
When building an item that requires subcomponents , the total number of subcomponents required for all builds appears in this column.

In Stock ( Input Cell )
If you allready have an amount of this item, specifying it here will remove the specified amount from the build reqs and purchase requirements.

Purchase ( Input Cell )
If you want to purchase a subcomponent rather than build it, put the amount you are going to purchase in here.

#BPO ( Input Cell )
The number of Blueprint you have for this item.

per BPO ( Formual Cell )
The number of build runs per BPO.

P.E. ( Input Cell )
Specify the p.e. of your blueprint here.

Days ( Formula Cell )
The Required number of days to build all the items you need or want.

TypeID ( Data Cell )
The TypeID of the Item.
This value is used to fetch and apply the prices by the Eve-Cental sheet
If you do not want a price to update automatically , remove this value or comment it out with a # in front of the number.
You will need to unprotect the sheet to do this.



In the top right of the sheet you will see an input cell where you can input a build time modifier.
Use this cell to adjust the build time of items.

Your build time modifier depends on you build skill ( industry iirc ) and an implant you have for build time improvement , plus any bonus you get from the location you are building in ( POSes )

The easiest way to work it out is to divide the base build time on a particular bpo by the actual build time you get when installing a job.

You only need to specify this value once.
Lore Varan
Caltech Shipyards
#3 - 2012-07-29 20:59:15 UTC  |  Edited by: Lore Varan
The Buy Sheet

Here you update prices / stock levels of input materials and can see what you need to purchase.


First 2 Columns
Specifies [ type of input or item market group ] and item name.

Quantity ( Formula Cell )
The amount of the material you need to Purchase ( Requirements - Stock Level )

Price ( Input Cell )
The market value of a material.
This can be populated automatically from the Eve-Central subsheet by pressing the "Apply Prices" buttons in those sheets.

Total Cost ( Formula Cell )
Cost for purchasing the amount of the material you need as specified in Quantity

Required ( Formula Cell )
The amount of the Item you need to do all the specified Builds / Invents

Stock ( Input Cell )
If you allready have an amount of this item, specifying it here will remove the specified amount from the purchase requirements.

TypeID ( Data Cell )
The TypeID of the Item.
This value is used to fetch and apply the prices by the Eve-Cental Sheet
If you do not want a price to update automatically , remove this value or comment it out with a # in front of the number.
You will need to unprotect the sheet to do this.
Lore Varan
Caltech Shipyards
#4 - 2012-07-29 21:01:53 UTC
The Invent Sheet

This sheet contains 3 Tables

The 1st table enables you to enter the level of your invention related skills.

For operation ease , I recommend that once you have entered your values in here , you unprotect the sheet and hide the rows of this table.
Then unfreeze the sheet ( Menu Bar > Windows > untick freeze )
and refreeze the sheet with the cell cursor in the first cell in the "Cost per run" column.
This will freeze the table header of the 2nd table at the top of the sheet.


2nd Table contains the best invention method for each item.
3rd Table contains all invention methods for each item ( from which the best is selected and highlighted )


Fields ( all fields here are formula fields with 1 exception )

First Field - Contains Item Name

Line ( Best Methods Table ) / Attempt ( All Methods Table ) ( Input Cell )

In the best methods table this field shows you on which row of the spread sheet you can find the best method for inventing this item.

In the All methods Table you will find an Input Cell where you can input the number of invention attempts you want to make using this method.


Cost per run - The invention costs per successful output run received ( takes into consideration - cost of inputs / invention chance / number of runs )

Item Build Cost - The final cost of building this item using this method , taking into account invention and build costs.
( Whichever method has the lowest overall item build cost is selected as the best method )

Invention Cost - The ammount of isk required to make an invention attempt using this method.

Decryptor - Decryptor of the method

Meta Item - Meta item of the method

Input BPC - Input Bpc of the method ( either single run or max runs )

M.E. - The output m.e. of the bpc using this method

Chance - The chance of success using this method with your skills

Runs - The number of output runs received with this method

P.E. - The output p.e. of the bpc using this method

Profit per Item - The value of the item ( as specified in the "make" sheet ) - the invention and build costs per run

Profit per Day - The ( profit per item ) / The amount of time to manufacture the item
This is the profitability of manufacturing the item , does not take invention time into account.
It is also assumed that it is possible to sell all the items constructed as the current market price.
Take this value with a big pinch of salt and research the sales volume of an item before creating 100 runs of the golden looking bpc's
Lore Varan
Caltech Shipyards
#5 - 2012-07-29 21:04:23 UTC
The Summary Sheet & Macro Functionality

The summary sheet

The four buttons on the left.

a.k.a. Show me the money buttons.
Lists all the inventions jobs in the invent sheet and sorts them by Profit or Profitability

Non Macro Workaround.

1.Copy the entire Invention sheet.
2.Paste into a new spreedsheet or subsheet.
3.Sort away to your hearts content.

Do not sort the invention sheet itself , this will almost certainly stop the sheet from working.

Either way remember that the profitability column assumes that you can sell all the items you build.
For low sales volume items - this value is missleading.



Purchase and build summary Button

Simply lists all the materials you need to buy.
Followed by the amount of each subcomponent you need to build.
Followed by the amount of each finished product you need to build.

Includes the build cost - for subcomponents and finished products.

Copy and paste this into eve notepad.

Non Macro Workaround.

Copy and paste into a new sheet , the "make" and "buy" sheets
deleted columns / rows as neccesaary.



Purchase and Build HTML

This generates a Purchase and Build Summary that can be viewed in the EVE In Game Brower.

The summary is generated as a HTML file on your local hard drive.
The link to use to view the file in the IGB is displayed in the second column in the output area of the Summary sheet along with some basic instructions.

An Index HTML file off all purchase and build summaries is also constructed and the link to it displayed.

Viewing the HTML file in the IGB enables you to open up the market screen at the correct item by clicking on an item in the list.



Apply Button

This repaints the spreadsheet with the colours / borders / background colours / fonts and number formats you specify.

The 7 boxes to the right of the button are where you specify fonts/colors/borders etc.
Just use the standard spreadsheet format cell options to alter these cells.

The 4 boxes to the left of the button are where you specify number formats.

Iskcolumns > the number format of all *(except buysheet.value) columns that contain isk values
These isk values are usually quite large and dont need isk cents by default and negative values in red.

Intcolumns > the number format of most non isk columns , no decimal places by default and negative values in black.

Floating point columns > isk cents included in format , only used in the buy sheet value column and the download sheets.

Percentage columns > How to display a percentage , only applied to chance column in Invent sheet.

Non Macro Workaround.

Apply any formats you want manually.



Other Macro Buttons

"Optimise columns" - Makes all the columns on the sheet the optimal width.
This is a shortcut for - unprotect sheet > apply optimal column width > reprotect sheet.

"Copy best to make sheet" on the Invention Sheet.

This goes through each best method in the sheet and copys the values for ( m.e. , p.e. and Invention costs ) to the make sheet.
These values can of course be copied manually if you are just interested in a few items.

Note.

If you want to build some t2 items from a BPO you have and you do not want your values for m.e. , p.e. , misc cost overwritten for that item do the following.

unprotect the make sheet.
Place a #Mark or "." in front of the items name in the first column
reprotect the make sheet.
Lore Varan
Caltech Shipyards
#6 - 2012-07-29 21:08:00 UTC  |  Edited by: Lore Varan
The Eve-Central download sheet


Reset Button
Clears the sheet and builds a fresh list of every item that a price can be obtained for.


Drop down
Select the region you want prices for.


Eve-Central Button
This fetches the pricing data from the website.
This will cause the sheet to lag out a little while the prices are downloaded this is normal do not worry.


Apply Prices
Applys the value in the formula column for each item to the rest of the spreadsheet.


Formula to Apply
Enter into the box the formula you would like to apply to the download data or leave blank for the default formula.
Any cells selected in the formula should be on the line above the header.
This gets altered as the formula is copied down the sheet in the same manner as regular copy and paste of spreadsheet formula's

Clicking reset copys your new formula into the formula box for each item.


Optimising

Its not neccessary to download a full list each time.
Once a full download has been performed and those prices applied you can cut out rows you dont want to upate each time you download.

Maybe just have the minerals and moon goo as regular downloads.
You can alter the formula for individual items if you wish.
Beware that pressing reset will remove these customisations.

Copy and paste your optimised download list into a spare sheet.
That way when you do need to do a full download refresh , you can paste you optimised list back in afterwards.
Sexy Cakes
Have A Seat
#7 - 2012-07-30 12:17:36 UTC  |  Edited by: Sexy Cakes
Looks pretty well laid out and straight forward.

Thanks for uploading this!

Edit: I get a 'Unable to download XML data from EVE-Central' error when I press the EVE-Central button. I setup the security options for macros as well as tried to clear out the 'Formula to Apply' cell mentioned in the old thread. Sorry if I'm missing something obvious.

Not today spaghetti.
Lore Varan
Caltech Shipyards
#8 - 2012-07-30 15:15:13 UTC  |  Edited by: Lore Varan
Sexy Cakes wrote:
Looks pretty well laid out and straight forward.

Thanks for uploading this!

Edit: I get a 'Unable to download XML data from EVE-Central' error when I press the EVE-Central button. I setup the security options for macros as well as tried to clear out the 'Formula to Apply' cell mentioned in the old thread. Sorry if I'm missing something obvious.



OK I'll take another look at it when I get home.
The error message is generated by the fetch data macro , so its not that.


Your connected to the internet and checked Eve-Central site is up ?

Is it PC or Mac ?

What Operating system are you using ?
What version of Open Office are you using ?
Lore Varan
Caltech Shipyards
#9 - 2012-07-30 16:07:00 UTC  |  Edited by: Lore Varan
deleted
Sexy Cakes
Have A Seat
#10 - 2012-07-30 16:31:07 UTC
Lore Varan wrote:

Your connected to the internet and checked Eve-Central site is up ?

Is it PC or Mac ?

What Operating system are you using ?
What version of Open Office are you using ?


Was unaware the Eve-Central site needed to be open, but still getting error with it open.

PC; Win 7; OO 3.4

Thanks!

Not today spaghetti.
Lore Varan
Caltech Shipyards
#11 - 2012-07-30 16:40:28 UTC
Sexy Cakes wrote:
Lore Varan wrote:

Your connected to the internet and checked Eve-Central site is up ?

Is it PC or Mac ?

What Operating system are you using ?
What version of Open Office are you using ?


Was unaware the Eve-Central site needed to be open, but still getting error with it open.

PC; Win 7; OO 3.4

Thanks!



You need access to eve central api server from the machine you are using.
If you are at college you may be behind a firewall that is blocking.

Can you access this link ( small xml file ) from your pc
http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&regionlimit=10000002

If you cant get that file in web browser then firewall is issue.

If you can then issue is with the spreadsheet.
Sexy Cakes
Have A Seat
#12 - 2012-07-30 16:50:27 UTC
I assume the HTML code on that page means that I can indeed access it? lol sorry not real sharp on the xml datas

Not today spaghetti.
Lore Varan
Caltech Shipyards
#13 - 2012-07-30 17:36:52 UTC
Sexy Cakes wrote:
I assume the HTML code on that page means that I can indeed access it? lol sorry not real sharp on the xml datas



Yeah that sounds fine.

One more thing to try.

Press the Purchase and Build HTML button on the Summary sheet.

Any errors ?

If not try the download one more time.


weird request I know , but its to do with creating working directories.

both functions use a sub directory called \eveIMMS\ in your normal MyDocuments Folder
Its for storring temp files and HTML pages for the Ingame Browser.

I'm wondering if the data import doesnt correctly create that directory.
Sexy Cakes
Have A Seat
#14 - 2012-07-30 17:45:58 UTC
Ya it seems to be working now.

Well played sir.

Not today spaghetti.
Tallinlen
The Scope
Gallente Federation
#15 - 2012-07-30 19:44:38 UTC
Sweet Sheets
Arcosian
Arcosian Heavy Industries Corp Holding
#16 - 2012-07-31 04:35:02 UTC
I thought you left the game Shocked rejoice the spreadsheet god has returnedBig smile
Haffsol
#17 - 2012-07-31 05:02:15 UTC
I think the "Unable to download XML data from EVE-Central" error is due to a path issue. I moved your (uber) sheets in a my /Documents/EVE/something subdir, created the EveMMS links pressing the Purchase&Buy button but I had that error trying to fetch prices by the Eve-Central button. Now I moved the sheets in /myself/Documents (where the EveIMMS dir is located) and I have no error shown, prices are applied correctly in the "Buy" page so it looks like being fixed.....but I don't see anything in the Eve_central one. Not a major problem but having all those fields in the Eve-Central tab/page referring to the buy/sell orders filled up with data could be useful.

I don't know how hard it is fixing the path issue but I'd like to be able to move them in a different path than /myself/Documents. That dir is full of stufff you know

Anyway...... thanks for sharing the love for spreadsheets
Lore Varan
Caltech Shipyards
#18 - 2012-07-31 12:37:32 UTC
Haffsol wrote:
I think the "Unable to download XML data from EVE-Central" error is due to a path issue. I moved your (uber) sheets in a my /Documents/EVE/something subdir, created the EveMMS links pressing the Purchase&Buy button but I had that error trying to fetch prices by the Eve-Central button. Now I moved the sheets in /myself/Documents (where the EveIMMS dir is located) and I have no error shown, prices are applied correctly in the "Buy" page so it looks like being fixed.....but I don't see anything in the Eve_central one. Not a major problem but having all those fields in the Eve-Central tab/page referring to the buy/sell orders filled up with data could be useful.

I don't know how hard it is fixing the path issue but I'd like to be able to move them in a different path than /myself/Documents. That dir is full of stufff you know

Anyway...... thanks for sharing the love for spreadsheets



Hey.

The fix is quite easy its one line of code in fact.
It creates the working directory MyDocuments/eveIMMS/
Puting it into all sheets and deploying it is gonna take me about an hour.
I didnt have time yesterday , but will put out the fix tonight as v1.31

If your using 1.3 the fix is simply press the Purchse and Build HTML button which should create the working directory.
Once done the directoy is never deleted by the sheet so the fix is permanent unless you remove the direcory manually.

The location of the sheet itself should not matter unless you are having problem running the macros at all, that is to do with macro execution permission , see the original post.

A quick check if you are getting the can't download from eve central message is to try this link in your web browser.
Can you access this link ( small xml file ) from your pc http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002

It appears that there are problems accessing Eve-Central data feeds through a proxy server.
This issue is not related to the sheets as the problem Http response 400 bad request happens even when using IE to access the link.
If you can't access that link from a particular computer, like me from work :( Then the sheet download will never work.

Haffsol
#19 - 2012-07-31 15:31:56 UTC
Quote:
The location of the sheet itself should not matter unless you are having problem running the macros at all, that is to do with macro execution permission , see the original post.

well actually what I was trying to say it's that it really looks like a path-related issue instead. I did follow all the steps you mention and I have no problem fetching eve-central prices (using other sheets) or opening your link in my browser..... BUT: if I leave your sheets in any other path different than /myself/Documents I get the "Unable to fetch eve-central" thing. If I just move them into /myself/Documents the error disappears.

I use LibreOffice 3.5.4.2
Lore Varan
Caltech Shipyards
#20 - 2012-07-31 15:55:49 UTC
Haffsol wrote:
Quote:
The location of the sheet itself should not matter unless you are having problem running the macros at all, that is to do with macro execution permission , see the original post.

well actually what I was trying to say it's that it really looks like a path-related issue instead. I did follow all the steps you mention and I have no problem fetching eve-central prices (using other sheets) or opening your link in my browser..... BUT: if I leave your sheets in any other path different than /myself/Documents I get the "Unable to fetch eve-central" thing. If I just move them into /myself/Documents the error disappears.

I use LibreOffice 3.5.4.2



Part of the process is calling into OO to get the standard work directory.

in oo Win this returns the MyDocuments Directory
in oo Mac it is the users home directory.

a sub directory /eveIMMS/ is then added to create the final path to save HTML files and XML transfer into

from what you are saying it sounds like LibreOffice is returning the spreadsheets directory rather than the working directory.

A workaround might be to access the document via a shortcut specify the "start in" directory as the location where you want the eveIMMS directory to live if different from the documents location.


Sorry I cant help anymore than this I dont use or have LibreOffice.
12Next page