These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Import a price in Google sheet

First post
Author
Previous Topic Next Topic
Tecka Grethys
Disaster Area
DISASTER Delivery Service
#1 - 2015-02-11 10:28:22 UTC
This is making me crazy !

I've been trying to import a price in my google sheet for a few days, went through the forums, eve-market website, fuzzwork's stuff and still... I did not manage to do it. Cry

In cell C12, I have "Isogen".
In cell B12, I want to import the average price in the universe, a representative price. I'm not hunting 0,0001 isk.

For the example, let's say :
- my API key ID is 3035842
- my verificatino code is Sp2N0ZPqji
- my name is Tecka Grethys (I insist on that because I don't know what to do with the space character nor with the capital letters)

(I have few prices to import so I should not reach the limit of 50 imports)

What should I write in the B12 cell ?? Blink
Ortho Loess
Escalated.
OnlyFleets.
#2 - 2015-02-11 12:09:24 UTC
You can't get that kind of thing from the XML API (the one that has keyIds and verification codes)

The data you want sounds like it should be fine coming from eve-central though. They have an API that's good for such things.

https://eve-central.com/home/develop.html
Tecka Grethys
Disaster Area
DISASTER Delivery Service
#3 - 2015-02-11 12:13:31 UTC
Yeah, I read that too but I always get a #ERROR! result in my B12 cell. I cannot figure out what is the correct syntax to use. Ugh
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#4 - 2015-02-11 17:49:36 UTC
An average price over the entire universe isn't really possible with the resources available.

Regional works. And I'd suggest using the percentile from eve central for that (it's an average of the lowest 5%)

https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs is a way to pull prices. If you take a look at the example sheet, you'll see the basics of how to use it.

Now, The first problem you'll have is: You have Isogen. You need the typeid of isogen.
To solve this, you import something like https://www.fuzzwork.co.uk/resources/typeids.csv into a new sheet. call it typeids.

To go from the name, to the typeid, you would use vlookup. Something like (going from memory)
=vlookup(C12,typeids!B:C,2,false)

You can put that into something like D12, or if you want it to be neater, create a new sheet for it. If you do that, then C12 would need to become mainsheetname!C12 where mainsheetname is the name of the sheet that the C12 you care about is on.

Now, the function I have there isn't suited to pulling a single price. So if you have multiple, just make sure all the typeids show up in a list on a sheet (hence the neater method. where you have a sheet that's just a list of typeids to look up)

Once you have a list, create a new sheet to import the price data into. It grabs all the data from eve central for those items, to keep down the number of calls to eve central. Just being a good neighbour. If you don't create a new sheet, you stand a good chance of overwriting something.

Then, to load data from the forge (it's a default), in A1 of that new sheet:
=loadRegionPrices(mytypeidsheet!A1:A28)

Where mytypeidsheet is the sheet with your list of typeids. and A1:A28 is the list of types. It's not limited (particularly) in how big it is.

Once you have that, you're finally ready to fill in B12.

=vlookup(vlookup(C12,'typeids'!B:C,2,false),'mypricesheet'!A:O,15,false)

And that will get you the percentile sell price.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Tecka Grethys
Disaster Area
DISASTER Delivery Service
#5 - 2015-02-12 08:15:12 UTC
OMG, this finally worked ! Big smile

I even managed to get more than 50 lines from the LoadRegionPrices fonction, which I do not understand. I thought I was limited to 50.

Fortunately, random players like me can rely on competent geeks like you to enhance the game experience. Thanks !
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#6 - 2015-02-12 12:55:13 UTC
LoadRegionPrices doesn't use importxml :)

It grabs things in batches of 150 (or so) and uses an alternate method to grab them. Then it iterates through them, and returns the data. Far more efficient.

And handy, when you want to get, say, buy and sell prices. As it doesn't have to grab it twice.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter