ESI - pull full market history for hub regions

[Alacta_Lithia]

After not playing Eve for about 2 years finally decided to give it a try again, which means it was time to update how I get market data.

So I have managed to use ESI for pulling all market data (/markets/{region_id}/orders/) into excel with power query for the main hubs.

Then I went to look for a dump of market history data as I did not need anything current, just needed to see which items have movement, but was not able to find anything recently available.

So now I am trying to pull all market history data (/markets/{region_id}/history/) into excel with power query.

Got the power query to work linked to a table with all the TypeID that only include those that are published / on the market. But I have not managed to pull a whole region, it will pull anywhere from 15k – 22k records and give me an error.

The error messages change all the time, 502 bad gateway, sometimes 500, sometimes it gives me an error it can not find the table which I assume is excel issue. So thought maybe I am looking up too many items (712), and if I change it to fewer it is less likely to give an error but will take a long time or have to setup multiple queries to pull all 712 items for each hub. Thought that maybe I am requesting the data too quickly, so managed to work out how to put in a delay between pulling the data, but that has made no difference.

Anyone else had problems pulling all market history with ESI with power query as I have run out of things to try to get it to work. Strangely I can pull the item data for a region which is 10 000 records per page and 30 pages if The forge with out any issue.

Thanks

[Blacksmoke16]

Try the queries in chrome and see if you have issues, otherwise it might be something with the way you are doing/setting up the query in excel.

[Alacta_Lithia]

it works in Chrome so the web link is correct.

If I change it to only a couple of items to check, the data comes through, this is the reason I thought it is the amount of data I am trying to pull or how quickly. But it gives different errors and stops at different points, I have tried everything now that I can think of.

Not my area of expertise, maybe someone with more experience notices something if I have done it wrong.

The following query is used for the connection, the IDs are passed from the second query table.

GettypeID

(STypeID as number) as table =>
let
    Source = ()=> Json.Document(Web.Contents("https://esi.tech.ccp.is/latest/markets/10000002/history/?datasource=tranquility&type_id=" & Number.ToText(STypeID) & "")),
    #"Converted to Table" = Table.FromList(Function.InvokeAfter(Source,#duration(0,0,0,2)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "order_count", "volume", "highest", "average", "lowest"}, {"date", "order_count", "volume", "highest", "average", "lowest"})
in
    #"Expanded Column1"

The following query is used linked to the table.
JitaHistory

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "TypeIDList"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each GetTypeID([TypeIDList])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"date", "order_count", "volume", "highest", "average", "lowest"}, {"date", "order_count", "volume", "highest", "average", "lowest"})
in
    #"Expanded Custom"

Although seeing as I dont need updated data often, I am now trying to use / test scripts in google to pull the data once as a possible workaround

Regards
Alacta

[Blacksmoke16]

I don’t have excel to mess with this but maybe checkout https://github.com/Blacksmoke16/GESI?

Could at least see if you can get the data via that. Don’t even need to auth since the endpoint is public. If you can get it fine it must be something with your code.

[Krysenth]

By the way, when you’re posting code snippets, use backtick triplets (`` ` without the space) to “quote” it. You’d go from having-

lines
of
code
going
here
blah
blah
blah

to having -

lines
of
code
going
here
blah
blah
blah

[Alacta_Lithia]

Thanks, loaded your script and it is very easy to pull a region / item type, which then shows the table.

But I am looking to pull history for 700 plus ID’s, and it is not like I can generate one large table or just use formula on each line for each item type in another column, as there is a table then in the way for the second line.

Will keep looking / trying to work this out

[Blacksmoke16]

Actually you might be able to. Try doing something similar to https://github.com/Blacksmoke16/GESI#using-functions-with-multiple-characters

Where you can define an array of all the IDs you want. Then build a function similar to the one in that example, where the resulting table of each ID is appended to one array and returned.

However depending on what you want to do with it, might be a bit much for sheets to handle

[Alacta_Lithia]

Been struggling a little more to try and get the historic data of items for the major trade hubs, without success.

My next option is if anyone knows where there is a static history data dump for the market, even if it was from two or three months ago, only managed to find one set of files online but it was from like 2 years ago.

Thanks.