open All Channels
seplocked EVE Technology Lab
blankseplocked Calling all Excel Gurus
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Bambi
Existentialist Collective
Posted - 2008.01.10 08:48:00 - [1]
 

Hi all,
I am in need of some help from an Excel guru.
I have managed to tweak Fubars Ultimate Eve Sheet to retrieve advanced material prices from eve-central.com using their wonderful xml feed service.
Using a url such as http://eve-central.com/home/marketstat_xml.html?regionlimit=10000022&hours=24&minQ=1000&typeid=17317 in excels xml map feature.
Now my question for the guruís is this, is it possible to use a variable in this url lookup? What I mean is can I replace the regionlimit=10000022 with a value from a cell in Excel?
If I have a dropdown list of region names that does a vlookup of the region_ID, is there a way to put this value into the xml map url changing the region it retrieves prices for?

Vessper
Indicium Technologies
Hephaestus Forge Alliance
Posted - 2008.01.10 12:33:00 - [2]
 

I've used something similar before with the CONCATENATE function, which will join the fixed part of the URL to something dynamic (such as a cell value). E.g.

=CONCATENATE("http://eve-central.com/home/marketstat_xml.html?regionlimit=",A1,"&hours=24&minQ=1000&typeid=17317")

Obviously replace the value of A1 as appropriate but you should also be able to do lookup values in place of just cell references.

Bambi
Existentialist Collective
Posted - 2008.01.10 16:25:00 - [3]
 

Edited by: Bambi on 10/01/2008 17:27:43
I'll have a go with that when I get home, not sure if it will work as the url is being used as an xml source in place of a local xml file.

Its scary how technical and geeky a spreadsheet can get!!

Tried it, wont have it... Crying or Very sad

I can make a region dependent url this way but I cant get excel to use this as an xml map source.

drone test
Posted - 2008.01.10 17:46:00 - [4]
 

Edited by: drone test on 10/01/2008 17:49:22
it work like this:
i assume you are using excell get web query tool to get data

(data->import->get web query)

it is possible to macro it
just do the following:
prepare what Vessper said - so you will have your xml adress with regionId ready
than run macro recording
do the (data->import->get web query) to get your xml file (just get it for whatever region)
after you finish stop recording macro

than go into VB editor (alt+F11) and change the xml adress to cells("A1").value - where A1 is cell for your concanated adress.

quit edit and go to view -> toolbars -> forms

from new toolbar chose button and place it near your dropdown list.

right click it and choose macro or whatever its called (dont have excell on this notebook - i will check later in home) and chose the newly created macro for the button.

now after you choose the region form list you just click on button and it should update info - but as i said before - will have to check in home - may need to use macro to delete previous data or something

anyway - test with it



If it will work - feel free to send some iskies to Max Kolonko :)

Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2008.01.10 17:47:00 - [5]
 

Damn, posted with an alt

Bambi
Existentialist Collective
Posted - 2008.01.10 19:27:00 - [6]
 

I was using the Data->XML->XML Source the XML Map.
This returns XML with 11 fields and 11 lots of data to chose from, I then map the avg_sell_price and total_sell_qty values to my spreadsheet.
I have a macro recorded and assigned to a button that updates all the advanced material prices and quantities for the current region.


I dont mind having 4 or 5 hard coded regions, as I have now, its more for Fubars Ultimate Eve Sheet and public release, it would be better to have the ability to use ANY region

I have never done anything like this before, so its all a bit hit and miss ;-)

Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2008.01.10 19:42:00 - [7]
 

will have to look into that tool - what excell version u r using? hope not viste becouse i dont have it (2003 here - at home at least - will be there in an hour or so)

can you post the xls file? i can look into it tomorrow.

Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2008.01.10 21:49:00 - [8]
 

back at home - it looks like my excell - dont have data->xml option - waiting for you to send the file

Bambi
Existentialist Collective
Posted - 2008.01.10 23:03:00 - [9]
 

an example of the xml file it requests is a standard one from eve-central.com

http://eve-central.com/home/marketstat_xml.html?regionlimit=10000002&hours=24&minQ=1000&typeid=17317

The part I want to make variable is the regionlimit=xxxxxxxxx

from that I map two fields, <avg_sell_price> and <total_sell_volume>

Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2008.01.10 23:46:00 - [10]
 

Edited by: Max Kolonko on 11/01/2008 00:24:16
here is the link to example xml file i made that gathers the data from the xml file you specified

you type required data (region, itemid and so on)
and press button

http://www.dyplom.bud-net.pl/test/example.xls

hope it will help - though you will have to experiment with it
still not all data taken from xml - dont know why

just checked with MS page - my excell version dont have the XML option - i have 2002 and it started with 2003 - need to download it :(

Fubar
Posted - 2008.01.11 02:19:00 - [11]
 

Here is an Excel example of how I would do it using VBA so versions of Excel under 2003 (like my version XP or 2002) can play with XML files.

Blue Binary
Polychoron
Posted - 2008.01.11 07:13:00 - [12]
 

Originally by: Bambi
an example of the xml file it requests is a standard one from eve-central.com

http://eve-central.com/home/marketstat_xml.html?regionlimit=10000002&hours=24&minQ=1000&typeid=17317

The part I want to make variable is the regionlimit=xxxxxxxxx

from that I map two fields, <avg_sell_price> and <total_sell_volume>

You could try using a dynamic Web Query file. You could link a parameter to a cell, or a prompt, to make the URL dynamic.

A possible solution is to create tables from the XML maps for all the different regions and then perform functions/filters on the tables to get the data you need. I'm using Excel 2007 and there is an option to automatically perform refreshes on the tables at a time you specify. There is also an option for appending or overwriting data when refreshing (very useful for compiling a history of a commodity if you append it).

Or just use a macro as others have suggested.

Bambi
Existentialist Collective
Posted - 2008.01.11 08:01:00 - [13]
 

Edited by: Bambi on 11/01/2008 15:20:18
Thanks Guys!

I have something solid to work from now....

**Update**
Been playing with the web querey import thingy, and for some reason it doesnt seem to import all the data, and it appears to duplicate the fields it does return.

At least I can get the avg_sell_price, shame the total sell volume is missing for some reason.


Max Kolonko
Caldari
Worm Nation
Ash Alliance
Posted - 2008.01.11 19:29:00 - [14]
 

Edited by: Max Kolonko on 11/01/2008 19:29:19
yep - thats what was wrong with my example file - i already managed to instal 2003 - and managed to get the thing working woith data->XML thing
will post the file in half an hour or so

Bambi
Existentialist Collective
Posted - 2008.01.11 20:01:00 - [15]
 

Thanks guys.
I have it working wonderfully now. I used the example code that fubar posted, seeing as it his sheet I'm messing about with.


 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only