MIzpah

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.

 

◊ Drauk

I don't think its possible with thottbot, unless you can write a parser that will analyze Thott's HTML.

Alla, on the other hand provides an XML interface

http://wow.allakhazam.com/dev/wow/item-xml.pl?witem=[item id]

Fun is for casuals

 

Humbaba

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.

 

Toxygene

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.

 

Tylena

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

 

arc

I wouldn't think getting things from Thottbot/Allakhazam/WoWGuru within Excel would be worth the effort after reading Tylena's post.

Easiest thing to do would likely write something in PHP/C#/Perl whatever that grabs a bunch of those WoWGuru XML files and spits them out in CSV format (comma delimited + quotes). You can load the CSV files into an Excel sheet really easily and just work from there.

How experienced of a programmer are you? Whatever method you use it'll go well outside the scope of just Excel, whether you end up using VBA within Excel, or something entirely seperate.

 

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

 

arc

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.

 

Tylena

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

 

MIzpah

Thanks Arc \ Tylena and others!

Indeed its fair to say that the work\reward ratio seems a little off base with this project. As it stands I have completed (nearly!) the first part of the project which involved building a 'gear configurator' using excel dropdowns and using that to generate the inputs to lactose's HICS spreadsheed. This worked (although I am still trying to get some logic on MH\OF\U\One hand items to behave) and is now being redone already for HICS 2.0 .

The area this post addressed was finding an alternate to filling in lots of seperate tables for each item choice, no solution (that I can implement) found as yet!

PS: My eyes really dont work well in the morning till I have had more coffee...

 

arc

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.

 

Farstrider

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).

John O'Groats to Lands End 2009 for Leukaemia Research

 

◊ Drauk

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

Fun is for casuals

 

Tylena

Oeh nice... jus the thing I was looking for.

tx, Drauk!