EVE Technology Lab

Goal:
I'm working on a spreadsheet that will allow me to view every T1 item, what it is reprocessed into, the cost of said reprocessed minerals on a few markets, and also the price of just selling the item on those same markets

So Far:
I have a sheet that lists every T1 item and what it breaks down into. (I searched and eventually found a thread but can't find it again to link. Apologies.) I've been trying to read into setting up queries using user specific parameters but I'm not getting very far. Not sure if I'm searching the right terms but i haven't found much info. The info I have found has been pretty confusing. I'm not new to excel but I am new to databases and coding. I understand this is a large project but whats the point of learning how to do something if I will never use the tool I just made?

The Setup (Ideal):
Using the spreadsheet I found on the old forums (again sorry I forgot the link) as a template:
Page 1
Column B - List of all the T1 items
Column C - Volume of the item
Column D - Volume of the minerals after the item is reprocessed
Column E - Current median sell price in Amarr for the item
Column F - Current median sell price of the items (added up) in Amarr
Column G,H - Same as E,F except using Jita Prices
Page 2
All T1 items, itemid's, volume, and what they reprocess into.

Questions:
"IF" I get this sheet working would it lag so bad fetching the prices to make it not worthwhile?

Can anyone point me to a site or thread that can explain how to edit queries to only show the information I want and not the whole XML chart. Example: I do a query for the price of Trit in Amarr but I only want to see the median sell price not everything else so that just the price shows up in a specified cell.

Can anyone point me to a site or thread that can explain how (if possible) to make queries based on Cell information. Example:The eve-market query to pull all the info for the price of Trit in Jita would look like - http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002
Can I use the address as a template and use cell info for each query like this - http://api.eve-central.com/api/marketstat?typeid="CellA1"&regionlimit="CellJ1"

Any help on this project would be greatly appreciated and after I am done (If it is possible) I will release it for anyone else that wants to use it.

Hey Bertram

TL;DR (ill link a cheatsheet in the bottom of the post :) )

If you need any help ill give out some pointers - just catch me ingame :)

First of all, read up on IQY:

http://www.vertex42.com/News/excel-web-query.html

You can setup the IQY to change on cell value change, just like requested.

NOTE: there is 2 kinds of IQY, one is build outside your "main" sheet, and would be called "abcxyz.iqy" as a file, which excel can read. but that would require a call to another sheet every time you want to query something.

instead you can create the same query inside your own sheet.

Go to Data -> New webrequest and enter the a query string. (see example below)

here is query string:

http://api.eve-central.com/api/quicklook?typeid=["paramname","Enter%20the%20value%20for%20paramname:"]

Which basicly is the following:
URL + ["paramname","info about the parameter"] + &region=["param2","info about the second parameter"]

When you get the query up and running you can then bind each parameter to a cell in your Excel file(rightclick on the data and choose parameters)

You may want to fetch some data first with this string
http://api.eve-central.com/api/quicklook?typeid=34

Then change it to
http://api.eve-central.com/api/quicklook?typeid=["paramname","Enter%20the%20value%20for%20paramname:"]

---

Cheatsheet:
http://blackproject.dk/storage/eve-iqy-cheatsheet.xls

This contains 2 pages (main & data)

On main you will see the parameter cell, if you change the cell value to reflect a different typeid, the data will automagically change on the data page.(watch the globe on the bottom of the excel, as it tells you that new data is being fetched.)