Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Yelu Olympias
|
Posted - 2009.10.04 14:39:00 -
[1]
I'm trying to make my own spreadsheet in Open Office Calc and would greatly benefit from being able to pull the mineral prices and about 20-30 module prices from eve-central. I see the http:// link at the eve-central site but understand I need to 'map'? it on the spreadsheet.
I can't seem to find any instructions after a 10 minute Google, can anyone point me to a tutorial or explanation of what I need to do?
|
Haguu
Caldari School of Applied Knowledge
|
Posted - 2009.10.05 06:18:00 -
[2]
I was trying to do something similar with Google Spreadsheets.
After going to w3schools and learning XPath for the 10th time I got this to work in Google Spreadsheets using the URI from the eve-central example.
=importXML("http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002","//type/all/volume")
|
Yelu Olympias
|
Posted - 2009.10.05 14:58:00 -
[3]
Thanks for the reply, I had not seen the w3schools site before and it looks interesting.
Unfortunately the example you provided didn't work for me in Open Office. Can anybody out there even tell me if this is possible in Open Office?
|
Muscaat
ASCENTIS CORPORATION Free Worlds Alliance
|
Posted - 2009.10.05 17:29:00 -
[4]
Hmm, looking at the site you link there it seems quite an interesting task...
Bear in mind that almost all of the "XSLT Import" code listed seems to be boiler-plate. The relevant bit starts at the "insert column labels" comment - you should be able to tweak from there and below to suit your purposes.
If you're still stuck, send me an EVE-mail and I can look into it for you ---------- EVE Markets - global price trends at your fingertips |
Haguu
Caldari School of Applied Knowledge
|
Posted - 2009.10.05 23:18:00 -
[5]
Your example of converting to an OO format seems like a bit of work. A less elegant but easier approach would be to use XSLT to make a CSV list and then import that into OO.
|
Fubar
Eagle Eye Inc.
|
Posted - 2009.10.06 00:29:00 -
[6]
You can retrieve the eve-central xml file into a new calc document using the steps in that web page you found before.
marketstat_import.xsl has been crudely modified to work with eve-central.
Once you add it in as an XML Filter named MarketStat you could use openoffice.org basic to simplify retrieval then copy and paste the retrieved data into your sheet.
Openoffice.org Basic code follows
REM ***** BASIC *****
Sub Main
rem ------------------------------------------------------- rem - Init import URL dim xmlurl as string xmlurl = "http://api.eve-central.com/api/marketstat?typeid=34&typeid=35" xmlurl = xmlurl & "&typeid=36&typeid=37&typeid=38&typeid=39®ionlimit=10000002"
rem ------------------------------------------------------- rem - Import payments dim properties(0) as new com.sun.star.beans.PropertyValue properties(0).Name = "FilterName" properties(0).Value = "MarketStat"
dim doc As Object doc = StarDesktop.loadComponentFromURL(xmlurl, "_blank", 0, properties())
End Sub
|
Yelu Olympias
|
Posted - 2009.10.06 15:26:00 -
[7]
Linking that erm.. link may have made me appear smarter than I actually am, this is a problem I often experience.
Unfortunately I can't make any sense of the link, nor what any of you who have been kind enough to reply have posted. I'll keep making attempts at understanding but any more detailed steps or advice would be great, failing that I'll take up the offer of personal help if it still stands, Muscaat.
|
Fubar
Eagle Eye Inc.
|
Posted - 2009.10.06 16:07:00 -
[8]
Download and save the file marketstat_import.xsl linked above.
Install the XSLT filter for Import 1) Open OpenOffice.org Calc 2) Select "Tools" -> "XML Filter Settings..." 3) Click the "New" button 4) Fill in the following...
4a) "General" tab 4aa) Filter name = MarketStat 4ab) Application = OpenOffice.org Calc (.ods) 4ac) Name of file type = Eve Central MarketStat
4b) "Transformation" tab 4ba) XSLT for import = Browse and select the file marketstat_import.xsl you downloaded previously. 5) Click Ok and Close
Change Macro Security 1) Select "Tools" -> "Options..." 2) Select "OpenOffice.org" -> "Security" 3) Click the "Macro Security" button 4) Select "Medium" on the "Security Level" Tab 5) Click Ok and Ok
Add OpenOffice.org Basic code 1) Select "Tools" -> "Macros" -> "Organize Macros" -> "OpenOffice.org Basic..." 2) Select "My Macros" -> "Standard" -> "Module 1" and click the "Edit" button 3) Paste the code below after any existing text
Now you can run that Macro from any open OpenOffice.org app. 1) Select "Tools" -> "Macro" -> "Run Macro..." 2) Select "My Macros" -> "Standard" -> "Module1" -> "GetEveCentralPrices" and click the "Run" button
More typeids can be added to the url. Be aware that Eve-Central may limit the call to 20 typeids.
Hope this is more helpful.
Code--->Sub GetEveCentralPrices
rem ------------------------------------------------------- rem - Init import URL dim xmlurl as string xmlurl = "http://api.eve-central.com/api/marketstat?typeid=34&typeid=35" xmlurl = xmlurl & "&typeid=36&typeid=37&typeid=38&typeid=39®ionlimit=10000002"
rem ------------------------------------------------------- rem - Import payments dim properties(0) as new com.sun.star.beans.PropertyValue properties(0).Name = "FilterName" properties(0).Value = "MarketStat"
dim doc As Object doc = StarDesktop.loadComponentFromURL(xmlurl, "_blank", 0, properties())
End Sub <--- Code
|
Yelu Olympias
|
Posted - 2009.10.06 17:19:00 -
[9]
Edited by: Yelu Olympias on 06/10/2009 17:21:37 Fubar it worked! You're a genius! Personally I think this should be stickied and all spreadsheets moved away from Excel.
Thank you to everyone who replied and to Fubar for the answer, now to start using my spreadsheet of awesomeness to get rich... rich beyond my wildest dreams!!!!
Edit: needed some smileys
|
Kaladr
Amarr
|
Posted - 2009.10.07 05:32:00 -
[10]
For future reference for users, I've linked to this thread here:
dev.eve-central.com/tipsandtricks
Thanks for all of the contributions :) ---- EVE-Central.com - Your EVE economic and corporate site, featuring the inter-region market browser |
|
Sidrat Flush
Caldari Life is Experience New Eden Hardware Emporium
|
Posted - 2009.10.08 11:29:00 -
[11]
It would be nice if more people used excel actually as calc is a) a stupid name and b) ugly as sin.
But apart from that well done, do you think people would actually run your macros if you put the sheet up for free download?
Eve-online Industrial Organiser thread t1, t2 and t3 batch manufacturing |
Dethmourne Silvermane
Gallente Silvermane Enterprises
|
Posted - 2009.11.13 03:09:00 -
[12]
This really hits the info I was looking for, thanks!
|
Dethmourne Silvermane
Gallente Silvermane Enterprises
|
Posted - 2009.11.14 04:52:00 -
[13]
Now I just need to figure out how to use the sheet your macro produces in a manner that copies the data over to cells on my existing sheet... any suggestions?
|
Fubar
Eagle Eye Inc.
|
Posted - 2009.11.14 17:45:00 -
[14]
Originally by: Dethmourne Silvermane Now I just need to figure out how to use the sheet your macro produces in a manner that copies the data over to cells on my existing sheet... any suggestions?
Simplest method: copy and paste.
|
Tonto Auri
Vhero' Multipurpose Corp
|
Posted - 2009.11.16 01:33:00 -
[15]
More complicated - would be to change macros to walk through prepared sheet and insert appropriate data in appropriate cells. -- Thanks CCP for cu |
Synvaat
|
Posted - 2010.01.20 02:08:00 -
[16]
Originally by: Fubar ...set XSLT Filter... ...Code example... doc = StarDesktop.loadComponentFromURL(xmlurl, "_blank", 0, properties())
I've got the filter, and it filters the xml from the call into a nice sheet for me if i manually load in the xml.
However, once I get into the macro making, I don't know how to do anything with the doc object.
The XSLT Filter is telling openoffice that the doc object is a spreadsheet? If so, I'm lost on how to use it, since the usual spreadsheet methods don't seem to work with it.
Could someone post a usage example of using this object to dish up the data?
|
Testiteppo
|
Posted - 2010.03.23 13:15:00 -
[17]
Originally by: Haguu I was trying to do something similar with Google Spreadsheets. After going to w3schools and learning XPath for the 10th time I got this to work in Google Spreadsheets using the URI from the eve-central example. =importXML("http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002","//type/all/volume")
I'm trying to get this working with excel2003, but so far with poor results. What I'm trying to accomplish is simply look up the url eg. cell A1 and get median sell price to cell B1 with formula =importXML(A1,"//type/sell/median") , which doesn't work. I can't get it working even with url manually inserted in the formula =importXML("http://api.eve-central.com/api/marketstat?typeid=34®ionlimit=10000002","//type/sell/median")
As most can see, I'm not very familiar with these things, so all in all, any help would be very much appreciated. (all the pro excel sheets that I found and tried to examine are locked so I can't see the formulas that they are using :( ) |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |