| Pages: [1] 2 3 4 :: one page |
|
|
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Lore Varan
Caltech Shipyards
   |
Posted - 2010.04.30 02:54:00 -
[1]
UNDER CONSTRUCTION... goddam forums ate my post
Caltech Shipyards
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.04.30 03:00:00 -
[2]
Edited by: Lore Varan on 30/04/2010 03:00:36
reserved
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.04.30 03:19:00 -
[3]
reserved
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.04.30 03:37:00 -
[4]
reserved
|
Ghanid
|
Posted - 2010.04.30 08:32:00 -
[5]
Errr, do you really mean OpenOffice 3.3? I have the latest version which is only 3.2
|
Dav Varan
|
Posted - 2010.04.30 13:09:00 -
[6]
 Originally by: Ghanid
Errr, do you really mean OpenOffice 3.3? I have the latest version which is only 3.2
Fixed typo , thanks.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.04.30 20:48:00 -
[7]
Edited by: Lore Varan on 30/04/2010 20:53:17
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.
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
|
Posted - 2010.04.30 22:25:00 -
[8]
Edited by: Lore Varan on 30/04/2010 22:29:41
The Download Sheets
Eve-Metrics and Eve-Central download sheets both work the same way.
Eve-Central has a volumes column whereas Eve-Metrics doesnt but it does have a simulated value , which is imo the besst value to take.
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-Metrics / Eve-Central Button
This fetches the pricing data from the relevant websites.
This will cause the sheet to lag out while the prices are downloaded.
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
By default Eve-Central sheet uses the highest value of Average Buy orders or minimum sell orders.
By default Eve-Metrics sheet uses the highest of the Simulated prices ( buy or cell )
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.
Questions or bug reports
Please post any queries about the sheet in here rather than in-game mail.
Regards
Lore Varan.
|
Stafen
Killer Koalas
|
Posted - 2010.05.01 19:13:00 -
[9]
This topic deserves a reply, especially as I have been thinking of making similar spreadsheets myself. They look very complete, with a nice at a glance list of the most profitable items.
Making the spreadsheets in openoffice is nice as it's free, no need to acquire a version of excel.
(even though using excel's xml import functions makes importing data trivial)
The macros are visual basic, and you can inspect the source code, so someone can easily verify nothing is amiss. It takes a bit of time to import the prices though, CPU is not at 100% and it does fetch them in batches of 19, so the bottle neck is probably the server.
|
Emporer Norton
|
Posted - 2010.05.01 20:47:00 -
[10]
Nice set of sheets thank you
|
|
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.05.02 16:11:00 -
[11]
Originally by: Stafen
This topic deserves a reply, especially as I have been thinking of making similar spreadsheets myself. They look very complete, with a nice at a glance list of the most profitable items.
Making the spreadsheets in openoffice is nice as it's free, no need to acquire a version of excel.
(even though using excel's xml import functions makes importing data trivial)
The macros are visual basic, and you can inspect the source code, so someone can easily verify nothing is amiss. It takes a bit of time to import the prices though, CPU is not at 100% and it does fetch them in batches of 19, so the bottle neck is probably the server.
Yeah the download is not great.
On my quad core cpu runs at 25% while downloading which leads me to believe that OO only supports single core operation.
I decided to use OO basic ( very similar to VB :) ) to do the macros as the macro code can travel with the spreadsheets.
Anyone with OO Basic or Visual basic experiance will find it easy to check out the code before running it.
The other way to do XML imports in OO is to use XML Filters, but they need to be installed independantly by the user afaik.
Basically what happens is the XML returned by the server is opened as a new CSV spreadsheet ( invisible ) then the macro code parses the the spreadsheet to extract the required info.
Not the most efficient way to do it , But it works.
There is a 3rd method of getting XML into OO using a component known as the SAX parser.
I am going to have a play around with it these next couple of weeks to see if I can get the download working more smoothly.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.05.07 15:48:00 -
[12]
Bumpage
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.05.17 13:10:00 -
[13]
Bumpage
|
Jobby
Minmatar
Sebiestor tribe
|
Posted - 2010.05.17 14:33:00 -
[14]
Looks quite useful, will certainly be checking this out. :-)
Thanks.
|
Kordel Trask
|
Posted - 2010.05.17 23:51:00 -
[15]
Very Nice,
I too appreciate the open office format and the open visual basic.
Thanks for the open office docs on behalf of those who don't have MS office. (I have both installed)
The Visual Basic.
That's a big benefit for those of us trying to figure out how everything works for ourselves.
Thanks for all of the work and sharing it.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.07.21 13:55:00 -
[16]
Bump before the lock
|
Bad Princess
Minmatar
|
Posted - 2010.08.05 13:26:00 -
[17]
Black on dark blue is impossible to read.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.05 13:36:00 -
[18]
Originally by: Bad Princess
Black on dark blue is impossible to read.
Theres should be no black on dark blue in the sheet by default!
Are you using Open Office 3.2 or another version ?
That said you can apply whatever colours you like by pressing the apply button on the summary sheet.
Apply your own colours to the input cells next to that button.
If that does not work then you can allways apply colours with the standard format tools of the spreed sheet.
The headers are dark blue and the text color in them is automatic.
If this is not working properly then try making the text color white and hitting the aply button.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.25 15:17:00 -
[19]
Bumpage
|
Jeddeita
Lead Farmers
|
Posted - 2010.08.26 09:13:00 -
[20]
Many thanks for the work you have put into this. Just one question - does the invention profit/day take into account the % chance of a invention job working? Just need to know so I don't mess up my maths :)
----------
EVEStuff.net - High Quality Corp & Alliance Web Hosting |
|
|
Cerrano
Caldari
|
Posted - 2010.08.26 09:24:00 -
[21]
This sheet looks promising, I'll take a closer look when I'm home from work. But it looks like it covers what I've been looking for. Thanks.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.26 19:09:00 -
[22]
Originally by: Jeddeita
Many thanks for the work you have put into this. Just one question - does the invention profit/day take into account the % chance of a invention job working? Just need to know so I don't mess up my maths :)
Yes percentage chance is taken into account.
Of course this makes it an averaged value that can be expected over many runs.
Specifically.
( Invent Costs / Percentage Chance / Output runs ) = Invent costs per run
( Manufacturing cost is calculated on the me of the output bpc of the invention )
Then Total Build Cost per finished item = Invention costs per run + Manufacturing Costs.
Profitability = ( Product Value - Total Build Cost per finished Item ) / Manufacturing Time.
So the profitability relates to the time taken up in the manufacturing slots.
This beacause invention is a much quicker process than manufacturing and is rarelly the bottleneck.
|
JASON W0RTHING
The Devolved
|
Posted - 2010.08.26 21:24:00 -
[23]
Hi,
I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.
I just wanted to know your rationale behind the method you chose for cost calculations.
Originally by: CCP Shadow
What is thy bidd -- Wait. This thread, I have an irresistible urge to lock it for "being related to neither crime nor punishment."
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.26 23:32:00 -
[24]
Originally by: JASON W0RTHING
Hi,
I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.
I just wanted to know your rationale behind the method you chose for cost calculations.
Subcomponents.
A subsomponent's value is assesed as the higher of build cost or market price.
Initially before i released the sheets the subcomponent price was just taken to be the build cost due to the me of the print being used.
When I added the profitibility column , the sheet showed for example.
Building a charon.
A nice profit build the subs.
And a nice profit building the charon.
When added togethor the total profit made no sense and was out of whack with what i was making.
I realised I had the same profit showing in 2 rows.
Both in the subcomponent row and the final product row.
So I changed the formula to take the higher of buildcost | market value as the value of the sub.
This then shows the profit correctly.
Some of the profit is on the subcomponent.
Some of the profit is on the final build.
If you want to see all the profit on the final build , zero out the market value of the subcomponent and hash out the typeid so that the download sheets dont overwrite the zerod out price.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.26 23:51:00 -
[25]
Originally by: JASON W0RTHING
Hi,
I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.
I just wanted to know your rationale behind the method you chose for cost calculations.
Market prices from download sheets.
These are customisable.
I take simulated from eve-metrics ( the price required to sell/buy 5% of the entire market iirc ) and higer of ( average buy | minimum sell ) from eve-central.
The prices downloaded are for a region.
minimum sell can be misleading on a region level.
e.g.
The forge.
There may be a sell order for 27 trit in Friggi @1.9isk.
Whereas buyers are buying in jita for 2.3isk.
What is the value of trit ?
I tried to find a formula that is a good fit for a lot of different products.
raw materials , subs , finished products.
Its perfectly possible to set up the sheet to always take minimum sell price.
In the download formula box create a formula that simple links to the cell on the same row as itself in the minumum sell column.
Press reset.
Press download.
You may find minimum sell works well for some items.
For other items it wont be accurate.
Each item can have its own different formula if you want to make the sheet really accurate.
e.g.
For trit you could use average buy price.
And for Absolution you could use minimum sell price.
just change the formula in the yellow box in each items row.
These customised formula stay in place when you next download as long as you dont press reset.
pressing reset will use the formula in the default formula box for each row.
If there is no formula there . ie it has been cleared by teh user then the sheet substitutes my default formula.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.27 01:50:00 -
[26]
Originally by: JASON W0RTHING
Hi,
I just had a question about the cost calculations that you are using. You said you use the higher of the values for calculating build cost. I find this to be somewhat inaccurate. When using invention programs and such with market aggregating tools such EVE-central or EVE Metrics I find the best way to approximate the profitability of an item at a glance is to use minimum sell price, for both buying and selling items. This way you are essentially calculating revenue for an item off what you would sell it for 0.01 isking, and what you would buy materials for from the cheapest jita-market supplier. I have found these calculations to be reasonably reliable in determining profitability.
I just wanted to know your rationale behind the method you chose for cost calculations.
The sheet has 2 major functional area's
Calculating material requirements ( purchases ).
In this the sheet should be 100% spot on accurate at all times.
When you have run all your builds and inventions you should have a nice empty input hangar.
Profit Evaluation.
Is a somewhat greyer area.
This sheet is not a ledger system , it is not calculating profit after the fact with accurate inputs.
The purchases total should be fairly accurate to within a percent.
Assuming prices are not fluctuating wildly.
As far as the profit per item columns go in both the make sheet and inventions sheets.
These are predicted values based on the assumption that market prices are relativelly stable over the build time.
In making these predictions and setting aside market price fluctuations the rationale has allways been to make a cautous assesment.
|
Val Ashir
|
Posted - 2010.08.27 08:28:00 -
[27]
Edited by: Val Ashir on 27/08/2010 08:35:30
Awesome spreadsheet
Ive been using the modules spreadsheet and i get macro errors when i changed the formula in the eve-central sheet to eliminate non-buyable items.
atm it sits as =IF(sellmin=0;999999999; IF(RIGHT(TRIM(item name);2)="II";MAX(buymax;sellmin);MAX(AVERAGE(buymax;MIN(sellmed;sellavg));AVERAGE(I18;MIN(K18;L18))))) which works
what i wanted to do was return it as N/A in the first IF condition if the item lacked sell orders.
I also added a new column in the make sheet. It shows the magnitude of the average of of sell/buy orders as a log function and the ratio between sell and buy orders.
Oh i also messed with your bpc names, i put the 1run/300run at the front instead of the back so its easier to see and allows me to shrink the column width.
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.08.27 13:36:00 -
[28]
Edited by: Lore Varan on 27/08/2010 13:45:14
Originally by: Val Ashir
Edited by: Val Ashir on 27/08/2010 08:35:30
Awesome spreadsheet
Ive been using the modules spreadsheet and i get macro errors when i changed the formula in the eve-central sheet to eliminate non-buyable items.
atm it sits as =IF(sellmin=0;999999999; IF(RIGHT(TRIM(item name);2)="II";MAX(buymax;sellmin);MAX(AVERAGE(buymax;MIN(sellmed;sellavg));AVERAGE(I18;MIN(K18;L18))))) which works
what i wanted to do was return it as N/A in the first IF condition if the item lacked sell orders.
I also added a new column in the make sheet. It shows the magnitude of the average of of sell/buy orders as a log function and the ratio between sell and buy orders.
Oh i also messed with your bpc names, i put the 1run/300run at the front instead of the back so its easier to see and allows me to shrink the column width.
Use
=IF(I2=0;NA(); IF(RIGHT(TRIM(A2);2)="II";MAX(E2;I2);MAX(AVERAGE(E2;MIN(K2;L2));AVERAGE(I2;MIN(K2;L2)))))
How did you get the volume information into the make sheet ?
LOOKUP are are you diddling with the macros as well ?? :)
Glad you getting good use out of it.
Good idea on the bpc names.
If I ever manage to get around to v1.1 I'll make that the default.
|
Val Ashir
|
Posted - 2010.08.27 17:02:00 -
[29]
Edited by: Val Ashir on 27/08/2010 17:06:11
The volume column i made uses uses 2 additional columns to look everything up.
They fetch the buy/sell volumes using =VLOOKUP(TypeID;'Eve-Central'.$B$4:$M$1314;12) for the sell volume and =VLOOKUP(Typeid;'Eve-Central'.$B$4:$M$1314;7) for the buyvolume.
then its just =LEFT(LOG(AVERAGE(buyvol;sellvol));4)&", "&LEFT(sellvol/buyvol;3) in a visible column beside the other stuff
|
Lore Varan
Caltech Shipyards
|
Posted - 2010.09.14 16:34:00 -
[30]
Bumpage |
|
|
|
|
| |
|
| Pages: [1] 2 3 4 :: one page |
| First page | Previous page | Next page | Last page |