This release is a soft update requiring a CTRL+F5 to clear cache to recieve the changes, this update can later be acquired through a classic update prompt inside Excel. If clearing cache and refreshing isn’t enough.
This update brings important changes to the MARKET_ORDERS_STREAMING functions. The aim is to prevent ESI bans caused by the automatic updates resembling market scraping bot activity. Additionally, limits on the number of _STREAMING functions have been implemented to safeguard against potential crashes due to exceeding Javascript technical limits. This prevents from getting caught in an endless loop and subsequently getting banned.
Updates to functions parameters and logic
Limited Market _STREAMING functions to 100 variants to prevent add-in internal crashing scenarios that could lead to ESI bans.
Limits being hit will display a #CALC error in the cell. Deleting existing _STREAMING functions will allow new ones to be called.
Variant meaning each unique combination of type_id, region_id, buy_sell_boolean and location_id.
Changed Market _STREAMING functions auto-refresh timers to 1 hour to prevent scenarios that could lead to ESI bans.
Market orders and market history functions now check for a valid market_group_id on the type and if the type is published before calling ESI.
Market history function has a new optional boolean parameter “latest_only”, where it will return a simpler entity with only the latest date’s values.
Example function. =EVEONLINE.MARKET_HISTORY(10000002, 587, TRUE)
More helper parameters for market history data handling will be added later based on community feedback.
Market history “history” array of the data in the generic version is now reversed so the latest date will be on top of the array. (Community feedback)
Solar systems returned in functions such as SEARCHSOLARSYSTEM will now also include an array of the stations in that system (Community feedback)
Character and corporation ASSETS functions with with a type_id or a location_filter will now pre-filter and only resolve relevant entities improving the speed of the function.
Logic is in place to retain resolution of final location for nested items, and also for the display of items in a container if the location_filter is fx. a station_id.
Using a string to search assets that exact matches a type will also get this improved handling, fx “Rifter” but partial terms like “Navy” will not.
GROUP and CATEGORY functions now have a new “expanded” boolean optional parameter, defaults to false, if TRUE will output a list of the groups/types belonging to that category/group.
Loading for large groups could take a while and get stuck so it’s been made optional for when only the name of the group was what the user wanted.
INVENTORYSEARCH now has the parameter “display_unpublished” for display of items in the SDE file that aren’t flagged as published.
New functions
.CHARACTERSEARCH(string, strict) to use the ESI search endpoint to find characters. Requires a logged in character for endpoint access. 5 letter minimun for non-strict searching
.CHARACTER_PLANETS([character_id_or_entity]) and .CHARACTER_PLANET(character_id_or_entity, planet_id) added with the first draft of PI informational data.
This version includes pin information and extractor information in a raw way.
This will get more updates to it’s fields so know that the current version will recieve breaking changes if used in this state.
Added an internal Planet Entity for displaying planet information in the PI entities.
Bug fixes and QoL improvements
CHARACTER_SKILL_QUEUE now displays the correct the start date .
CHARACTER_CONTRACTS will now return results if there are any.
Wars description text in the sidebar now correctly states it will only fetch 50 latest wars.
Faulty character access tokens and refresh token will properly output a #VALUE error instead of #N/A.
Market history now displays the name of the region that was being called instead of the ID.
Corporation functions requiring division_id will now default to 1 if divison was omitted.
Renamed the following fields in Type Entities to match ESI field notation (Community feedback)
groupID to group_id
marketGroupID to market_group_id
raceID to race_id
portionSize to portion_size
Renamed fields for regions, constellations, solarsystems and stations to match ESI field notation.
Character and corporation ASSETS functions with a paging parameter will now return 500 per page instead of 1000 to better support excel web limited output payload.
Edit:
*Adding type_group_id and type_category_id to blueprint material array for easier logic creation when dealing with nested blueprint in industry scenarios. (Stealth addition, deploying with a minor UI improvement on 29/08/2023)
i dont know if its a bug, but patchnotes say only the market streaming was changed…
but since the release my spreadsheet is not working anymore. i try to get 50 marketorders by the normal marketorders function (not streaming function)
it worked fine last patch and now excel is crashing while loading every single time!
its working till about 10 orders… but thats quite low
Can you show me your excel workbook by messaging me on Discord? And are you on excel web or desktop?
Mostly Interested seeing your input exactly since the only thing changed in the older market_order() was the addition of code relating to checking for type_id being a type that existed, had published = TRUE and contained a valid market_group_id to prevent requests that would be a wasted esi call.
What I find frustrating is the type mismatch that occurs.
Cell A1 = Devoter
=EVEONLINE.INVENTORYSEARCH(A1,TRUE).Id returns 12017 as the result.
If I try to nest that in a different formula Like this:
=EVEONLINE.BLUEPRINT_EXPLOSION(EVEONLINE.INVENTORYSEARCH(A1,TRUE).Id)
I get a #VALUE! error
So I have to cast the result like this:
=EVEONLINE.BLUEPRINT_EXPLOSION(NUMBERVALUE(EVEONLINE.INVENTORYSEARCH(A1,TRUE).Id))
the strange part is that if I do a ISNUMBER check on the cell that has =EVEONLINE.INVENTORYSEARCH(A1,TRUE).Id in it, it returns true. So why does the nesting require a cast to work?
Am I doing something wrong, or not understanding this correctly? I would like to be able to type the name in a cell and get the BP listed out possibly by using a LAMBDA function without jumping through more flaming hoops than necessary. I do like what we have right now, wish for more, but as someone that was part of a project to make an Excel Addin for work, it takes time to flesh it out.
Question - is the mainpage from the eveonline sample excel file going to be updated? i noticed that is still referencing the v1.0. additionally the info on the microsoft site is also referencing the wrong version
The app store version display, the one I assume you are referencing as the one showing 1.1.0.0.
That version display is only updated when we update any requirements for the store, and most updates we’ll be doing, including this one, don’t require that to be done for the update to be available to most users.
When the 1.3 information is pushed to the app store it will update that field, and prompt any users still on an older version to update.
The example file is waiting on some extra things for re-creation without the duplication issue.
I’ll poke someone next week to try and get it completed a bit faster, awkward having an example file that’s basically read-only since the workbook is “poisoned” by our internal version of the add-in!
remember to upgrade the formulae to the new “format”. that is what i noticed when i re-downloaded from the main site. after reviewing the forums i saw the changes and corrected my version.
Agreed, the first test scenarios involve a parameter that lets the user define his own range by specifying number of days for the latest_only version to combine.
Something like .MARKET_HISTORY(10000002, 587, TRUE, 30) and the 30 being a parameter called “aggregate_n_days_for_latest” (name needs work), with a default as 1, i.e. just yesterday or first available date. 30 would count back 30(31 days technically) and but taking into account empty days of data.
edit: 24/09/23 with fanfest over I can say that this feature is almost ready, I’m testing it right now trying to break it with weird data, but the first rendition seemes to load alright. Hopefully a minor patch can be released this week to add this.
sheet has never worked correctly on 10 different PC’s and when it does work it loads columns a-f and 6 lines and stops kinda sucks at best. GL and hope someone puts out a good sheet.
Is anyone seeing stability issues with the plugin or even the data connection I’m really taking off on learning the usage of it and have had the Excel (Mac and usually rock solid) crash and take all of my work with it. After recovering (and even rebooting) the add in does not seem to be responding. Now. After recovering from the crash any attempt to drop in as much as a character card from the admin results in a #NAME? error.
Apparently the crash disabled the Analysis Tool pack and Solver Addins which had to be re-enabled for the Eve add-in to be able to function And that data connection still does not seem to have returned to normal as no data will load.
The way excel is binding the add-ins to the sheets can be difficult to fix if it encounters some critical error.
My best idea is probably to create a fresh sheet and copy over the cell formulas to it as that can help get around any workbook related issued that might have come up during an error.
Was your excel file by any chance based on the original example linked in the first post?
Since that workbook has some issue due to a duplication from the internal copy of the eve plugin.
(In progress to be remade without that duplication issue, since updating it with any new features and parameters is also needed)
No. In this case it was an original sheet. The crash appears to have completely changed my formula from its original instance.
From: =EVEONLINE.CHARACTER_PLANETS(C3)
To: ='https://d.docs.live.net/Applications/Microsoft Excel.app/Contents/Frameworks/ATP.framework/Resources/en.lproj/FUNCRES.XLAM'!_xldudf_EVEONLINE_CHARACTER_PLANETS(C3)
This has cascaded into other formulas as it was the top level basis for others.
For instance: =IF(ISERROR(EVEONLINE.CHARACTER_PLANET($C$3,G3).extractors.extractor_product_type_id),"",VLOOKUP(EVEONLINE.CHARACTER_PLANET($C$3,G3).extractors.extractor_product_type_id,'CONFIG - START HERE -'!$K$3:$L$85,2,))
Has become
=IF(ISERROR('https://d.docs.live.net/Applications/Microsoft Excel.app/Contents/Frameworks/ATP.framework/Resources/en.lproj/FUNCRES.XLAM'!_xldudf_EVEONLINE_CHARACTER_PLANET($C$3,G3).extractors.extractor_product_type_id),"",VLOOKUP('https://d.docs.live.net/Applications/Microsoft Excel.app/Contents/Frameworks/ATP.framework/Resources/en.lproj/FUNCRES.XLAM'!_xldudf_EVEONLINE_CHARACTER_PLANET($C$3,G3).extractors.extractor_product_type_id,'CONFIG - START HERE -'!$K$3:$L$85,2,))
Appears to have found 636 instances of the error on the sheet and repaired everything in one fell swoop!
EDIT: I can provide you with a copy of the sheet if you would like to see its use first hand. I’ve already shared it in a couple of places. It is my “at a glance” PI tracker. When it works it’s amazing.
I do like what we have right now, wish for more, but as someone that was part of a project to make an Excel Addin for work, it takes time to flesh it out.