Market Discussions

Hi, I am working on an excel spreadsheet, and im trying to figure out how to make auto updating prices, i know googledocs uses like =importxml or something but what would the command be for excel? or if anyone already has the commands written could you share them heh.

all i need is updated prices for, jita, rens, amarr, and dodixie, and for the basic minerals, Tritanium, pyerite, mexallon, isogen, zydrine, megacyte, nocxium and morphite.

thanks in advanced

Importing prices with excel is a giant pain in the ass. Mainly because excel is not built to interact with the internet.

Its not a simple command you can put in a cell. This is a brief explanation of how to get started:
http://office.microsoft.com/en-us/excel-help/get-and-analyze-data-from-the-web-in-excel-HA001054848.aspx

To do this automatically, you will need a script that runs the above web queries and updates the spreadsheet.
I would bet that you would have to write a script in w/e programming language and use a macro in excel to run it when you hit a refresh button.
Basic tutorial on scripting MS excel:
http://technet.microsoft.com/en-us/library/ee176994.aspx

It appears Steve has a nice guide for excel already.


Or you could just use google docs. Avoid dealing with excel and just use the following command (for eve central):

=ImportXML("http://api.eve-marketdata.com/api/importxml_prices2.xml?char_name=demo&buysell=s&usesystem=30000142&type_ids="&JOIN(",",$F3:$F12), "/emd/price")

Where buysell = s or b. usesystem is the system ID. typeids= X. Where in this case X is a range of cells from F3 to F12.

System and item ID's can be found on the eve central website. Also, below the cell you put the above command you will want to put this: (If the command is in G3)

=CONTINUE(G3, 2, 1)
=CONTINUE(G3, 3, 1)
ect.

Example with the above script working:
https://docs.google.com/spreadsheet/ccc?key=0AphxdxaKHTAzdFhjckpWZTFRRkxZNThNLXFjRHp0RHc&usp=sharing

you can use link sheet with an external files in CVS format for example , but yes its a big sorrow for update pricer like that

with primary material like raw planetary / mineral /ice …. Too much files overs 500 ( sorrow with numerical format / export format Csv and local option of computer .


if you have time and skill try tu dev a tiny database with an tiny import command


nota : Eve can't be auto rise the mass export quotation , so export all primary material spend a lot of time for single clic each material but is the only way without use any exploit tools


Edit : i missed new change about xml … good new with an tiny script in vbs or python i can export in mass the data , but data not provide directly by CCP so carefully refresh data and legality of t the theird parry obtain them, and their method for calculate a price

Doing it in Excel is not as easy as using Googledocs, but having said that, once you get it working in Excel it's easy to maintain and update. You will need, as Jdestars says, a CVS file containing all EVE items and their IDs. Not hard to find but it will take a little bit of effort on your part. Then you will need to write a macro in Excel's flavor of VB (visual basic for applications). I have separate macros for each trade hub I want to pull prices from eve-marketdata with. You could probably combine them into 1 macro, but the returned xml is a bear when you have to sort it yourself via region/station/system/however you specify location. Finally, the macro(s) you write rely on using XML which you also need to write and embed inside the spreadsheet. This XML describes the format of the result data you expect to receive from the server. It's not overly difficult but it will take you some time to experiment and get it right. My biggest challenge was coming up with the XSD files that must accompany the XML you're expecting from the server. I had to find a non-Microsoft utility to do that for me, and even then I think I had to tweak the resulting file before it would work.

If you know anything at all about Excel and VBA, I'd give it a week of effort to get it all playing nicely together.

In the end, it really is easy to maintain and expand upon once it's working, and for all your effort you're not constrained by Googeldoc's limit of 50 items (I think) per query. It's very powerful but somewhat complex.

Getting XML data into OpenOffice Calc is beyond difficult. Unless you are well versed in XML transforms and programming macros, I would not recommend it.

I did not use google docs because it cannot handle the amount of data involved, and I don't care to pay for MS Office.

It was my understanding that excel has an importXML function. Is this not correct?

I saw this topic and could not just pass by.

Here are simple VB scripts I made to make Excel act as GoogleDocs. Excactly what OP was searching for.


And then you can just write =GetJitaPrice(1230) in a cell to get a price from eve-central.

The side effect is that the whole spreadsheet recalculation becomes very laggy. Every-time you change a value or a formula in a cell the data from eve-central.com will be retrieved and that will slow you down. You can overcome this by using this guide:

But then you will have to manually recalculate worksheet every-time you change something