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

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

Science & Industry

 
  • Topic is locked indefinitely.
12Next page
 

I give up! excel stuff...
Author
Previous Topic Next Topic
Jason McCoy
Deep Core Mining Inc.
Caldari State
#1 - 2012-11-20 02:02:08 UTC
OK guys ive been trying this for years now without any luck at all. Ive done my own research and to be honest I have no effing clue what to actually search for. I have asked a few times here without any pointers.

I really want your help on this.

I want to be able to queue market prices from Jita 4-4. Specifically the moon minerals, simple and complex reactions. YES ALL OF THEM.

I need POS fuel queues as well.

I have built a spreadsheet that you input the fuel block cost per unit, you input your sell and buy orders from the market.
It automatically outputs what reactions either simple or complex are worth doing with percent gains on investment. It also calculates how much it will cost you to buy the materials to run the reaction on a daily, weekly, bi-weekly and monthly basis.
It does all this using fuel calculations to keep the pos up as well.

But it requires a lot of tedious manual input for every item.
What is the code in order to just open the spreadsheet and it runs the market queue?
Is it possible to have it only run once a day or only every time the sheet is opened? I dont want to spam the server either.

I am at a total loss here. Please help.
Styth spiting
Brutor Tribe
Minmatar Republic
#2 - 2012-11-20 06:09:37 UTC
Jason McCoy wrote:
OK guys ive been trying this for years now without any luck at all. Ive done my own research and to be honest I have no effing clue what to actually search for. I have asked a few times here without any pointers.

I really want your help on this.

I want to be able to queue market prices from Jita 4-4. Specifically the moon minerals, simple and complex reactions. YES ALL OF THEM.

I need POS fuel queues as well.

I have built a spreadsheet that you input the fuel block cost per unit, you input your sell and buy orders from the market.
It automatically outputs what reactions either simple or complex are worth doing with percent gains on investment. It also calculates how much it will cost you to buy the materials to run the reaction on a daily, weekly, bi-weekly and monthly basis.
It does all this using fuel calculations to keep the pos up as well.

But it requires a lot of tedious manual input for every item.
What is the code in order to just open the spreadsheet and it runs the market queue?
Is it possible to have it only run once a day or only every time the sheet is opened? I dont want to spam the server either.

I am at a total loss here. Please help.


Honestly, you'll be better off using google docs spreadsheets for this. Excel has just awful support for doing queries from non-microsoft external tools. Where as with google docs its almost as simple as simply setting a sell to query a page (exe-central for example) to pull the items price in jita and display it on a cell

Id suggest looking at how some of the more popular public google doc spreadsheets pull data from the market websites and just duplicate it to match your needs.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2012-11-20 10:28:13 UTC  |  Edited by: Steve Ronuken
http://eve-marketdata.com/developers/item_prices2.php

specifically:
http://api.eve-marketdata.com/api/item_prices2.xml?char_name=yourname&buysell=s&solarsystem_ids=30000142

load that up as a new sheet (data, from web). It'll give you pretty much all the prices in jita for things.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Matt Ellis
Caldari Provisions
Caldari State
#4 - 2012-11-20 10:43:02 UTC
if you wanna import prices of things automatically, then you can use the market API to do this..

I use them myself when working out fuel costs, and production costs.

Quote:
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=4246","evec_api/marketstat/type/sell/min")


For instance, that in there, is the import for Minmitar fuel blocks, through sell orders

typeid: the id of the product you want to grab

List of Type IDs

sell/min: obviously, this is the type of order, so sell or buy, and the min,max or average i think..

I use Googledocs for mine, but bear in mind, i think there is a limit to the amount of XML imports you can do in a file...
Master Kent
Sons Of Korhal
#5 - 2012-11-20 17:55:28 UTC
Steve Ronuken wrote:
http://eve-marketdata.com/developers/item_prices2.php

specifically:
http://api.eve-marketdata.com/api/item_prices2.xml?char_name=Zathor%20Baal&buysell=s&solarsystem_ids=30000142

load that up as a new sheet (data, from web). It'll give you pretty much all the prices in jita for things.




Does not work google disk only allows you 50 ithems to be pulled by api Sad
Styth spiting
Brutor Tribe
Minmatar Republic
#6 - 2012-11-20 18:23:00 UTC
Master Kent wrote:
Steve Ronuken wrote:
http://eve-marketdata.com/developers/item_prices2.php

specifically:
http://api.eve-marketdata.com/api/item_prices2.xml?char_name=Zathor%20Baal&buysell=s&solarsystem_ids=30000142

load that up as a new sheet (data, from web). It'll give you pretty much all the prices in jita for things.




Does not work google disk only allows you 50 ithems to be pulled by api Sad


You can get around that though. But more importantly then getting around the 50 limit is decreasing the number of queries made (instead of doing 100 queries, why not 10 queries with 10 items).

If you look at the Jita PI google doc you will see they have over 86 items, with buy/sell, etc etc data. You can see they do a number of queries, which each query pulling in data on 15 items.


https://docs.google.com/spreadsheet/ccc?key=0Ar_YTOcXpvb9dGFIdHJlb0VmYXBORWRrcXFkeHNjcWc#gid=10

For example one xmlimport URL looks like http://api.eve-central.com/api/marketstat?usesystem=30000142&hours=48&typeid=2268&typeid=2305&typeid=2267&typeid=2288&typeid=2287&typeid=2307&typeid=2272&typeid=2309&typeid=2073&typeid=2310&typeid=2270&typeid=2306&typeid=2286&typeid=2311&typeid=2308&regionlimit=10000002

So you can get around the 50 max xml requests.
Aziesta
Binal Extensions
Xagenic Freymvork
#7 - 2012-11-20 18:43:15 UTC
I use Excel to pull info from eve-central's website all the time:

1) In your excel sheet, make a new tab (or "worksheet"). Rename it something obvious.
2) Go to Data -> From Web
3) Use the (crappy) browser to navigate to the eve-central page that has the item (and system) you're looking for
4) Use the check boxes to select whether you want to import buy or sell info.
5) Use cell references other places to reference this tab. If you use the default options, your price will end up in cell C2.

When you want to update, go to Data -> Refresh All. Just make sure you have 5 minutes or so, as it's not quick.
Jason McCoy
Deep Core Mining Inc.
Caldari State
#8 - 2012-11-21 00:38:28 UTC
Matt Ellis wrote:
if you wanna import prices of things automatically, then you can use the market API to do this..

I use them myself when working out fuel costs, and production costs.

Quote:
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=4246","evec_api/marketstat/type/sell/min")


For instance, that in there, is the import for Minmitar fuel blocks, through sell orders

typeid: the id of the product you want to grab

List of Type IDs

sell/min: obviously, this is the type of order, so sell or buy, and the min,max or average i think..

I use Googledocs for mine, but bear in mind, i think there is a limit to the amount of XML imports you can do in a file...

This! X1000000!!! this is what I needed. A few months ago I was trying this but I guess I was writing the code wrong or something and I had given up. Thank you sir!


Thank you to all who replied as well. All input is very welcomed.
Tau Cabalander
Retirement Retreat
Working Stiffs
#9 - 2012-11-21 00:52:20 UTC
I've been manually entering mineral, moon products, and ice products, into my spreadsheet for years. Takes very little time.

However, I did write a PHP web page for estimating the value of all Sleeper drops based on eve-central prices. It is easier to use, but FAR FAR LESS accurate. I found fetching the prices from eve-central to be quite slow, but not as slow as manually entering them.

Just beware that with automated price retrieval from a place like eve-central, you are not getting current market prices nor volumes.
Jason McCoy
Deep Core Mining Inc.
Caldari State
#10 - 2012-11-21 01:02:43 UTC
Right but even if its off by a few isk here and there, im not going to be pulling my hairs over it. I dont do jita runs everyday, I do them maybe once a week or two, so its not a big deal. Thank you for the heads though.
Styth spiting
Brutor Tribe
Minmatar Republic
#11 - 2012-11-21 01:11:20 UTC
Tau Cabalander wrote:
I've been manually entering mineral, moon products, and ice products, into my spreadsheet for years. Takes very little time.

However, I did write a PHP web page for estimating the value of all Sleeper drops based on eve-central prices. It is easier to use, but FAR FAR LESS accurate. I found fetching the prices from eve-central to be quite slow, but not as slow as manually entering them.

Just beware that with automated price retrieval from a place like eve-central, you are not getting current market prices nor volumes.


I'm guessing you're doing each separate item as its own xml query. You should instead be doing as many queries as you can at one time (eve-central allows this) so you can get more results with less xml requests. Doing 10 requests for 100 ivems will be far faster then 100 requests for 100 items.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#12 - 2012-11-21 01:16:22 UTC  |  Edited by: Steve Ronuken
Master Kent wrote:
Steve Ronuken wrote:
http://eve-marketdata.com/developers/item_prices2.php

specifically:
http://api.eve-marketdata.com/api/item_prices2.xml?char_name=yourname&buysell=s&solarsystem_ids=30000142

load that up as a new sheet (data, from web). It'll give you pretty much all the prices in jita for things.




Does not work google disk only allows you 50 ithems to be pulled by api Sad



Google's limiting you to 50 lines returned?

Because otherwise that'll pull pretty much everything.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Emma Royd
Maddled Gommerils
#13 - 2012-11-21 06:01:20 UTC
Jason McCoy wrote:
Right but even if its off by a few isk here and there, im not going to be pulling my hairs over it. I dont do jita runs everyday, I do them maybe once a week or two, so its not a big deal. Thank you for the heads though.


As long as you treat the data pulled via eve-central etc as a GUIDELINE and not fixed you'll be ok, but always have some way of manually entering the real time prices before you start planning to build or whatever you're doing with the data.

ie have 2 columns, 1 with the eve-central data in, and the other is what you work off, it's easy enough to copy the data from the eve-central column to the one you work off to get an estimate, then overwrite these values with the accurate values should something look promising to double check.
TryfanMan
Cevelo
#14 - 2012-11-27 19:09:05 UTC
It's a bit out of date, but here's a T1 ship Goggle Docs SS I pulled together:

https://docs.google.com/spreadsheet/ccc?key=0Ak_Ay8VPHDzXdDAtNkZZVHRfLUZWUEtYd19HMldpbVE

If nothing else, it's something else which might give you some ideas.
Uppsy Daisy
State War Academy
Caldari State
#15 - 2012-11-27 19:39:55 UTC  |  Edited by: Uppsy Daisy
It always used to be possible to automate an in-game search through the market via the in game browser, then fire up a program to scrape the prices out of the game's cache files.

Fast, legal and accurate.

Slightly technical though I guess.
Nalha Saldana
Aliastra
Gallente Federation
#16 - 2012-11-28 12:59:22 UTC
Matt Ellis
Caldari Provisions
Caldari State
#17 - 2012-11-29 12:10:39 UTC
Nalha Saldana wrote:
So this is exactly what you're looking for
https://docs.google.com/spreadsheet/ccc?key=0Av6bUJhtOy5qdHJQSGNtMzk1bmpwOFZZZkdXOU53OGc


Nahla, while trying to use your spread to calc profits, i noticed there is an error with your working for 'PPD Fullerine Fibres'. You have it down as needing 100 of each c50 and c60, where it is actually 300 C50 and 100 C60. Almost had me investing in a potentially loss making reaction there.. hehe.
Nalha Saldana
Aliastra
Gallente Federation
#18 - 2012-11-29 13:29:56 UTC
Matt Ellis wrote:
Nalha Saldana wrote:
So this is exactly what you're looking for
https://docs.google.com/spreadsheet/ccc?key=0Av6bUJhtOy5qdHJQSGNtMzk1bmpwOFZZZkdXOU53OGc


Nahla, while trying to use your spread to calc profits, i noticed there is an error with your working for 'PPD Fullerine Fibres'. You have it down as needing 100 of each c50 and c60, where it is actually 300 C50 and 100 C60. Almost had me investing in a potentially loss making reaction there.. hehe.


Im so sorry, the whole hybrid reactions part is new and I havent personally used it yet so there might be more errors in it.
Thanks for letting me know tho, its fixed now!
Matt Ellis
Caldari Provisions
Caldari State
#19 - 2012-11-29 13:40:48 UTC
Nalha Saldana wrote:
Matt Ellis wrote:
Nalha Saldana wrote:
So this is exactly what you're looking for
https://docs.google.com/spreadsheet/ccc?key=0Av6bUJhtOy5qdHJQSGNtMzk1bmpwOFZZZkdXOU53OGc


Nahla, while trying to use your spread to calc profits, i noticed there is an error with your working for 'PPD Fullerine Fibres'. You have it down as needing 100 of each c50 and c60, where it is actually 300 C50 and 100 C60. Almost had me investing in a potentially loss making reaction there.. hehe.


Im so sorry, the whole hybrid reactions part is new and I havent personally used it yet so there might be more errors in it.
Thanks for letting me know tho, its fixed now!


Don't be! I'm building my own little spread for complex reactions so i'll let you know if there are any more when i get that done..:D
Matt Ellis
Caldari Provisions
Caldari State
#20 - 2012-11-29 13:45:52 UTC  |  Edited by: Matt Ellis
Oh, and good job on the spread though, just trying to pick the best money spinner for me..:D
12Next page