PI Google Doc more robust

I have made my PI spreadsheet on google docs more robust.

For those that don't know, I have a google doc spreadsheet that lists the buy and sell prices for PI (planetary interaction) mats at different trade hubs, and also list what is profitable to manufacture on planet based on those prices.  The manufacture profitability is split up in several scenarios: Import all, import some + extract some, or extract all.

Google docs don't like pulling lots of data from eve central (or even crest) on a regular basis.  It works (mostly) ok for the spreadsheet owner, but not so well for other users.  I also have a 'force refresh' option by changing a 'cachebuster' parameter, to insist that I get new prices.

The fix is that I now have 3 'pricing' tabs:

• Central : grab the prices from eve central.  This is up to date when it works, and broken otherwise.
• Snapshot: Every so often, I will copy the data from the Central tab to static data on the Snapshot tab
• Main (first tab): If 'Central' is working, use that data, if not use then use the Snapshot tab

As an end user, you will know whether you are using 'live' (ish) data or how old the snapshot is from the top left corner on the 'Main' tab.

If it is still broken for you, please leave a comment preferably with links to screenshots showing your errors.  If it is working I will already know by the growing number of users keeping the google doc open.

Fighting with markets (and software)

Despite being moderatly quiet on the blog, I am still busy in Eve.

My current project that I don't understand anywhere near well enough is how to use the Crest API, for pulling market information.

What I used to do.

I had previously used google docs and eve central's API, but google docs are flaky on something as small as my PI spreadsheet, and is unworkable for a full market listing.

What I am currently doing.

I use Steve's python based crest downloader to pull down all buy and sell orders for a region ( https://github.com/fuzzysteve/CREST-Market-Downloader ). I have some success, but what works reliably for him in pulling down an entire region of market orders in 15 minutes, takes 4 hours for me and only works 1/3 or the time.

I also pull down my current asset list and market orders using jeveassets, peeking into both corp and personal assets.  Despite having my own division in the corps that I am currently in, I have to filter out 'others' assets from my own. (using sql per below)

While I am no wizard at either Perl or Java, I am very good at SQL.  So I upload these 2 data sets and put them into a MariaDB instance (MySQL fork/clone - but much less hassle to download)

Knowing how to map item ids to names, groups, categories and meta levels is useful.  For that I use Steve's SDE dump, and as MariaDB/MySQL is my preferred database, https://www.fuzzwork.co.uk/dump/mysql-latest.tar.bz2 works well (other databases and versions in the same folder).

With all of this, a final SQL script can show me, by whatever market group I want to filter on, what my current stock counts are, and what is currently on market, the local price (if any), the jita price, my markup.

When this comes together, it works well.  When it doesn't it is a pain.

Alternatives

Evernus is a reasonable alternative for some of this functionality and I have looked at it.  It is not enough.

I cant work out how to make it show me all the skillbooks missing off the local market, then swap to look at either T1 or T2 ammo, then flit to the next part of the market I am interested in.  I have  2 tycoon and 1 wholesale level pilots, but there are 400 different skillbooks alone, and I have less than 700 market slots. If I was better at working out what was needed, I could probably make sure that was there, but have little chance of stocking everything wanted.

I got mildly excited by seeing Evernus Alliance Market ( https://bitbucket.org/krojew/evernus-alliance-market ).  It looks to be a good group logistics tool, and despite being written for a Linux environment, with a bit of fiddling, got it installed on a windows instance (there are a couple of evernus 'bug reports' with tips that I found useful ).  But .. it too didnt have the functionality I needed, much of which is in Evernus (including a quick crest sales/buy orders download).

The future

I am (mostly) enjoying the challenges of doing nullsec trading, but I want better tools.  Evernus is an open source software package, and I am in the process of downloading it plus the tools required to enhance it.

Tweak to PI spreadsheet

I have made a small tweak to my PI spreadsheets.

My PI spreadsheet prices have historically been based on average sell prices across 4 trade hubs (Amarr, Jita, Dodixie, Hek)

For me being wormhole based, Amarr is mildly preferable to Jita (and yes that even includes my stocking of nullsec) as Amarr is well overrepresented as the closest wormhole route, but any of them may be closest.

What I (and commenters) have noticed recently is that sometimes the average of 4 hubs is screwy because (generally Hek) has one or two really bad prices.

So now, I am using a slightly more complicated 'sell' price which is best sell of Hek or Rens and then averaging 'Minmatar', Amarr, Jita, Dodixie.

For those who have not seen the spreadsheet before, it's master is at https://docs.google.com/spreadsheets/d/1lXM8PqU0Tn4EJJ2xRuI7uo55IrZ7MKWJidK9T_bA9L4/edit?usp=sharing

As google docs is no longer reliably playing with eve central nicely, I also have a PDF version linked manually from  http://foo-eve.blogspot.com.au/p/pi-spreadsheet-snapshots.html (linked at the top of this blog). The most recent PDF version is https://drive.google.com/file/d/0B1HsU_rOxWl4VHI3YkVjUFJVazA/view?usp=sharing


PS.  My last post a fortnight ago was me saying I was rebuilding my Eve setup.  This turned into something more involved and ended up being a complete PC re-install from operating system up.

You profit from fixing my 'broken' PI spreadsheet

I occasionally get reports that my PI spreadsheet is broken.

Sometimes it is not working at all as I am fighting with Google itself, which is why it now is cached via http://foo-eve.blogspot.com.au/p/pi-spreadsheet-snapshots.html

But generally it is because the prices are 'wrong'.  I get my prices daily from Eve Central, and take the average prices from Jita, Amarr, Dodixie, and Hek.  I primarily do my PI in a wormhole, and one trade hub is as good as the next, with a mild preference for Amarr (due to ~40% wormholes ending up close to there).

So, there are 2 ways the prices can be wrong.  Firstly my cache of prices can be 24 hours old.  Shrug, you can copy my spreadsheet and change the CacheBuster parameter on the main sheet at A2 to force a price refresh.






Secondly, my price is an average over 4 trade hubs (ignoring no price at all).  If one of those hubs has a price that is wildly wrong, then all pricing running from that will also be (less) wildly wrong.

As of writing this post, my spreadsheet is getting the 'wrong price' for livestock, with an average sell of 145,000 ISK/unit.  The Jita price sell price is 9,500 ISK/unit, and my average buy price is 7,000 ISK/unit.

Both Eve Central and my spreadsheet see the only sell order for Livestock in Hek as 550,000 ISK/unit which by anyone's account is overpriced, especially for a trade hub.  I have no intention of fixing my spreadsheet, as it is working as intended.

However, there is a good trade opportunity for those wanting to fix the data I retrieve, buying some livestock anywhere, getting red frog/push x to haul it, and selling livestock in Hek.  If you are so inclined, I also note that some of the other trade hubs are also missing sell orders for PI. 

• Hek is missing Polyaramids, Silicate Glass, Ukomi Super Conductors
• Dodixie is missing Synthetic Synapses
• Rens is also missing a few items.

Yes, I could fill these gaps myself.  That would involve me spending more time in highsec.  If no one else does this inside of another week or so, I will get some stock sent to Hek, if only to fix my spreadsheet.

Two of the complaints I read about market trading are that there is no way to find things to trade, and how everything always is immediately undercut.  In the third biggest market in Eve ( Dodixie - http://eve-marketdata.com/station.php?step=Rank ), for Synthetic Synapses at least, neither of these complaints are true.

Cache of PI spreadsheet

I have created a Google Docs folder containing most recent PI price snapshots.  I will name the files with a "<YYYY-MM-DD> PI snapshot.pdf" format.  The first one is 2015-03-31 PI snapshot.pdf


I am fighting a losing battle with google and the Foo Eve PI spreadsheet.  It works fine for me, but  can't keep it working for everyone all the time, despite different caching strategies.  ( http://foo-eve.blogspot.com.au/2014/12/pi-spreadsheet-working-again.html )

So ... I will really cache it, this time as a PDF, manually.  As it is manual, it will be updated when I remember to do so, hopefully weekly.

I have plans on fixing this permanently, possibly as a google app.  But for now, this will do.  Please magnify or download for your favourite PDF reader.

Google docs whack-a-mole continued, plus a request of readers

My whack-a-mole with google docs is continuing.

I have added caching and a random delay to my open copy of the trading spreadsheet.  https://docs.google.com/spreadsheets/d/1qL_ofk-QRQCFt2zKTTEXSi6oY4n1gtZX6Mojxxtgs-4/edit#gid=1869404277

I think it should be ... less unstable than it was before.

Readers, can you please have open (and leave open) the above spreadsheet for a few days, using either Firefox or Chrome.  Eve's in game browser has no chance.  Internet explorer and Opera struggle.  It is a published document and I don't mind if you keep it open longer.

A full refresh (daily) will take a couple of minutes to load.  Apart from that, it should be fairly quick.

If it is breaking, please leave a comment on this page.  If you find yourselves using it (either the original - which I can track, or a copy - which I can't), please also leave comments about how.

This is a continuation of http://foo-eve.blogspot.com.au/2015/03/market-spreadsheets-in-space.html

Whack-a-mole with Google Docs and Eve Central

My primary PI spreadsheet ( https://docs.google.com/spreadsheets/d/1lXM8PqU0Tn4EJJ2xRuI7uo55IrZ7MKWJidK9T_bA9L4/edit#gid=0 ) is fixed again.


In my last post about Google Docs, I inadvertently tripped a google doc limitation.

I have added caching to this spreadsheet, and temporarily disabled it in my other spreadsheets.  This should not only resolve my urlfetch issue, but also significantly speed up the loading of the spreadsheet.  It comes at a 'cost' of encouraging the data to be up to 6 hours older.

I will be rolling the changes out to the other spreadsheets soon.

I also found a github code snippet from 'Haggen', also putting together google docs based eve-central calls https://gist.github.com/haggen/461a454b200efe89f3e6

For those interested, I have raised a bug on Fuzzy Steve's github repository, https://github.com/fuzzysteve/eve-googledocs-script/issues/5

Market spreadsheets in space

Eve is sometimes known by another name : Spreadsheets in space.

I have one spreadsheet in particular that is 'way too big', yet at the same time I want more.

I use this spreadsheet to primarily work out what is not being sold by anyone, then provide a suggestion as to where I might buy it in turn.

Screenshot of spreadsheet

 
It shows the buy/sell price in 5 trade hub systems (Jita, Amarr, Dodixie, Hek, Rens), and 1 region (The Forge).  It then shows the full set of data as available from the Eve Central API for yet another system.  I have chosen Sujarento, Sugar Kyle's hub

The item choices are based on what can be used (rather than items consumed in manufacture).  It (tries to) list T1, best named meta, and T2 modules, plus faction ammo.  The item list is a little out of date, and is missing the most recent changes.  I used the static data (in turn downloaded from https://www.fuzzwork.co.uk/dump/ ) and some SQL to build this list.

Use either Chrome or Firefox.  It will break the in game browser, Internet Explorer and occasionally Opera.

Restart your browser may help.  It takes minutes to load, and then I added additional delays to avoid google docs crashing.

When you first open it, it will complain that it is breaking, and then slowly populate data.  At first, some fields populate "#NAME?" and then "Loading..."  and then you wait a few minutes and data will start to appear in sets

I am also playing 'whack-a-mole' with google, working around limitations that google add to their services.  The latest of which is to add a 1 second delay to the googlescript, for every 50 prices in a 'set'.  Sometimes, it fails to load, even for me.  As such, the second tab is snapshot of values in case it chooses to decline to load.
 
Now that I have given the caveats, the spreadsheet itself is
https://docs.google.com/spreadsheets/d/1qL_ofk-QRQCFt2zKTTEXSi6oY4n1gtZX6Mojxxtgs-4/edit?usp=sharing

Please feel free to make a copy of this spreadsheet and tweak to your hearts content.

I will be watching for both comments and pageviews on this post.  If it is 'interesting' I intend to delve into some of the technicalities of how I put this together.

Edit: this is continued in http://foo-eve.blogspot.com/2015/03/google-docs-whack-mole-continued-plus.html

Single planet pi

I am reevaluating single planet PI. 

For the record, I don't like single planet PI.  It is wasteful of CPU and powergrid; either by running 2 extractor control units, or by running additional storage and swapping ECU between them.  It has also historically been a profit destroyer, that is it used to consume more ISK than it created. That is, previously you could sell the mats for more than the final product.
 http://foo-eve.blogspot.com.au/2014/02/single-planet-pi-manufacturing-bad.html presents some alternatives.

All of the above said, what single planet PI is good for is no fuss manufacture of P2 or P3 items.  Also, at the moment at least, conventional knowledge that single planet PI destroys value may be wrong.

I have created a page on my PI spreadsheet for single planet PI.

https://docs.google.com/spreadsheets/d/1lXM8PqU0Tn4EJJ2xRuI7uo55IrZ7MKWJidK9T_bA9L4/edit#gid=964439998

My spreadsheets are provided as is, where is.  Please note that this page is still considered 'draft' and may have errors.  But it has planet types, and what can be extracted and manufactured on a single planet.  As with most of my spreadsheets, it is based around 4% PI tax (being what a Foo corp charges by default).

Column A lists planet types and what can be made on your typical planet without importing. Column C lists the average sell prices across 4 trade hubs.  Columns D and E refer to the value made or destroyed by turning your items into the relevant P2 instead of selling parts.

For example a Barren planet Biocells manufacture on that screenshot (late January 2015) show that not only are the best 'average sell', but also have the best earnings over the component parts, stating that despite my reservations, maybe some forms of single planet PI may be profitable.    What is most profitable to make in the future will change.

Polytextiles are still truly aweful.  If you need polytextiles for other items, consider importing them and let someone else destroy their own ISK.

2 Extractor control heads still are very expensive (Power and CPU) to run.  I still don't like it, but can see value some pilots who are paying high import/output taxes, or who simply don't want to be in system or fiddle with their PI much.

Changing PI prices

I am a moderately happy PI farmer.

I have been sitting on stacks of PI scattered around highsec stations as various wormholes allow.  This is generally not something I would consider smart, but events have favoured me.

The following graphs PI prices over the last 3 months, taken from http://eve-marketdata.com

Autotrophs

Industrial Fibers

Polyaramids

Broadcast Node

High-tech Transimtters

Many PI products are showing a roughly 20% price increase since mid to late December.

I can think of 2 contributing factors to the upward pricing that occurred at this time: the jump (freighter) nerf limiting goods from nullsec; and the change to how google docs based price importing works breaking many spreadsheets.

I am somewhat pleasantly surprised as I expected  the lower costs in highsec with player owned customs offices enabling lower PI costs in highsec to counter this, but the graphs above (derived from prices in game) reflect reality rather than expectations.

Techical update on Google docs & Eve Central

Google docs and the Eve central API for data has not been playing nicely.

I wrote up my implementation of a solution based on Steve Ronuken's google script fix, that works moderately well.

https://docs.google.com/spreadsheets/d/1lXM8PqU0Tn4EJJ2xRuI7uo55IrZ7MKWJidK9T_bA9L4/edit#gid=0

http://foo-eve.blogspot.com.au/2014/12/pi-spreadsheet-working-again.html

The problem I had is that Google was caching data for far too long, and I missed some price changing trends.

The solution is to use a ?cachebuster=<value> parameter when you want a new call.

From the Eve Central API, apparently we are allowed to pull the same data every 5 minutes.  ( https://groups.google.com/forum/#!topic/eve-central/b5FjssiqFIQ ).

For my purposes, I don't care about completely current pricing, and price refreshing makes my spreadsheet unusable for about 30 seconds while it happens.  My current solution is to pass the day of the month as the cachebuster parameter, that is I request different data once per 24 hours.

I will be keeping a bit more of a closer eye on this as PI prices have moved around a bit over the last few weeks (post scheduled for Tuesday my time).

Packaged vs Unpackaged volumes

This is just a minor post about some spreadsheets about space work I have been doing in Eve, primarily regarding volume. If you are not involved in spreadsheets in space (either as spreadsheets or applications), please move along as there will be nothing to see.

In Eve, it is possible to have a 'packed' volume different to the 'unpacked' volume.  For most items these are the same.  For ships and containers at least, they are different.

After perusing the Eve static data, web searching, and finally asking Steve Ronuken, it appears that while the unpacked volumes are available in the static data, there is no available source of packed volumes apart from the Eve software itself.

There is a very old forum post with partially incorrect values.

So I have created a new google doc to share with my investigation : https://docs.google.com/spreadsheets/d/1hlng1FppLB40oACK76LpG9r-qZZORrFgqRTXognMlDw/edit#gid=0

This contains 4 tabs:

• Volumes by type id and type name for containers.  It appears that there is some groupings according to container size but even then there are exceptions
• Volumes by group id and group name for ships.  It appears that there are groupings according to ship class (frigate through super capital). 
• MySQL compatible sql to create tables with this data and then join it back to the eve static data
• Attributions

It is entirely possible that I have gotten items incorrect, and/or missed classes of items.  Eventually this data will become stale.

Regardless, I am hauling to null (not always personally), and this has attached a cost to it, I want to know if it is worth hauling completed items or hauling component parts.  Outside highsec, each trip's risk and cost are based on number of trips more than what is inside a hauler.   Of course, one day, one of our haulers will make someone a glorious killmail.

PI Spreadsheet working again

I use a google doc's spreadsheet for tracking PI prices, and what I should or should not be making.

My biggest personal need is to set prices for our in-wormhole PI buying program.

Google Docs spreadsheets have been upgraded lately, and importxml, which is the basis of how I have been pulling prices from eve-central is broken.  So my old PI spreadsheet, as well as the one Wyke Mossari are now both also broken.

However Steve Ronuken came to the rescue with some google docs scripts.  I have tweaked them to my desires and now have my new version.

Not only can I import data from Eve Central again, it is much faster, and I can now combine my buy and sell spreadsheets.

The spreadsheet is at https://docs.google.com/spreadsheets/d/1lXM8PqU0Tn4EJJ2xRuI7uo55IrZ7MKWJidK9T_bA9L4/edit?usp=sharing

You are welcome to make your own copy and tweak.

Main Tab

Main Tab

The first 'Main' tab lists the PI products, what level they are, and max buy/min sell prices from Eve Central for several systems. It also includes a few other items for POS fuel and WH gas clouds.


Columns P and Q have the average prices from Jita, Amarr, Dodixie and Hek.

If you make your own copy, you can change the system names in row 2 and everything else will work.

Make Import Tab

Make Import Tab

This tab assumes you have one PI item being manufactured, on planet and you are looking for something to pair it with.  The Earnings% column is a quick guide, colour coded.  Red is a loss; white, yellow, green in that order show what is worth making.  If you are color blind you are welcome to just use % values (or make a copy and colour it to your liking)

All values try to take into account the effects of import and export taxes, assuming you are making the value of 'Manufacture 1' on planet.

The tax rate by default is 4%, which is the standard in Foo corps.

Manufacture Tab

This tab assumes you have a pure factory planet, importing everything.  This also uses the tax rate from the Make Import Tab.

SelectedTypes Tab

This contains the Eve TypeId's, item names and a grouping code.  The TypeId's can be determined from various sources.

If you make a copy of the spreadsheet and change a TypeId then it will be reflected on the main tab.  At the moment the Items are hard coded.

If you have a specific item you want to find the type id, look for it's price on eve-central.  Eg search for Tritanium on there, and you will get https://eve-central.com/home/quicklook.html?typeid=34 as the URL.  This URL tells you that the typeid for Tritanium is 34

Various websites contain the static data from eve, including http://eve-files.com/chribba/typeid.txt

Buying Tab

This tab is what I use to run my wormhole buying program.  It will occasionally (often?) be filtered to the products that I have most recently purchased from someone.

Credits and License tab

This tab is where I have various credits to where I have used other people's work.

Caveat Emptor

I have taken some care, but take no responsibility for any ISK you may earn or lose as a result of using this spreadsheet


PS. Steve has also set up his own version of the PI manufacture calculator at  https://www.fuzzwork.co.uk/pi/

PPS.  To see the scripts used by my spreadsheet above, make a copy of the google doc spreadsheet, and  use Tools -> Script Editor.