| Pages: 1 :: [one page] |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |

Bambi
|
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
The Graduates Brutally Clever Empire
|
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.
--------------
EveHQ Character App | Item Database |

Bambi
|
Posted - 2008.01.10 16:25:00 -
[3]
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!!
|

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 Domini Umbrus VENOM Alliance
|
Posted - 2008.01.10 17:47:00 -
[5]
Damn, posted with an alt Max Kolonko |

Bambi
|
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 Domini Umbrus VENOM 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 |

Max Kolonko
Caldari Domini Umbrus VENOM 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 Max Kolonko |

Bambi
|
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 Domini Umbrus VENOM Alliance
|
Posted - 2008.01.10 23:46:00 -
[10]
Edited by: Max Kolonko on 10/01/2008 23:46:38 here is the link to example xml file i made that gathers the fdata from the xml file you specified
you type required data (region, itemid and so on) amd press button
http://www.dyplom.bud-net.pl/test/example.xls
hope it will help Max Kolonko |

Fubar
Eagle Eye Inc.
|
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
Gallente
|
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.
____________ Blue Binary |

Bambi
|
Posted - 2008.01.11 08:01:00 -
[13]
Thanks Guys!
I have something solid to work from now....
|

Max Kolonko
Caldari Domini Umbrus VENOM 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
Max Kolonko |

Bambi
|
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.
|
| |
|
| Pages: 1 :: [one page] |