Fuzzwork
If you’ve read my older post on it, using Crest with Excel can be a complete pain in the ass. You have to load in a module, then write something in VBA, using it, because VBA doesn’t support json natively, and doing it with a user defined function, well, I never got that far, before throwing my hands up, and walking away.
Now, I got that working, but I didn’t like it. I ran into power query for a while, and it kinda worked, but I didn’t find a way to easily get it to pull a bunch of data, and reprocess it the way I wanted. So not so good for CREST prices.
So I was still on the lookout. And I recently came across xlwings, and it made me very happy indeed. I like Python, and I’m fairly good with it now. So, combining the two seemed like a winner.
After a little playing with it, I got it all working, and I now have an excel sheet, which does CREST lookups, and all it needs, is python, xlwings, and requests installed. So, to the instructions:
Install python. I’ve been using 2.7.10, but 3 should work just fine too. Make sure it’s in your path.
Install pywin32 (I’m using this one, for 2.7, 32 bit)
Using Pip, install xlwings. (you’ll do this in a command prompt)
[where you installed python, like c:\python27]\scripts\pip install xlwings
Using pip, install requests.
[where you installed python, like c:\python27]\scripts\pip install requests
using pip, install requests-security, to stop it bugging you
[where you installed python, like c:\python27]\scripts\pip install requests[security]
Now, go to a directory where you want to create your new workbook, using your command prompt. run the following command (myneatcrestbook, is the name of your new workbook. change it now if you want.):
xlwings quickstart myneatcrestbook
If this fails, it’s probably because your python scripts directory isn’t in your path. So try
[where you installed python, like c:\python27]\scripts\xlwings quickstart myneatcrestbook
With that done, you’ll find a new directory, containing an excel sheet, and a python file. open the excel sheet, and enable macros (it’ll probably just prompt you). You’ll also want to turn on the developers ribbon, in the file->options->customize ribbon option.
On the developers ribbon, click ‘macro security’, and check the ‘trust access to the VBA project object model’. hit ok.
Then click the ‘excel add-ins’ button, and click browse. Browse to [where python is]\Lib\site-packages\xlwings, and select the xlwings.xlam file. hit open, then check the box next to xlwings.
You should now have an xlwings ribbon entry.
That done, you can add code to the python file, come back to excel, and hit ‘import python UDFs’, to make them live.
When you want it to reprocess your functions (say, to update the data again, and get new prices) use ctrl+alt+F9
Below you’ll find a copy of a simple script to get the 5% average sell price. called with =crestSellPrices(34,10000002)
from xlwings import xlfunc
import requests
@xlfunc
def crestSellPrices(typeid,regionid):
response=requests.get('https://public-crest.eveonline.com/market/{}/orders/sell/?type=https://public-crest.eveonline.com/types/{}/'.format(int(regionid),int(typeid)))
data=response.json()
count=data['totalCount']
numberOfSellItems=0
sellPrice=dict()
for order in data['items']:
sellPrice[order['price']]=sellPrice.get(order['price'],0)+order['volume']
numberOfSellItems+=order['volume']
# generate statistics
if numberOfSellItems:
prices=sorted(sellPrice.keys())
fivePercent=max(numberOfSellItems/20,1)
bought=0
boughtPrice=0
while bought<fivePercent:
fivePercentPrice=prices.pop(0)
if fivePercent > bought+sellPrice[fivePercentPrice]:
boughtPrice+=sellPrice[fivePercentPrice]*fivePercentPrice;
bought+=sellPrice[fivePercentPrice]
else:
diff=fivePercent-bought
boughtPrice+=fivePercentPrice*diff
bought=fivePercent
averageSellPrice=boughtPrice/bought
return averageSellPrice
