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

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

Market Discussions

 
  • Topic is locked indefinitely.
 

Spreadsheet help
Author
Previous Topic Next Topic
Dragonfel
Magitek Consortium
#1 - 2012-03-12 15:25:24 UTC
Hey guys, I've been creating some spreadsheets to help me calculate the profitability of various things I do in Eve. These range from LP to isk conversion rates, average value of mission salvage, PI profitability etc. The big issue I'm having is that I have to update all my market prices of the items involved manually. I was thinking that there must be an easier way to do this and I realized that if I had a program that would take a snapshot of the market and create a spreadsheet for every marketable item, I could just make references to that spreadsheet instead of manually updating values like an idiot.

Anyone know if such a program exists or have any advice for keeping spreadsheets accurate over time?
Dan Osiris
Never Not Contract
#2 - 2012-03-12 15:46:08 UTC
you could just use that spreadsheet as a reference, and then just look at all the price changes in your quick bar and figure out the change in profitability in your head.... the margins don't change that much (in jita anyway)
Strategos
Echelon Research
Goonswarm Federation
#3 - 2012-03-12 16:04:29 UTC
Dragonfel
Magitek Consortium
#4 - 2012-03-12 16:23:51 UTC
Strategos wrote:
http://eve-central.com/home/develop.html

Have fun.


Ahah! That's interesting, I didn't realize Eve-Central made daily database dumps available. How do you import them into excel?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2012-03-12 16:32:45 UTC
What might work better for you, is using the API, rather than the dumps. If you're using the same items every time.

Then you can just refresh the data

There's also eve-marketdata and eve marketeer. Both provide apis. The eve marketeer one, in semicolon delimited format is very easy to use.

http://www.evemarketeer.com/api

You'll want to use the aggregated stats.
http://www.evemarketeer.com/api/info/34_35_36_37_38_39_40/csv/10000043/sell_lowest5

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Dragonfel
Magitek Consortium
#6 - 2012-03-12 16:55:04 UTC
I've never imported data from a website into excel before, could you give me an example of how I can use this to find the median sell price of an item (let's use Water, a good PI example) in the Heimatar region (I'm based out of Rens mostly).

Thanks very much!
Strategos
Echelon Research
Goonswarm Federation
#7 - 2012-03-12 17:17:56 UTC
Dragonfel wrote:
I've never imported data from a website into excel before, could you give me an example of how I can use this to find the median sell price of an item (let's use Water, a good PI example) in the Heimatar region (I'm based out of Rens mostly).

Thanks very much!



Which version of excel?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#8 - 2012-03-12 17:28:44 UTC  |  Edited by: Steve Ronuken
The example above gets the price for a simulated 5% buy of market volume. It's generally a better way of getting the 'average' price you'll pay for something, than the median. It neatly eliminates the outlier values.

Anyway, assuming Excel 2010 (as it's all I have to hand)


Go to the data tab.
hit 'from text''
Give it the url when it asks for a file.
leave it on delimited. hit next
tick 'semicolon'. hit next.
hit finish.
select 'new worksheet'. hit ok.


You should now see that 'refresh all' is available, so you can update it at will Smile


Now, the example above is for minerals. Which have typeids between 34 and 40 (and there's one out in the 11000 or so). /You'll need to find the type ids for what you want. Easiest way is:

Grab the typeid to name csv from my site. http://www.fuzzwork.co.uk/resources/typeids.csv
You can get it in the same way as the prices (just make sure , is selected as a delimiter), if you want it to be easily updatable, when CCP update their static data dump. It's set up typeid,typename,typeid, to allow easy bidirectional vlookups.

Then just look them up. Water is 3645.
Update the url with the typeid you want.


http://www.evemarketeer.com/api/info/3645_2398/csv/10000043/sell_lowest5

would get you oxygen and reactive metals.

If you're not comfortable with them now, go look at the vlookup function. you'll need it.




Oh, and if anyone is using Open/Libre Office, 'insert sheet from file' (iirc) will allow for much the same functionality. far easier than messing with XML.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Dragonfel
Magitek Consortium
#9 - 2012-03-12 22:41:01 UTC
Steve Ronuken wrote:
The example above gets the price for a simulated 5% buy of market volume. It's generally a better way of getting the 'average' price you'll pay for something, than the median. It neatly eliminates the outlier values.

Anyway, assuming Excel 2010 (as it's all I have to hand)


Go to the data tab.
hit 'from text''
Give it the url when it asks for a file.
leave it on delimited. hit next
tick 'semicolon'. hit next.
hit finish.
select 'new worksheet'. hit ok.


You should now see that 'refresh all' is available, so you can update it at will Smile


Now, the example above is for minerals. Which have typeids between 34 and 40 (and there's one out in the 11000 or so). /You'll need to find the type ids for what you want. Easiest way is:

Grab the typeid to name csv from my site. http://www.fuzzwork.co.uk/resources/typeids.csv
You can get it in the same way as the prices (just make sure , is selected as a delimiter), if you want it to be easily updatable, when CCP update their static data dump. It's set up typeid,typename,typeid, to allow easy bidirectional vlookups.

Then just look them up. Water is 3645.
Update the url with the typeid you want.


http://www.evemarketeer.com/api/info/3645_2398/csv/10000043/sell_lowest5

would get you oxygen and reactive metals.

If you're not comfortable with them now, go look at the vlookup function. you'll need it.




Oh, and if anyone is using Open/Libre Office, 'insert sheet from file' (iirc) will allow for much the same functionality. far easier than messing with XML.


You sir have rocked my world and taught me something new about Excel. Thank you very very much!
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#10 - 2012-03-12 22:43:53 UTC
If you're loading an XML one in excel, just use the 'From Web' option.

It's pretty self explanatory, when you go through it. Better than google's =importxml() as it handles tabular data properly, rather than single attribute. Open/Libre office can do it, but it's a pain in the ass, needing some code and XSDs

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter