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 help

First post
Author
Previous Topic Next Topic
FRISKIESFORISKIES
Mors et Vectigalia
#1 - 2017-01-05 04:30:00 UTC
So, I've decided I need to work on making a good PI plus other things spreadsheet...

I have written a bunch in excel so I am pretty familiar with that program but this will be my first for eve.

So, what is the best method to import accurate prices? just the regular API calls or do I need to start learning crest or who knows what else to do this right?

What I want to do will be a tad bit complicated so figure I better do a bit of research first... I'll be trading in large quantities so the plan will be to find a way to load say the first 10 orders of say widgets then use my spreadsheet to see how many widgets are for sale in said orders and do the math for how much it would cost to actually buy X amount of widgets

So say the first order is for 20 widgets for 20ISK, the second for 200 for 21ISK and the next is 10 for 22 and the final would be 3000 for 23ISK

I want the spreadsheet to look at those quantities and if I want 300 widgets it will do the math and say I need 20@200, 200@21, 10@22, and lastly 70@ 23 then add up those amounts.

It should be possible to do if I can import the top say 10 or 20 orders of a given item, and still it will be an estimate... but it will be a more accurate estimate than if I just took the cheapest order since I may be taking all the items from 10 or 20 or however many orders which can and will significantly change the price... Nor would it be better to take the median or the average since some people may like hundreds of thousands of items at ridiculously high or low prices that have no real chance of ever getting filled.... they are making those orders in case of someone screwing up...which is fine, but in my case it will throw off estimates if I use median or average prices.


Anyway any advice on how best to go about this? If need be I can write a web app for it but I want to do it in the easiest way. hopefully that wont require me learning JSON since I dont know it lol but if needed I will learn it.


Also should be noted if it requires a SSO I'm unsure If thats even possible with excel... I havent messed around with any SSOs so I'll have to learn about that as well.
Caesar Shepherd
Juro Political Extraction
#2 - 2017-01-05 05:31:04 UTC
Yes it is possible to get the current market orders from specific stations, or regions using the CREST API. The tutorials out there seem a bit outdated or incomplete but nevertheless I've had some success using the API.

Here's the documentation for CREST. It leaves a lot to be desired but is a good place to start. http://eveonline-third-party-documentation.readthedocs.io/en/latest/crest/

I find it very useful to manually walk through the CREST hierarchy to become familiar with its structure. Here's an example:

You can plug the API root address https://crest-tq.eveonline.com into your web browser (only tested on firefox) and it will download a JSON file which may not even have the .json extension. This is just plain text, so you can read it with notepad or any editor. You can also download the page using the wget command if you're on a Linux machine but I'm sure theres a windows command out there too. Inside that file, you should see something like this (truncated):


...
"races": {
    "href": "https://crest-tq.eveonline.com/races/"
},
"regions": {
    "href": "https://crest-tq.eveonline.com/regions/"
},
"serverName": "TRANQUILITY",
"serverVersion": "EVE-TRANQUILITY 14.11.1108313.1108313",
"serviceStatus": "online",
...


You can follow the href links inside to other pages, and these pages will contain their own href links. As another example, here's how to get all the order in the Amarr region:


root -> "https://crest-tq.eveonline.com"
"regions" -> "https://crest-tq.eveonline.com/regions/"
"Domain" -> "https://crest-tq.eveonline.com/regions/10000043/"
"marketOrdersAll" -> "https://crest-tq.eveonline.com/market/10000043/orders/all/"


Note that this is multi-page, you will have to follow the "next" link until you've read all the orders. There will be a station ID number and item ID number with each market entry, so you can filter out the results to only include the items and stations you want. The ID numbers are also buried in other pages of the API, but I've only found some of them so far.

As a final note, you can also use a third-party API which may be a bit easier to use. market.fuzzwork.co.uk is a good third party API.

This probably isn't relevant to you since you're using Excel, but libcurl (-lcurl) a good C++ library for making HTTPS GET requests. https://curl.haxx.se/libcurl/c/ftpsget.html

I know this response was more about CREST and less about Excel, but I hope you find it useful!

Caesar
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2017-01-05 23:59:37 UTC