Hey All,

I am sure that someone smarter than me has already done this but I am currently trying to rewrite Lactose's hunter spreadsheet with the addition of a 'front end' where you can pick all of your gear etc from drop down lists and have it calculate (this is as much a learning exercise as anything else).

The basic principle is from a warrior spreadsheet I saw linked here using named ranges and vlookups that seem to work fine.

However I am wondering if I can extend the functionality to run a query from excel to allakhazam\thott to populate all of the stats of an item directly into a table? Thus to add possible gear choices all you would need to do is enter the exact name in a field and hit an update macro. Can anyone provide any pointers on how this might be done? I have googled for any existing sql query structure for thott et all with no joy.

You might check out the code in item stats for getting the item id from alla. It's in php, but the logic is sound.

edit: look in the plugins folder in the allakhazam.php file.

WoW Guru has XML syndication of items that I'm finding very handy.

Usage directions:
http://www.wowguru.com/syndicate/database.php

Example output:
http://wowguru.com/db/syndicate.php?...&q=Earthshaker

A word of warning, the xml encoding attribute is UTF-8, but the actual encoding appears to be ISO-8859-1 (Latin 1). When I ran the xml through PHP, it threw all kinds of encoding errors, so I had to strip off the xml header and replace it with a correct one.

I've tried to make a similar spreadsheet for my mage/druid character, but I was never able to get it working properly.

The problem with thottbot has mentioned already: thottbot doesn't have an XML interface, so in order to get item information from thott, you would have to interpret the HTML result from the HTTP request.

Allakhazam looked promising, because of it's XML interface... the only problem is making a generic HTTP request which can get any item from the allakhazam database.

The way excel's external webquery interface works is, that you can define a static webquery, which then returns you the data requested. But since we want to select items from a list and fill the cells accordingly, you have to generate this webquery. As far as I know, variables are not supported in the webquery wizard, so the only way to generate a webquery would be by means of VB scripting.

The last problem I bumped into was requesting a specific item. In order to do this you need the item's unique identifier. You can't retrieve a list of these ID's through Allakazham's XML interface, so the only way to search for items is by predefining the ID's in a separate worksheet. And since the whole idea was based on automating the whole item selection + stat retrieval proces, this was a major setback.

greetz,

Tylena

You could implement a webform in PHP/ASP/Perl which would parse the XML data, yes, but the problem of the item IDs isn't solved by this. You still need the IDs to request itemdata from either Allakazham or Thott, and since you don't have direct DB access or can populate a list through their XML interface, a tool like this isn't very feasible in my opinion.

The only solution is to either predefine the IDs in your software, which means updating it every time a contentpatch is released or writing a UI mod which can extrapolate this information for you. Or just doing it using a bruteforce method by (random) generating item IDs and parsing the valid/invalid content. On way or the other, it's alot of work to accomplish something like this.

greetz,

Tylena

Ah, I was asking the OP about his experience level, not you Tylena. :p

As for concerns on how to get the IDs, that's unnecessary if you use WoWGuru. You can just provide a search string.

yeah.. I figured that much after reading your post again :).. so I removed that stuff.

Btw. I know you can use searchstrings on both Thott and Guru, but the result will be plain HTML, which has to be interpreted, right? The best solution would be a XML interface with this kind of search functionality.

As for the whole issue about the possibilities of this in excel.. well.. let's say that you need some VB experience to populate your datasheet(s).

greetz,

Tylena

No, you can use wowguru to retrieve XML from a query string. :)

Example cited above in post #4: http://wowguru.com/db/syndicate.php?...&q=Earthshaker

Regardless for a project of this size where you're dealing with a relatively limited subset of items (2-5 per slot probably) it seems it'd be best to just enter them manually. Better time investment. Unless you're just doing it for the sake of doing it.

If you get stuck doing this, take a look at a stickied hunter simulator on the WoW forums - it uses pre-entered item stats (and must have taken a jurassic age to write) to trigger a vlookup function. It also allows you compare up to 8 sets of gear which is great for working out how each set of gear will react to a talent change. Only thing is that I don't think it estimates the effect of Improved Aspect of the Hawk.

It can be found in this thread http://forums.worldofwarcraft.com/th...=1#post1066563

Any other comments on the sheet would be interesting as I have used it, rather than Lactose's, for most of my observations (i.e. some rather skimpy work on the theoretical point at which 0/21/30 outperforms 5/31/15).

Alla has item name to ID reference in XML - http://wow.allakhazam.com/itemlist.xml