Microsoft Excel Question

Hello,

I have done a excel warehouse for my industry and i have manage to get the price for my items with Swagger Interface, more exactly with this command:

let
Source = Json.Document(Web.Contents(“https://esi.evetech.net/latest/markets/prices/?datasource=tranquility&order_type=sell&page=1”)),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column1” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“adjusted_price”, “average_price”, “type_id”}, {“Column1.adjusted_price”, “Column1.average_price”, “Column1.type_id”}),
#“Sorted Rows” = Table.Sort(#“Expanded Column1”,{{“Column1.average_price”, Order.Descending}}),
#“Reordered Columns” = Table.ReorderColumns(#“Sorted Rows”,{“Column1.type_id”, “Column1.average_price”, “Column1.adjusted_price”})
in
#“Reordered Columns”

Now my problem is that i want to show me the prices in Amarr and I didn’t succeed…
Can someone help me please?

A couple of problems there that are related:
Firstly, the end point you are using is getting the average and adjusted prices that are used to build the installation and other costs as part of manufacturing and invention. These are not the prices of orders on the market. This will not be terribly useful information to you.

Have a look at the /markets/{region_id}/orders/ end point where you replace the {region id} with the value for Domain. /universe/ids/ is useful there. Domain=10000043 (Amarr is in Domain). This returns all the orders visible as if you were in Domain. You will need to sort out what items you are interested in, but I leave that as an exercise for you - I’m not that familiar with Excel.
I’ve been tinkering with the Eve API as part of keeping my hand in with Java rather than just pulling stuff into a spreadsheet.

Possibly not terribly helpful, but hopefully a pointer in a better direction.

1 Like

Ugh, this is easy to do and difficult to explain. But, I guess I’ll try. Anyway, here are instructions for excel.

Screenshot

  1. To make life easier, you’ll want to import a typeid list, which will give you two columns -one with the item type, and the other with the typeid. (I’ll let you search for an up to date typeid list).
Screenshot

  1. In the cell to the right of that, paste the following formula. What this will do is to generate the web address that you’ll need by pulling the typeid from the appropriate cell and inserting it into the web address. Note that when you paste this text into a cell, it will automatically try to pull data from cell B19. So, you will have to manually change that to the desired cell. However, once you have that set up, you can copy and paste the cell (which is not the same as copy and pasting the text within the cell), and excell will automatically update the formula. For example, if I copy and pasted cell C19 into cell C20, it would automatically change the formula to pull data from cell B20. Note that the formual includes the equal sign and quotation marks.

    =“http://api.evemarketer.com/ec/marketstat?typeid="&B19&"&regionlimit=10000043

Screenshot

  1. Next, copy column C, and paste it into column D by using the Paste Values command. This will paste the actually web address, instead of the formula that was used to generate the web address. You should end up with a bunch of formulas that look like the one below (which pulls market data for Tritanium in Domain.
    http://api.evemarketer.com/ec/marketstat?typeid=34&regionlimit=10000043
Screenshot

  1. Now comes the tedious part. You’ll need to manually set up a web query for each item that you want data for, using the addresses in column D. It will eventually ask you where you want to import the data, but you can make your life a little easier by select the first cell in the desired row first.
Screenshot

  1. It will then fill all the data into cells on that row. Which will look like this.


This is the what will get put into each column (columns H through U). Oh, and you can update your data at any time by pressing the “refresh all” button on the data tab. Just note that it can take like 10-15 minutes to finish if you have a bunch of them.

Hopefully, that made sense.

1 Like

Thank you all for the response.

I’m not at home right now… i will try all this later.