Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 1 post(s) |
Meescha Zimmerman
Power of the Phoenix
0
|
Posted - 2013.09.14 22:35:00 -
[1] - Quote
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 |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
1908
|
Posted - 2013.09.14 22:55:00 -
[2] - Quote
You can set, in Excel against the connections you're using to load xml, to refresh on open. There's also a refresh all button on the connections tab.
Other than that: https://www.fuzzwork.co.uk/2013/06/22/importing-price-data-into-spreadsheets/ Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Thur Barbek
Republic University Minmatar Republic
167
|
Posted - 2013.09.14 22:57:00 -
[3] - Quote
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
|
Meescha Zimmerman
Power of the Phoenix
0
|
Posted - 2013.09.15 09:56:00 -
[4] - Quote
thanks, i guess ill make a quick google docs, and set it up to where i can just copy and paste the output, again thanks |
Jdestars
Stars Research systems Incorporation
3
|
Posted - 2013.09.17 11:58:00 -
[5] - Quote
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 GǪ. 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 GǪ good new with an tiny script in vbs or python i can export in mass the data ^^ |
Immortis Vexx
Sons Of Alexander AL3XAND3R.
62
|
Posted - 2013.09.17 22:11:00 -
[6] - Quote
This is actually fairly simple. The Eve-Central APIs allow for some pretty simple xml data imports. I have this set up at home and will post a link to the spreadsheet i have if anyone is interested. It pulls the data for a given item from the main market hubs then determines the highest buy order and the lowest sell order for the given item at each hub. I have a simple vlookup linked to another sheet with every item in game so that it pulls the appropriate itemid.
Vexx |
Swidgen
Republic University Minmatar Republic
104
|
Posted - 2013.09.18 07:23:00 -
[7] - Quote
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. 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. |
Hel O'Ween
Men On A Mission
23
|
Posted - 2013.09.18 15:50:00 -
[8] - Quote
Swidgen wrote: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
As MS Excel also 'speaks' MS Access, you could use my EWA's MS Access database, which includes all EVE items (table invTypes) from CCP's static database dump instead of a CSV file. |
Soldarius
Deadman W0nderland Forsak3n.
341
|
Posted - 2013.09.18 16:12:00 -
[9] - Quote
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?
Free Ripley Weaver! |
Minerva Trask
Republic University Minmatar Republic
0
|
Posted - 2013.09.19 14:08:00 -
[10] - Quote
This can be done in Excel 2013 using the WEBSERVICE and FILTERXML functions.
Thus instead of googles =importxml(url,xpath) you can use =FILTERXML(WEBSERVICE(url),"xpath") in Excel
Seems to work my end although I did get some pesky security warning that fubared my sheet till I turned it off
Of course you can probably refine the equation by placing the url in a cell, but the above should get you started. |
|
Ditrius Bedala
BecauseICaNDoIt
1
|
Posted - 2013.09.19 14:12:00 -
[11] - Quote
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.
Quote:Function ImportData(sUrl As String) As String
Dim xmlDoc As Object Dim xmlNode As Object
Set xmlDoc = CreateObject("MSXML2.DOMDocument") xmlDoc.async = False xmlDoc.Load (sUrl)
Set xmlNode = xmlDoc.SelectSingleNode("/evec_api/marketstat/type/sell/min")
ImportData = xmlNode.Text End Function
Public Function GetJitaPrice(typeId As Integer) As Double Dim a As String
a = ImportData("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=" & typeId) GetJitaPrice = Val(a)
End Function
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:
Quote:
- On the Tools menu, click Options, and then click the Calculation tab.
- Click Manual To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by clicking Calc Now (F9) on the Calculation tab)
But then you will have to manually recalculate worksheet every-time you change something |
Meescha Zimmerman
Zimmerman GmbH
0
|
Posted - 2013.09.20 17:28:00 -
[12] - Quote
it seems really complicated, so i took my excel spreadsheet and just imported it to googledocs added the code and now its auto updating, still use excel to edit stuff, but use googledocs to do the rest, just seems simpler. heh thanks for all the replies |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |