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.
 

Pulling Material Costs for Blueprints Into Excel?
Author
Previous Topic Next Topic
Volkan Alkaid
Deep Core Mining Inc.
Caldari State
#1 - 2016-12-12 23:00:04 UTC
I'm trying to pull input materials from BPOs off the fuzzworks (or any, but it seems to work) databases. But honestly I'm lost.

What I'd like to be able to do is be able to pull up material costs for any BPO. And have it all out in a nice spreadsheet in front of me. For example:

Mexallon Pyerite Tritanium Etc

Frigate 1 12 312 4444
Firgate 2 11 222 2222

I've gotten as far as knowing I probably need something from https://www.fuzzwork.co.uk/dump/latest/ but i have no clue how to use it.

And please, explain it as you would to a small child or a Golden Retriever.
Mephiztopheleze
Laphroaig Inc.
#2 - 2016-12-12 23:03:12 UTC
Volkan Alkaid wrote:
And please, explain it as you would to a small child or a Golden Retriever.


Howl, pant, bark, bark, pant, sniff bum, lick balls, howl, pant, bark, woof, wag tail, chase tail, howl, slobber, drool.

Occasional Resident Newbie Correspondent for TMC: http://themittani.com/search/site/mephiztopheleze

This is my Forum Main. My Combat Alt is sambo Inkura
Haffsol
#3 - 2016-12-12 23:07:40 UTC
Professor Humbert
Project Fruit House
#4 - 2016-12-13 10:19:04 UTC
You want to import industryActivityMaterials.csv to your sheet.
This gives 4 columns, information on BP typeID / activity / required input material typeID for the activity / each input material qty.
Then you need to query the typeIDs against invTypes sheet (yeah import that csv as well) and get easily readable typeName.
Alberik
Eusebius Corporation
#5 - 2016-12-15 17:42:53 UTC
I made a Workbook with a button with the following code


Sub QueryPriceList()
    Range("C4").Select                                                  'select a cell with a external query within
    With Selection.QueryTable
        .Connection = "URL;" & Worksheets("Query").Range("C3").Text     'exchange the old query text with the one you need
        .WebSelectionType = xlEntirePage                                 ' dont know if i need those ... 
        .WebFormatting = xlWebFormattingNone                                          
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    Selection.QueryTable.Refresh                ' excute query
    Call TransferPrices(True)                        ' a sub to get the latest data into my own database
End Sub


have some sheets with lots of automated vlookup-functions to show me all i want
if I change some dropdown-menus the cell "C3" will calculate the new query to get info on all items needed to build the selected items
typical string looks like that:
"http://api.eve-central.com/api/marketstat?systemID=30000142&typeid=34,35,36,37,38,39,40,11399,11690,9848,574,0,,,,,,,,3090"

Note: Eve central will ignore empty query items, but i got my data from other sites earlier so its still in the code and it still works .. so i dont care

Cell "C4" needs a Query within. Im using excel2000 so a little outdated, may be else where today
MenuItem Data->Get External Data->New Web Query->"Fill anything in, the macro above will replace it"