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.
 

Spreadsheet - importing blueprint material

First post
Author
Previous Topic Next Topic
YoHoBro
Doomheim
#1 - 2015-05-25 16:25:56 UTC
I am trying to make a market spreadsheet. I think I have the basic market stuff down, but I would like to be able to include a column showing the build price for an item. Apart from manually looking up and multiplying the mineral costs for each item against the amount of units needed, is there a way I can import blueprint data into excel?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2015-05-26 11:28:55 UTC
YoHoBro
Doomheim
#3 - 2015-05-26 22:32:26 UTC
Here is what I have so far (thanks to your Fanfest presentation and documentation): https://docs.google.com/spreadsheets/d/1AJ2ZB4c3qGDEbJ1LiZwISoH50y6CsR4SilBcPwgDxfQ/edit?usp=sharing

My problem is with the Material prices. I want to be able to see the material prices of my product and compare it to the buy and sell prices. Each time I change my item in the price load sheet, a new number of material entries appear and causes an error.

Is there any way around this, other than making a field for absolutely all materials?

Thanks for your response - and for your Fanfest presentation.
Jai Blaze
Honor Forge
Joint Operation Involving Nobodys
#4 - 2015-05-27 12:35:45 UTC
I'll be honest. This is a problem that has taken me a long time to solve myself, but solving it gives me an edge on the production and marketing field.

Which is probably why nobody is completely forthcoming with the answer to your question because to any player whos income relies on their sheets, they are literally increasing their competition everytime they help out another manufacturer. Doing so on a thread where even MORE people can make use of their hard work and cut out more of the pie.. it's just not something I or many other manufacturers are willing to do.

I can offer some direction though. It takes a long time to set up, but if you work at it (like I did for months), learn to use vlookup() and web queries. These two functions/features are the bread and butter of my spreadsheets.

I'm also constantly refining my sheets, it's an ongoing battle that I'm sure any spreadsheet designer wages almost daily.

Don't be afraid of long formulas either. I have formulas that literally span my entire screen, left to right, top to bottom. But it makes them accurate to in-game calculations.

I'm half-sorry I'm not much more help than that, but this is manufacturing PvP, and I've got my own war to wage.
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
#5 - 2015-05-27 19:52:54 UTC  |  Edited by: Charlie Nonoke
The problem you have is because you aren't giving enough room for the material list to populate on your Material Prices tab.
Some material requirements will occupy as many as 20 rows, give or take, and your function won't allow it to populate because it does not want to overwrite the data you've filled underneath it.

A solution is to allow for more than adequate room for each entry. This method also allows consistency, as you may choose to set each entry being 30 rows, and you can populate all your entries systematically then.

e.g. On that "Material Price" tab, let A1 be your first entry of the list, A31 be the second... etc...

This way, you can allow any entry to be replaced, and still have the same results.
Back on your main tab, on your AB column, do a vlookup instead of manually specifying the SUM.
YoHoBro
Doomheim
#6 - 2015-05-27 20:27:30 UTC
Charlie Nonoke wrote:
The problem you have is because you aren't giving enough room for the material list to populate on your Material Prices tab.
Some material requirements will occupy as many as 20 rows, give or take, and your function won't allow it to populate because it does not want to overwrite the data you've filled underneath it.

A solution is to allow for more than adequate room for each entry. This method also allows consistency, as you may choose to set each entry being 30 rows, and you can populate all your entries systematically then.

e.g. On that "Material Price" tab, let A1 be your first entry of the list, A31 be the second... etc...

This way, you can allow any entry to be replaced, and still have the same results.
Back on your main tab, on your AB column, do a vlookup instead of manually specifying the SUM.


Thanks. But what about the error I get when the material list is shortened? It doesn;t do the calculation because there are "REF" errors, and I have to manually empty the slots..
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
#7 - 2015-05-27 21:09:05 UTC
That is what I'm talking about.
Your REF error says "Array result was not expanded because it would overwrite data in XXX".
YoHoBro
Doomheim
#8 - 2015-05-28 14:21:13 UTC  |  Edited by: YoHoBro
EDIT: Fixed it by using the "=IFERROR" formula. Thanks!

Thanks for the tips. I have updated the spreadsheet. However, I still have a REF error in my 'Material prices' sheet. Because of the variability in the amount of items, I get error messages. Would you know of a fix?

Maybe the problem is with the query formula that I am using. I am using:

"=query('price source'!A40:N206, "select F where A = '" &B10 & "'", 0)".

This is something I took from the forums.

I would have thought that I would use

"=query('price source'!A40:N206, "select F where A ="&B10, 0)"

But for some reason that does not work.
Oriella Trikassi
Trikassi Enterprises
#9 - 2015-05-29 13:41:57 UTC
Spreadsheeting is fun isn't it!

Mine uses Excel which currently behaves itself if the Eve Databases are cooperating - sometimes they don't.

Being a small Corporation we acquire blueprints slowly, so manually typing in required materials isn't a major chore unless CCP Fozzie changes them in which case ugh. Then the Sheet adds manufacturing cost and taxes, pulls down prices from eve-central and looks for profits.

The XML tables that vary in size such as Corp Assets I keep on separate pages so they can't overwrite anything.

VLOOKUP is certainly your friend, as on Excel is the more complex INDEX MATCH for extracting data. The formulas do indeed get complicated:

{=IFERROR((INDEX(OriAssets!$G$1:$I$5502,MATCH(1,(OriAssets!$G$1:$G$5502=60003760)*(OriAssets!$H$1:$H$5502=C4),0),3)),0)}

"Look in this array for the number of widgets in the hangar at Jita. If there are none, just put 0"
YoHoBro
Doomheim
#10 - 2015-05-29 15:46:14 UTC
Thanks for the help everyone.

I am trying to make categories of documents. I used the file provided in this post, but I think it may be out of date: https://forums.eveonline.com/default.aspx?g=posts&m=112780

For example, Cruisers are GroupID # 26. While I do have a number of cruisers fitting that group ID, I also have items such as Amarr Standard Station
Planet (Oceanic)
Co-Processor II
Angel Predator
Deadspace Control Station
Vigilant Blueprint
Apocalypse Navy Issue Blueprint
Megathron Navy Issue Blueprint
Tempest Fleet Issue Blueprint
Nightmare Blueprint
Machariel Blueprint
Crimson Hand Level 2 Passcard

Is there an updated list?
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
#11 - 2015-05-29 21:20:39 UTC  |  Edited by: Charlie Nonoke
https://www.fuzzwork.co.uk/dump/latest/
Look for invTypes.xls

It has everything you need, for matching TypeIDs to its name.
TypeIDs to its GroupID. Even lists its mass and volume.

All entries in GROUPID, i.e. Column B with values 26 are all Cruisers.
I don't see anything other than Cruisers.