These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Tell me about importing XML market data from the web into Excel
Author
Previous Topic Next Topic
Lipoic Acid
Vulcan Creations
#1 - 2011-12-26 12:36:07 UTC  |  Edited by: Lipoic Acid
I have not been able to find good information on how to do this. It should be fairly easy, right? Yet I haven't been able to figure out how to do it. Please explain to me. I'm using Excel 2010.

My goal is to import web-based XML files, and read some values from it. Let's take this file here for example: http://www.evemarketeer.com/api/info/4247/xml/10000002

I'm interested in extracting the value from this element < sell_avg >14949.606278638< / sell_avg > (extra spaces added, otherwise it won't post). And then also the < buy_avg > value.

Any help is appreciated.


I'm not too familiar with the VB language but would it be possible to write a function that loops through all the specified XML files and extracts the relevant values for each, saving them as variable that Excel cells can be linked to?
Chibisuke
Children of Avalon
Avateas Blessed
#2 - 2011-12-26 14:36:11 UTC  |  Edited by: Chibisuke
Where is the point in using the XML interface when you want to use excel and there is a CSV interface?

http://www.evemarketeer.com/api/info/4247/csv/10000002

Save as 10000002.csv
open with excel
be happy

For everything else the macro recorder of excel can help you to get something you can work with.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2011-12-26 15:17:40 UTC
What you could do is:

open a workbook
go to the data tab
click 'from web'
put in the url for the page (into the address bar. not the search page)
hit go.
hit import.
hit ok
leave it as it and hit ok


You'll now have a row of data, representing what's pulled out of the xml. and it can be refreshed at will. you can manage the connection for the data, for updating the figures in the background automatically, too.

renaming the connection on the connection screen may be worthwhile too


And you should be able to do this with multiple entries.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Lipoic Acid
Vulcan Creations
#4 - 2011-12-26 15:54:15 UTC
Steve Ronuken wrote:
What you could do is:

open a workbook
go to the data tab
click 'from web'
put in the url for the page (into the address bar. not the search page)
hit go.
hit import.
hit ok
leave it as it and hit ok


You'll now have a row of data, representing what's pulled out of the xml. and it can be refreshed at will. you can manage the connection for the data, for updating the figures in the background automatically, too.


Thank you very much. Using the text import on the CSV link does just what I wanted. It lets me import only the cells I care about.
Lipoic Acid
Vulcan Creations
#5 - 2011-12-26 18:05:43 UTC
Another question: where can I look up the region, system and item IDs that are used in these market data files?
Chibisuke
Children of Avalon
Avateas Blessed
#6 - 2011-12-26 19:55:47 UTC  |  Edited by: Chibisuke
Its in the SDE

http://www.eveonline.com/community/toolkit.asp
(Crucible_1.0_58224_db.zip )

You need to install SQL Server 2008 (express), restore that backup and then you can access the data either from the SQL Server management utility or you can perform a data import of the tables to excel ("From SQL Server" as datasource).

for typeID use table invTypes
for solarSystemIDs use table mapSlarSystems
for regiondIDs use table mapRegions

For more information on the SDE see
http://www.youtube.com/watch?v=wZCiuYBaInU
Annie Zhara
Viziam
Amarr Empire
#7 - 2012-02-16 00:32:06 UTC
Steve Ronuken wrote:
What you could do is:

open a workbook
go to the data tab
click 'from web'
put in the url for the page (into the address bar. not the search page)
hit go.
hit import.
hit ok
leave it as it and hit ok


You'll now have a row of data, representing what's pulled out of the xml. and it can be refreshed at will. you can manage the connection for the data, for updating the figures in the background automatically, too.

renaming the connection on the connection screen may be worthwhile too


And you should be able to do this with multiple entries.

This works almost as it should but I have noticed it wont import past the buy/median table.... not sure why.

Also, is there any way to limit the import to JUST one data cell? Or to parse the downloaded data in excel more cleanly?