Weekend Update

Crunched on some work this weekend.  Getting dangerously close to complete on the spreadsheet front, and pushed some updates out.  Just posting this as a quick update for those that may have missed them.

Crius Industry Tools for Google Drive - Update

I pushed some updates to the script this weekend.  This should address some bugs with the getPOS() routine, as well as some features that @CCP_Nullarbor added to the Teams CREST feeds.

• Fixed issue with getPOS(): Was trying to fetch location names with the wrong function call.  This issue has been resolved and all valid TQ corp keys should return good data now
• Fixed issue with getIndustryJobs(): Was not fetching personal jobs correctly.  Changed key validation to be correct now.  Can fetch corporate or personal jobs automatically off a single key/vcode
• Added teamName/activity to Teams and TeamsAuction feeds: Were "TODO" placeholders.  Now reports in-game names and valid activity information

Go fetch a fresh copy from either source, and continue reporting bugs

• Demo Sheet
• Github repo
• Bug Reports
• Use Guide

I still need to clean up some errors and hanging bits.  I'd like to push this tool to proper add-in status, but I am a little unclear on what to do between what I have now and what it takes to be considered "release ready".  Also, probably time to bite the bullet and buy some icon/banner designs.

More Crius Utilities for Devs

This one won't be as useful for the veteran 3rd party devs, but should be invaluable to the amateurs.  I put together some SQL queries and the resultant CSVs into the github.  If you're trying to update your spreadsheets with new data, these feeds should help you save a boatload of time.  If you're not sure what to do with the CSV files, I suggest you read up on pivot tables.

Let's Build a Spreadsheet Series

I did a pretty long set of streams on Sunday.  The goal was to help show off the spreadsheet fu and help people learn to build better spreadsheets with the tools at hand.  I will have the raw streams up on YouTube and Twitch tonight, but I want to boil down the lesson plans into 5-15min function lessons.  I still need to write up lesson plans and get my hands around video editing, but it's a goal for this August to put together a series that will help people build their own calculators.

Building Better Spreadsheets - Crius Toolset

Crius releases on Tuesday, and most industrialists are scrambling to replace their spreadsheets (I know I am).  What's worse, the job-cost equation requires live data from the game, which can be difficult for someone relying on spreadsheet feeds.  Fear not!  I have great news!

Crius Feeds In Google Spreadsheets

These functions let you read in the CREST/API feeds of common calls and import them directly into your spreadsheet.  Furthermore, the triggers are set up to automatically refresh the data periodically so your spreadsheet will always be up-to-date.  Though this is not an exhaustive API tool, and still could use some more features, it should be a huge leg up for any spreadsheet jockey.

Most of the feeds are designed to dump the entire feed, and don't offer much filtering in the call.  Instead, they were designed to be used in a reference sheet that could then be leveraged using VLOOKUP() or QUERY().  This might lead to some issues with complexity down the line, so I intend to eventually add some finer calls that will just return single-line kind of data.

Getting Started

Method 1: Clone the Master Spreadsheet

This will give you the tools and triggers, but will not stay up-to-date with the master sheet.  Until I can wrap up the code as a stand-alone Drive app, this will be the most stupid proof way to get a copy:

1. Open the spreadsheet
2. Go to File -> Make a Copy
   
3. Set the name of your copy (do not share with collaborators)
   
4. Remove any extra sheets/calls you need to and start developing your spreadsheet

This method is the easiest to start with, but has the issue that it will not keep current with updates.  

Method 2: Copy-Paste from Github

The codebase is free and open source, and is designed to be copy-pasted into the gdoc script interface.  This method is a little more tedious, but will be easy to copy updates as they come out.

1. Get plain-text code from the GitHub repo
2. In your spreadsheet, go to Tools -> Script Editor...
   
3. This opens a new window.  Select "Blank Project" from the initialization prompt
   
4. Copy the raw code into code.js space
   
5. Set the name of the project
6. Save the changes
7. Configure the app triggers.  Set get/All functions to 1hr timers
   
   

This will give you all the utilities in a fresh, or existing codebase.  Also, configuring the triggers appropriately will keep the data up-to-date automatically.  It's technically optional, but without time triggers, it will require a fresh open to pull fresh data.

Also, as updates come out, you'll be able to drop in the new code.  I expect to keep this project backwards compatible, so each drop in should ADD features.  Though, of course, if you go editing the code, you will need to be more careful about dropping in changes.  

Function List

• getPOS (keyID, vCode, header_bool, verbose_bool, test_server_boo l)
• getFacilities (keyID, vCode, header_bool, verbose_bool, test_server_bool )
• getIndustryJobs (keyID, vCode, header_bool, verbose_bool, test_server_bool )
• getAvgVolume (days, item_id, region_id )
• getVolumes (days, item_id, region_id )
• AllItemPrices (header_bool, test_server_bool )
• AllSystemIndexes (header_bool, test_server_bool )
• AllTeams (header_bool, verbose_bool, test_server_bool )
• AllAuctions (header_bool, verbose_bool, test_server_bool )

The functions are designed to be referenced as simply as possible.  CREST feeds like AllItemPrices and AllSystemIndexes can be referenced without arguments if desired.  Also, the classic API feeds are designed to return as much information as they can, with internal switches to try and use the /corp/Locations feeds if possible.  Also, most feeds come with a "verbose_bool" trigger to add/remove ugly or useless raw ID kind of data.  Lastly, the test_server_bool has been left in the release.  For TQ this value can either be blank or false.

Function guide below the cut

Can I Play With MA(C)Dness?

Blame this one entirely on @K162space.  He got me playing with Quantmod in R.  Also, shout out to CCP Quant for showing off the original bits, and CCP Foxfour for the CREST market history.

After all my EVE data experiments, I've had a very hard time finding predictive correlations between datasets.  Even trying to bring in destruction data showed no predictive properties, instead only showing correlation to corroborate market volumes.  Also, I've tried my best to get into Varakoh's analysis, but have never been able to internalize his methods enough to consider rolling it into my own analysis (also getting reliable candlesticks for EVE data is a pain)

Then Blake over at K162space.com sent me this tidbit:

Quantmod opened a whole new realm of tools I never knew about (full listing here).  The most interesting to me has been Moving Average Convergence Divergence (MACD).  I wouldn't be so bold as to say "use this to make money", but the results are extremely interesting when using IRL stock data.

INTC - YTD

For those that have never seen an MACD plot, the theory is relatively simple: Different line crosses are signals for buy/sell operations (specifics here and here).  Though the signals are imperfect, and will by no means catch every peak and valley, they can be an excellent sanity check for the longer trend.  For many IRL stocks these trends can have very strong correlation and are a popular tool among amateurs.  It is less useful for the minute-to-minute daytrading or arbitrage, but can be a powerful data source for the long-term investor.

Can this be useful for an EVE trader?  Well....

Let's look at a few plots:

PLEX - YTD - click to embiggen

Tritanium - YTD - click to embiggen

Phenolic Composites - YTD

I had to do a little fudging to get open/close bars to work correctly (though high=close=avg is roughly the same).  The trend information is interesting, but the crossover signals aren't lining up well.  Using the traditional 12,26,9 configuration, many of the crossover signals arrive 2-3 days late.  If you ended up using these charts as-is, I think you'd end up at best breaking even.  Though there are some secondary things you could do like buy and sell in the swings up and down, these charts aren't going to be immediately useful.

I then started playing with shorter windows, and pairing a fast and a slow chart might be a better way forward.  Unfortunately, I'm blindly fiddling knobs right now, but I'm actively hunting down documentation to better fine tune the charts.  I was thinking a 10-15d window might be more accurate, and a 25-30d window would serve well as a "slow" chart.

Also, I think the weakness has a bit to do with the quality of data here.  Where MACD is looking for close prices, we're feeding it daily averages.  This might be a good excuse to finally work on a better snapshot tool using eve-central data.  Though I had a lot of trouble processing down the raw archive, starting up a stupid-script that pulls periodically from the traditional API would be a quick solution that could start crunching in the background.  Once I can get my industry tools refactored, I expect to get back into the data mining game pretty seriously.

In the meantime, be sure to subscribe to my channel on Twitch, and follow on Twitter.  I will be doing some random streams over the next week as I get back into a reasonable routine again.