How can I get the percentile price for a list of materials?

EVE Technology and Research Center EVE x Microsoft Excel Add-in
1

Hello,

I would like to get the percentile price for the list of materials that comes out from a blueprint explosion. How do I do that?

  • I tried passing EVEONLINE.MARKET_ORDERS_STATS the list of materials, it does not work because it only accepts one item id.
  • I tried calling BYROW with a LAMBDA containing the EVEONLINE.MARKET_ORDERS_STATS call, it does not work throwing a #CALC! error.

I ran out of ideas… Any help would be appreciated.

Thanks!

2

Asking ChatGPT I am being told that the problem might come by lambdas not supporting the async calls from market_orders_stats… This complicates the spreadsheet a lot.

I feel that anyone who wants to build something a bit more advanced using the extension, specifically with percentiles will have the same struggle.

Perhaps allowing the functions to receive an array would make things much easier and improve the quality of the addin? The pricings provided by the properties limit a lot the information. I work at Jita and the values I am getting have nothing to do with the reality, so I need to get the prices from the sell orders percentiles!!!

3

_STATS and array support is actually doable, since it doesn’t ever output subarrays.

I’ll add this as a task and look at implementing it when I have the chance. Thanks!

4

One slightly janky way to do this would be to use:

=sequence(rows(EVEONLINE.BLUEPRINT_EXPLOSION().materials)

to get an array of numbers to serve as an index you can reference with =index() functions to pull the data out of the ESI arrays. This will create a table that will scale with the number of materials in any given item. Wrap your functions with if(isnumber(index_column_value), function, “”) to leave everything else blank and use conditional formatting to keep it neat.

Gives you something like this that changes with whatever ship name you put in cell A1.

image
image690×340 36.4 KB

image
image687×675 64.5 KB

material names:

=IF(ISNUMBER($C2),INDEX(EVEONLINE.BLUEPRINT_EXPLOSION($B$13).materials.type,$C2).name,“”)

type ids:

=IF(ISNUMBER($C2),INDEX(EVEONLINE.BLUEPRINT_EXPLOSION($B$13).materials.type,$C2).id,“”)

material quantity:

=IF(ISNUMBER($C2),INDEX(EVEONLINE.BLUEPRINT_EXPLOSION($B$13).materials.quantity,$C2),“”)

buy value:

=IF(ISNUMBER($C2), EVEONLINE.MARKET_ORDERS_STATS(FORGE, $E2).buy.percentile * $F2, “”)

total material cost:

=SUM(G2:G100)
(using an arbitrary length for the sum function works fine)

if you wanted to be more precise, you could do something like:

=SUM(INDIRECT(CONCAT(“$G$2:$G$”,B14+1)))