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.
 

Give me a hand with a spreadsheet?

First post
Author
Previous Topic Next Topic
Jeremy Kamira
#1 - 2014-05-16 04:37:02 UTC  |  Edited by: Jeremy Kamira
Hey guys, i am somewhat decent in creating spreadsheets and would like some help. I apologize if most of these are stupid questions.
This is Microsoft Excel.

So what i am doing is trying to get the values of Tritanium and Pyerite into excel (From eve central, based on jita prices), so what i did was use "From Web" and used this web query thingy.

http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&regionlimit=10000002

I select the table that is there and i end up with a bunch of numbers into excel. I was cross referencing them and they seemed to be right so guess i am on the right track.

So my questions are.

1. How is the data updated?

2. How can i make it so my spreadsheets don't have those ugly tables all over them? Is there a way to reference a certain cell on another sheet? Or an easier way perhaps?
PLEX Sell
Profiteering Inc.
#2 - 2014-05-16 09:25:19 UTC
If you use Google Spreadsheets, you can use:
=IMPORTXML("http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002","//sell/min")

This will give you the value of minimum sell for typeid=34.

You can replace //sell and /min with the appropriate values.

Hope this helps!
RJander
Federal Navy Academy
Gallente Federation
#3 - 2014-05-16 11:28:44 UTC  |  Edited by: RJander
1. you have to update your self.. in the ribbon "data", search for refresh

2. take time to search for excel tuto in google.. because your question reflect you don't know nothing (John Snow..)

3. you are in the wrong forum btw
Caerfinon
Caldari Provisions
Caldari State
#4 - 2014-05-16 12:00:46 UTC
Put all your ugly table on it's own tab.

Create a new tab and then make references to your table with the ugly tabs. Say the price of what you are looking for is in a tab called "Jita Prices" in cell B3 use;

='Jita Prices'!B3

were you want numbers and calculations to reference your ugly table value.





Cheers C.

@Caerfinon - Twitter
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2014-05-16 12:17:07 UTC  |  Edited by: Steve Ronuken
you can set the data source to refresh on open, in the connections menu.

Useful things to know about cells.

'sheet name'|A27 refers to cell A 27 on 'sheetname'

If you drag the small square in the bottom right of the cell, you can fill other cells. However, this will change which cell is being referenced.

i.e. drag it down, and the newly filled cell will refer to A28. Drag right, and it will refer to B27. diagonally, and it'll be B28. and so on.

If you need to lock which cell is referred to, while filling, use a $ on the bit you want to lock. $A27, A$27, $A$27 are all viable.


A function you may find very useful is vlookup. It's used to take a value (say, 'Pyerite') and look it up in a list, before returning a value next to it. So if you had a list of prices by name, you could use vlookup to get the price for something.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jeremy Kamira
#6 - 2014-05-16 18:12:05 UTC
Thanks guys, i will try this all out and come back with more questions.

Also RJander, i decided to post in this subforum because people here would be really familiar with spreadsheets.
Abidal Trekt
Caldari Provisions
Caldari State
#7 - 2014-05-16 20:27:32 UTC
Jeremy Kamira wrote:
Thanks guys, i will try this all out and come back with more questions.

Also RJander, i decided to post in this subforum because people here would be really familiar with spreadsheets.

Spreadsheets round the parts, eh? Nope, monkeys with typewriters.
Jeremy Kamira
#8 - 2014-05-17 03:45:16 UTC  |  Edited by: Jeremy Kamira
Here is what i have been experimenting with, just so i can get a hang of it.

This will tell you how much ISK you get for every 1000LP on certain items. Could someone who is a spreadsheet guru do a quick look over and make sure that i am doing everything right? I don't want to make any errors early on and mess up all my future spreadsheets What? All of the prices are retrieved from eve central and all of the other numbers are put in by hand. Prices are based upon sell/min and buy/max.
The formula i used is "(Sell price - (LP store ISK cost + (item cost * amount))) / LP"

https://docs.google.com/spreadsheet/ccc?key=0ArnhN87yaTvddHZfOWNGNnlhVGNlNXlHaHg3ZXRUQ1E&usp=sharing
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2014-05-17 17:49:27 UTC
(This is an aside, as there is always a reason to have more things bieng made. http://www.fuzzwork.co.uk/lpstore/ )

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jeremy Kamira
#10 - 2014-05-18 05:13:38 UTC
Steve Ronuken wrote:
(This is an aside, as there is always a reason to have more things bieng made. http://www.fuzzwork.co.uk/lpstore/ )


Thank you! I have already seen and have been using this tool. The reason why i wanted to start experimenting with an LP calc is so i could throw my own numbers up against the ones in there to see if i am right.

Also a question on this formula.

=IMPORTXML("http://api.eve-central.com/api/marketstat?typeid=23009&regionlimit=10000002","//buy/max")*5000

I am talking about the buy/max section. I am pretty sure that it is getting the biggest buy order data from eve central. How would i modify it so i could get the top 5% of the orders and not just the top?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#11 - 2014-05-18 06:45:10 UTC
Jeremy Kamira wrote:
Steve Ronuken wrote:
(This is an aside, as there is always a reason to have more things bieng made. http://www.fuzzwork.co.uk/lpstore/ )


Thank you! I have already seen and have been using this tool. The reason why i wanted to start experimenting with an LP calc is so i could throw my own numbers up against the ones in there to see if i am right.

Also a question on this formula.

=IMPORTXML("http://api.eve-central.com/api/marketstat?typeid=23009&regionlimit=10000002","//buy/max")*5000

I am talking about the buy/max section. I am pretty sure that it is getting the biggest buy order data from eve central. How would i modify it so i could get the top 5% of the orders and not just the top?



The average of the top 5%? That's //buy/percentile

If you want the actual orders, I'm not sure that api supplies them.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter