Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.17 04:55:27 -
[1] - Quote
http://api.eve-central.com/api/marketstat?typeid=18&typeid=19&typeid=20&typeid=22&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40&typeid=41&typeid=42&typeid=43&typeid=44&typeid=&typeid=45&typeid=178&typeid=179&typeid=180&typeid=181&typeid=182&typeid=183&typeid=184&typeid=185&typeid=186&typeid=187&typeid=188&typeid=189&typeid=190®ionlimit=10000002
Ok, this is insane! Do I really have to type in every dam typeid number like this for excel in order to get an updated price list??
Is there really no function to pull the item # out of column a and look up and import the price into column c?
|
Tipa Riot
Federal Navy Academy Gallente Federation
3465
|
Posted - 2017.02.17 11:31:51 -
[2] - Quote
Who needs Excel? Who needs Spreadsheets?
I'm my own NPC alt.
|
CowQueen MMXII
441
|
Posted - 2017.02.17 12:21:25 -
[3] - Quote
Not sure if I get your problem right, but this should give you URLs for the individual item types quite fast:
Put the URL without the type ID in one cell, put the type IDs into other cells. Use CONCATENATE to put those together, format the result as URLs.
Moo! Uddersucker, moo!
|
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.17 14:45:12 -
[4] - Quote
CowQueen MMXII wrote:Not sure if I get your problem right, but this should give you URLs for the individual item types quite fast:
Put the URL without the type ID in one cell, put the type IDs into other cells. Use CONCATENATE to put those together, format the result as URLs.
Can you use CONCATENATE without using xml? The functions I have seen so far are all =importxml functions |
Oriella Trikassi
Trikassi Enterprises
4
|
Posted - 2017.02.18 18:15:16 -
[5] - Quote
Quote:Is there really no function to pull the item # out of column a and look up and import the price into column c?
Like this? Price of something at Jita.
=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")
where cell A7 has the typeID, or more likely a VLOOKUP to a Name >> ID table. |
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.19 00:54:25 -
[6] - Quote
Oriella Trikassi wrote:Quote:Is there really no function to pull the item # out of column a and look up and import the price into column c?
Like this? Price of something at Jita. =FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min") where cell A7 has the typeID or more likely a VLOOKUP to a Name >> ID table. FILTERXML is the Excel single cell function for downloading one thing from an XML table. WEBSERVICE gives the URL and passes on parameters, in this case the system and the itemID. The expression at the end defines which bit of the XML table we want, in this case the minimum sell price. Other possibilities should be obvious, "/type/buy/max" is the top buy price for example. VLOOKUP is the simple but limited Excel way of pulling data from a table. The more complex but more powerful one is INDEX MATCH which uses Boolian logic and arrays, best of luck with that! I can write the things but can't recall how I did it afterwards...
Tried inputing that into excel 2010. It doesn't like something about it, giving a error "The formula contains unrecognized text".
Do I need a newer version of excel or is something missing or spelled wrong? |
Oriella Trikassi
Trikassi Enterprises
4
|
Posted - 2017.02.19 14:20:09 -
[7] - Quote
Jaseac wrote:Tried inputing that into excel 2010. It doesn't like something about it, giving a error "The formula contains unrecognized text".
Do I need a newer version of excel or is something missing or spelled wrong? You are correct - you need at least Excel 2013 for FILTERXML which is what I have - or switch to Google Docs which has a similar function IMPORTXML.
|
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.20 01:29:33 -
[8] - Quote
=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")
Ok, so I got it working, but for some reason eve central doesnt show prices for over half the items. Even tried looking them up manually. They are priced on eve market, but not on eve central
|
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.20 02:18:01 -
[9] - Quote
my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless |
YeuxVerts Belle
Catastrophic Operations The Bastion
52
|
Posted - 2017.02.20 09:32:48 -
[10] - Quote
Jaseac wrote:my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless
The biggest issue is that your spreadsheet (for some stupid reason) isn't capable of filtering incorrect pricing. So it's worthless.
On a more serious note though, Excel is nice and all for doing what it does, but using it to fetch and analyse the price of 40k manufactured items with multiple webservice queries is a bit beyond its capabilities. You might want to look for a better tool for the job, or reduce your expectations.
The above message presents my opinions on the topic at hand. If there is a conflict between my views and reality, consider reality to be correct until proven otherwise.
|
|
Jaseac
KarmaFleet Goonswarm Federation
9
|
Posted - 2017.02.20 14:37:08 -
[11] - Quote
YeuxVerts Belle wrote:Jaseac wrote:my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless The biggest issue is that your spreadsheet (for some stupid reason) isn't capable of filtering incorrect pricing. So it's worthless. On a more serious note though, Excel is nice and all for doing what it does, but using it to fetch and analyse the price of 40k manufactured items with multiple webservice queries is a bit beyond its capabilities. You might want to look for a better tool for the job, or reduce your expectations.
I thought about creating my own program/app but I would guess that is alot harder than even Excel. Maybe one day. |
YeuxVerts Belle
Catastrophic Operations The Bastion
52
|
Posted - 2017.02.20 15:30:00 -
[12] - Quote
Jaseac wrote:I thought about creating my own program/app but I would guess that is alot harder than even Excel.
It is. But it'd be a custom tool, so much more adapted to the task. Better in every way.
The above message presents my opinions on the topic at hand. If there is a conflict between my views and reality, consider reality to be correct until proven otherwise.
|
Oriella Trikassi
Trikassi Enterprises
5
|
Posted - 2017.02.22 03:02:29 -
[13] - Quote
Jaseac wrote: =FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")
Ok, so I got it working, but for some reason eve central doesnt show prices for over half the items. Even tried looking them up manually. They are priced on eve market, but not on eve central
The Third Party apps and databases rely on players sending in data, as CCP does not publish Regional Market data out of game. Eve Market has gaps too! The two seem to have different criteria for what stuff they delete when out of date.
For high volume commodity stuff that sells at Trade Hubs Eve Central rarely fails. If you are searching for big profit unobvious niche items then the databases aren't so forthcoming.
|
Sassums
Repo Industries
134
|
Posted - 2017.02.28 05:05:07 -
[14] - Quote
Oriella Trikassi wrote:Quote:Is there really no function to pull the item # out of column a and look up and import the price into column c?
Like this? Price of something at Jita. =FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min") where cell A7 has the typeID or more likely a VLOOKUP to a Name >> ID table. FILTERXML is the Excel single cell function for downloading one thing from an XML table. WEBSERVICE gives the URL and passes on parameters, in this case the system and the itemID. The expression at the end defines which bit of the XML table we want, in this case the minimum sell price. Other possibilities should be obvious, "/type/buy/max" is the top buy price for example. VLOOKUP is the simple but limited Excel way of pulling data from a table. The more complex but more powerful one is INDEX MATCH which uses Boolian logic and arrays, best of luck with that! I can write the things but can't recall how I did it afterwards...
This works great! thank you!
How does excel ping the EVE servers for updated prices? Does one have to do it manually or does it automatically update itself?
|
Oriella Trikassi
Trikassi Enterprises
5
|
Posted - 2017.02.28 13:16:16 -
[15] - Quote
Sassums wrote:How does excel ping the EVE servers for updated prices? Does one have to do it manually or does it automatically update itself? Good question!
CTRL+ALT+F5 refreshes everything except WEBSERVICE which Excel treats as GÇ£non-volatileGÇ¥. It refreshes only when
GÇó A referenced cell is edited (pressing Enter isn't good enough) GÇó You press the secret key combination CTRL+ ALT + F9
I do both once a day which is good enough for me. Spamming the servers will get you banned!
|
Sassums
Repo Industries
134
|
Posted - 2017.02.28 21:41:13 -
[16] - Quote
Oriella Trikassi wrote:Sassums wrote:How does excel ping the EVE servers for updated prices? Does one have to do it manually or does it automatically update itself? Good question! CTRL+ALT+F5 refreshes everything except WEBSERVICE which Excel treats as GÇ£non-volatileGÇ¥. It refreshes only when GÇó A referenced cell is edited (pressing Enter isn't good enough) GÇó You press the secret key combination CTRL+ ALT + F9 I do both once a day which is good enough for me. Spamming the servers will get you banned!
Once a day is good enough for me - I am using it for simple T3 production so I dont need prices update more than that!
Thank you for the assistance it is greatly appreciated.
|
Sassums
Repo Industries
134
|
Posted - 2017.03.02 02:42:12 -
[17] - Quote
Oriella Trikassi wrote:Sassums wrote:How does excel ping the EVE servers for updated prices? Does one have to do it manually or does it automatically update itself? Good question! CTRL+ALT+F5 refreshes everything except WEBSERVICE which Excel treats as GÇ£non-volatileGÇ¥. It refreshes only when GÇó A referenced cell is edited (pressing Enter isn't good enough) GÇó You press the secret key combination CTRL+ ALT + F9 I do both once a day which is good enough for me. Spamming the servers will get you banned!
Does this update all the pages in the workbook, or just the sheet you are on?
|
Oriella Trikassi
Trikassi Enterprises
5
|
Posted - 2017.03.02 16:08:37 -
[18] - Quote
Sassums wrote:Does this update all the pages in the workbook, or just the sheet you are on? Both those are global.
|
Mossyblog Barnes
Pandemic Horde Inc. Pandemic Horde
0
|
Posted - 2017.03.03 06:31:36 -
[19] - Quote
No,
Use http://eve-markets.net/ and then build out your own "List" and use the XML/JSON/RSS feed from there to pull the info into Excel. In fact Excel prefers XML over other formats given its a throw-back to the days when SOAP was all the rage, kids wore baggy pants, Kriss Kross would make you jump and that movie Titanic was a hit.
Story time is always better told when discussing XML. |
Sassums
Repo Industries
134
|
Posted - 2017.03.03 06:54:32 -
[20] - Quote
Oriella Trikassi wrote:Sassums wrote:Does this update all the pages in the workbook, or just the sheet you are on? Both those are global.
Thanks appreciate it. |
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |