| Pages: [1] :: one page |
|
|
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Lateralus
War Supplies Inc
1
   |
Posted - 2014.08.01 22:50:00 -
[1] - Quote
Is this a thing of the past?
I've been doing Invention and T2 manufacturing for a few years now, I built a pretty simple spreadsheet which I was quite proud of that keeps track of the ~100 T1 blueprints I own and the corresponding T2 invention process.
With Crius, I'm having a hard time keeping up with the changes, I took a month off work and did a road trip pre-crius and I'm looking at setting up a new spreadsheet for Crius... however, I'm running into some problems.
It seems like I need programming skills beyond Excel to be able to do this in any scale now. I'm having a hard time figuring out what the entire cost of T1 Production / T2 Invention / T2 Production is going to cost me without actually going through the motions of doing it.
The reason I built my first spreadsheets was to keep track of what items were profitable by plugging in all the variables to figure it out myself what would be profitable BEFORE I built it.
I can't see any way of doing this now, without the use of APIs (I'm great with Excel, but have rarely had to pull data from the internet into Excel, so I'm not good with how CREST or other APIs work)
It seems there is two modes of doing T2 production:
1) Simple, GUI based, and hope it's profitable for people that are new to manufacturing, or...
2) Computer Programming required API usage with custom tools.
There doesn't seem to be anything in between. Would it be possible to still build a simple spreadsheet for production workout without relying on APIs? Does anyone still do this that can share how they built it?
|
Qoi
Exert Force
55
|
Posted - 2014.08.02 01:01:00 -
[2] - Quote
If you do not use external APIs, there are two problems you will run into.
* Calculating the Base Job Price (based on the adjusted prices published via CREST)
* Calculating the System Cost Index (published via CREST)
If you use Google Spreadsheets you can use =IMPORTXML() together with the API i published today ( http://api.eve-industry.org ), it does not really require programming expertise. I am sure Excel can do something similar. Or you just copy&paste the values from the API pages.
If you really want to do this with only ingame information, the system cost index can be gathered in game if you go to the star map and select the system cost index overlay. The precision is unfortunately only one significant digit for many systems, at least last time i checked.
The following is more a rube-goldberg machine than a real workflow.
If you have the system cost index, you can go to the industry window and get a quote for a manufacturing job in that system. You then divide by 1.1 to remove the NPC tax and divide again by the system cost index to get the base job price. Now you can calculate the manufacturing fee for other systems/facilities with a spreadsheet.
Furthermore, i respectfully request these forums to be upgraded to support preformatted text. |
Lateralus
War Supplies Inc
1
|
Posted - 2014.08.02 03:14:00 -
[3] - Quote
However simple you may think your example usages are, they're invaluable for me in figuring out how an API works. It actually seems simpler than I thought. [EDIT: this example usage stuff is exactly what I was missing in all these forum threads, I must have read 20 threads before posting my own, never understood it till you posted that page.]
Any idea how to easily get T1 and T2 BPO info into a spreadsheet? I slowly built a spreadsheet by manually inputting the material requirements every new T1 BPO I got and researched, now that data has changed, I have to manually re-enter the info, or somehow import it.
Thank you for putting that API page together. |
Electrified Circuits
The Ascended Fleet
Intrepid Crossing
17
|
Posted - 2014.08.02 05:14:00 -
[4] - Quote
Thanks Q, your site is my new go to place for data etc and these api are very usefull. Appreciate what you've done for crius crossover |
Edman Torillio
Imperial Academy
Amarr Empire
2
|
Posted - 2014.08.02 15:34:00 -
[5] - Quote
Qoi wrote:If you do not use external APIs, there are two problems you will run into. If you use Google Spreadsheets you can use =IMPORTXML() together with the API i published today ( http://api.eve-industry.org ), it does not really require programming expertise. I am sure Excel can do something similar. Or you just copy&paste the values from the API pages.....
I use excel vs google docs, simply because for what I was doing with it, it was too slow ( or my spreadsheets too complex ).
One feature that is missing from Excel that google docs makes easy is ImportXML. There is a way to do it in excel by default, but i find it lacking in the simplicity that google docs offers.
Here is what I do. In Excel open the Microsoft Visual Basic for Applications (accessed through the developer tab in Excel, which you may have to unhide since Excel does not display this by default.). Once you have it open, you will need to insert a new module, then simply copy+paste the function that is at the end of this post, and save the excel document as a MACRO enabled document, extension (.XLSM). This function will allow you now ala google docs, type in =ImportXML("url","string") to get the information you want (pricing) or using one of the many JSON to XML converted API's to call up CREST API for job data.
There is one flaw to Excel's UDF for ImportXML; it does not auto refresh. For me I use an Excel cell for my location and typeid's, so all I have to do is delete the cell location that the formula is pointing to and put it back in and it refreshes it.
In addition there is probably a limit on how many of these you can easily use in your document before it starts misbehaving. I use no more than about 100, which works fine for me.
Example Usage: =importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34","/evec_api/marketstat/type/sell/min")
This gives you the price at Jita, for typeid 34 which is tritanium, at the minimum sell order price. for buy order you would use /evec_api/marketstat/type/buy/max
I do not take original credit for this function, although I did modify it to fit same functionality as Google Docs.
Public Function ImportXML(xmlUrl As String, xmlPath As String) As Double
Dim xmlHttp
Set xmlHttp = CreateObject("Microsoft.XMLHTTP")
Call xmlHttp.Open("GET", xmlUrl, False)
Call xmlHttp.send
Dim xmlDoc
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"
xmlDoc.LoadXML xmlHttp.responsetext
xmlDoc.setProperty "SelectionLanguage", "XPath"
Set ndOutput = xmlDoc.DocumentElement.SelectSingleNode(xmlPath)
ImportXML = ndOutput.Text
End Function
|
Selaria Unbertable
POS Mortem
Renegades Of Silence
41
|
Posted - 2014.08.02 19:46:00 -
[6] - Quote
Qoi wrote:If you do not use external APIs, there are two problems you will run into. * Calculating the Base Job Price (based on the adjusted prices published via CREST) * Calculating the System Cost Index (published via CREST) If you use Google Spreadsheets you can use =IMPORTXML() together with the API i published today ( http://api.eve-industry.org ), it does not really require programming expertise. I am sure Excel can do something similar. Or you just copy&paste the values from the API pages. If you really want to do this with only ingame information, the system cost index can be gathered in game if you go to the star map and select the system cost index overlay. The precision is unfortunately only one significant digit for many systems, at least last time i checked. The following is more a rube-goldberg machine than a real workflow. If you have the system cost index, you can go to the industry window and get a quote for a manufacturing job in that system. You then divide by 1.1 to remove the NPC tax and divide again by the system cost index to get the base job price. Now you can calculate the manufacturing fee for other systems/facilities with a spreadsheet.
Oh, this looks so nice :)
If I hadn't gone through the process of writing the code for the CREST endpoint, I would totally use this :D
|
Lateralus
War Supplies Inc
2
|
Posted - 2014.08.02 22:31:00 -
[7] - Quote
Edman.... you deserve a medal. |
Edman Torillio
Imperial Academy
Amarr Empire
4
|
Posted - 2014.08.03 01:32:00 -
[8] - Quote
Lateralus wrote:Edman.... you deserve a medal.
Just so you know I decided to help you because I like your name ( and the band that it reminds me off ) :) haha. But best of luck in your Industrial desires and goals. |
Lateralus
War Supplies Inc
2
|
Posted - 2014.08.03 01:41:00 -
[9] - Quote
Tool's older stuff is great, not too sure about the new stuff though. |
Destitute Tehol Beddict
ROC Incorporated
The ROC
28
|
Posted - 2014.08.03 03:51:00 -
[10] - Quote
or you can use built in excel functionality and use an .iqy file...
Crius Reprocessing Spreadsheet 1B --áhttps://forums.eveonline.com/default.aspx?g=posts&m=4829122#post4829122
Loot Buying Service --áhttps://forums.eveonline.com/default.aspx?g=posts&m=4529397#post4529397 |
|
|
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
3628
|
Posted - 2014.08.03 14:20:00 -
[11] - Quote
Eve central prices into excel (and google docs)
https://www.fuzzwork.co.uk/2013/06/22/importing-price-data-into-spreadsheets/
Crest price indexes into excel (should be pretty simple to extend to also pulling in price data. the code is pretty simple)
https://www.fuzzwork.co.uk/2014/07/26/excel-and-crest/
May be of interest.
With excel 2010 and up, there's also filterxml() which works pretty much like importxml does.
http://office.microsoft.com/en-gb/excel-help/filterxml-function-HA102781000.aspx
Woo! CSM 9!
http://fuzzwork.enterprises/
Twitter: @fuzzysteve on Twitter |
Kale Freeman
Dirt 'n' Glitter
I Whip My Slaves Back and Forth
29
|
Posted - 2014.08.04 09:05:00 -
[12] - Quote
Lateralus wrote:However simple you may think your example usages are, they're invaluable for me in figuring out how an API works. It actually seems simpler than I thought. [EDIT: this example usage stuff is exactly what I was missing in all these forum threads, I must have read 20 threads before posting my own, never understood it till you posted that page.]
Any idea how to easily get T1 and T2 BPO info into a spreadsheet? I slowly built a spreadsheet by manually inputting the material requirements every new T1 BPO I got and researched, now that data has changed, I have to manually re-enter the info, or somehow import it.
Thank you for putting that API page together.
Re: ... inputting the material reqrements...
You can right click the little circles (>) on the arrows to the left of the big circle in the middle of the UI screen. That is quite a horrible sentence but I don't know how to simplify it. Anyway, setup the number of runs you want, right click the appropriate little (>) circles and copy the input requirements to clipboard.
Paste it into a spreadsheet. It's already nicely laid out with typeIDs and everything. |
Rutger Janssen
Xanadu
135
|
Posted - 2014.08.04 09:28:00 -
[13] - Quote
For T1 qnd T2 I made a simple c# app that parses blueprints.yaml and connects to mssql database where I imported the static datadump. With that info I have it create a table with each row for an item and a column for each used material(1 column for T1 item for t2 production, amount of columns would otherwise explode) and statistic I need.
I can then copy this this to my google docs page. |
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
3629
|
Posted - 2014.08.04 11:24:00 -
[14] - Quote
If you want the material requirements
https://www.fuzzwork.co.uk/dump/crius-1.6-100370/industryActivityMaterials.xls.bz2 (use 7zip to uncompress) should cover you.
blueprint id, activity (1 for manufacturing), material typeid, quantity
https://www.fuzzwork.co.uk/dump/crius-1.6-100370/industryActivityProducts.xls.bz2 for the output.
Woo! CSM 9!
http://fuzzwork.enterprises/
Twitter: @fuzzysteve on Twitter |
Lateralus
War Supplies Inc
3
|
Posted - 2014.09.19 23:23:00 -
[15] - Quote
Ok Fuzz, got a question on Excel for you - how can I take that IndustryActivityMaterials.xls and have excel format the data as blueprints in the first column, then material types in the following colums, with the rows showing the quantities of each? I'm a vlookup king, but I can't figure out how to get this to work with my current spreadsheet.
so like...
BP TypeID Tritanum Pyerite Mexallon etc etc
683 22222 8000 2444 etc etc
The info's all there that I need, I'm just having trouble figuring out how to format it this way without manually copy/pasting it in for all the BPOs I own. |
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
3856
|
Posted - 2014.09.20 15:58:00 -
[16] - Quote
That kind of thing is always annoying to do. Especially due to the number of possible materials. What you need to have is a pivot table.
You can stick the typeid into the pivot as a filter, so you can pick the typeid (or stick in a vlookup and use the name) and only get the relevant columns.
Woo! CSM 9!
http://fuzzwork.enterprises/
Twitter: @fuzzysteve on Twitter |
Myfanwy Heimdal
Heimdal Freight and Manufacture Inc
368
|
Posted - 2014.09.20 18:16:00 -
[17] - Quote
Edman Torillio wrote:Lateralus wrote:Edman.... you deserve a medal. Just so you know I decided to help you because I like your name ( and the band that it reminds me off ) :) haha. But best of luck in your Industrial desires and goals.
Now I am intreagued. Which band is this, pray?
- Myfanwy
p.s. Great thread.
Pam:-á I wonder what my name means in Welsh?Nessa: Why? |
Electrified Circuits
Lynx Inc
Northern Associates.
19
|
Posted - 2014.09.22 15:33:00 -
[18] - Quote
Yup you do it with pivot tables, I made my own T2 spreadsheet that lists the top 200 items in profit margin and in isk/hr , took me a day today hard work but very happy with it.
The problem with using pivot tables and this amount of data is it takes a long time to load all the settings so use seperate worksheets one for pulling prices, one for ingrediants etc. |
Lateralus
War Supplies Inc
3
|
Posted - 2014.09.22 18:17:00 -
[19] - Quote
Myfanwy Heimdal wrote:Edman Torillio wrote:Lateralus wrote:Edman.... you deserve a medal. Just so you know I decided to help you because I like your name ( and the band that it reminds me off ) :) haha. But best of luck in your Industrial desires and goals. Now I am intreagued. Which band is this, pray? - Myfanwy p.s. Great thread.
The band is 'Tool'
http://toolband.com/
'Lateralus' is from the CD 'Lateralus', my favorite songs were some of their older stuff - Forty Six & 2, Schism, Eulogy, etc.
|
Lateralus
War Supplies Inc
3
|
Posted - 2014.09.22 18:27:00 -
[20] - Quote
Steve Ronuken wrote:That kind of thing is always annoying to do. Especially due to the number of possible materials. What you need to have is a pivot table.
You can stick the typeid into the pivot as a filter, so you can pick the typeid (or stick in a vlookup and use the name) and only get the relevant columns.
Ah, yes that'll work. Is it just me or are pivot tables harder to work with in the new version of Excel?
Thanks again, and thanks for the years of help with your website - your site is invaluable to industry. |
|
|
Lateralus
War Supplies Inc
3
|
Posted - 2014.09.29 22:07:00 -
[21] - Quote
Qoi wrote:If you do not use external APIs, there are two problems you will run into. * Calculating the Base Job Price (based on the adjusted prices published via CREST) * Calculating the System Cost Index (published via CREST) If you use Google Spreadsheets you can use =IMPORTXML() together with the API i published today ( http://api.eve-industry.org ), it does not really require programming expertise. I am sure Excel can do something similar. Or you just copy&paste the values from the API pages. If you really want to do this with only ingame information, the system cost index can be gathered in game if you go to the star map and select the system cost index overlay. The precision is unfortunately only one significant digit for many systems, at least last time i checked. The following is more a rube-goldberg machine than a real workflow. If you have the system cost index, you can go to the industry window and get a quote for a manufacturing job in that system. You then divide by 1.1 to remove the NPC tax and divide again by the system cost index to get the base job price. Now you can calculate the manufacturing fee for other systems/facilities with a spreadsheet.
Qoi, you were right, this is exactly the problem I have run into. |
Tinu Moorhsum
Random Events
358
|
Posted - 2014.09.30 22:04:00 -
[22] - Quote
Lateralus wrote:
It seems there is two modes of doing T2 production:
1) Simple, GUI based, and hope it's profitable for people that are new to manufacturing, or...
2) Computer Programming required API usage with custom tools.
Or use a caluculator and work out a rough approximation of the worst case scenario.
spreadsheets are important if you want to answer the question, "what should I be doing" very precisely. However, it's not the only question that you could be asking.
T-
|
Lateralus
War Supplies Inc
3
|
Posted - 2014.10.03 16:24:00 -
[23] - Quote
So, to wrap up my own thread...
Yes, you can do T2 Invention / Production with an Excel Spreadsheet, but you need to learn a few things.
API calls to get:
System Cost Index from here: http://api.eve-industry.org/system-cost-index.xml?name=Osmon
Adjusted Price from here http://api.eve-industry.org/
I personally use a Google Doc spreadsheet to pull prices from eve-central
Most of this can be researched on these forums, especially thanks to Qoi with this thread & that site/PDF linked in it as mentioned above, give Qoi an upvote/like on that thread:
https://forums.eveonline.com/default.aspx?g=posts&t=362493&find=unread
Thanks to everyone for their help in getting me up to date with Crius stuff. I'm scared of the changes coming to different outputs to invention though, that adds a crazy amount of complexity to T2 BPC outputs. It'll be very hard to figure out what you're going to get, in my view, it's already a hard enough thing to figure out, and a barrier to entry. The proposed new changes will be ridiculous to code into a spreadsheet or tool. |
Lateralus
War Supplies Inc
3
|
Posted - 2014.10.03 16:27:00 -
[24] - Quote
Oh, and to add, to make it a dynamic spreadsheet that doesn't have everything manually entered, I pulled a pile of things from fuzzwork's website static data dump:
https://www.fuzzwork.co.uk/dump/
Props to you fuzz, keep kicking ass in that CSM spot. |
Sheri Angela
18
|
Posted - 2014.10.03 19:58:00 -
[25] - Quote
You can use xml functions in Excel 2010+ to access the nice API service Qoi built. I used it for a short bit in my validation spreadsheets that I used when updating my web application. I'd recommend using it for a short bit, but since this is a game don't leave yourself dependent on other's service when RL hits them. Fuzz linked a possible solution for parsing JSON in Excel and there are several other solutions out there just for Excel.
TIDI = Increasing profit while decreasing service level to the customer disguised a nicely marketed benefit. What would Amazon have done here. |
Rumbaldi
Reasonable People
Of Sound Mind
5
|
Posted - 2014.10.03 22:39:00 -
[26] - Quote
Some of the stuf fon here makes me think i am readign a foreign language, kudos to all you people who code/program/write up these spreadsheets, be it a website or google docs or excel. |
|
|
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |