Station Trading Secrets: Importing Prices Via XML/XPath in Excel 2013

One of the most essential principles of any type of industry in Eve Online is the ability to efficiently gather, analyze, and present data. To an industrialist, the data that is at your disposal can mean the difference between a huge profit margin and becoming devastatingly space-poor. Therefore, a wise industrialist should always question the method in which he gathers and analyzes data in order to optimize the process; the less time you spend staring at a spreadsheet and manipulating numbers, the more time you spend in-game making money and the more ISK you make per hour. For station trading, this concept is especially true. The less time you spend searching for the best profit margins on the market, the more time you spend updating orders and making money.

For years, station traders have utilized Google Docs as their platform for data analysis, primarily due to the ImportXML function. This function allows industrialists to pull real-time information about items the Eve Online market through Eve-Central's API using XML and XPath, but it has its limits. Google only allows 50 ImportXML functions per workbook, and although you can get around this by concatenating multiple item ID numbers in a single function, you'll eventually run into performance issues as well (scrolling through the spreadsheet is sluggish, calculations are slow to return results, and it is overall generally frustrating to work with.)

The most recent version of Microsoft Office, Office 2013, introduces a new function into Excel while revamping an old one: the WebService and FilterXML functions, respectively. In previous versions of Excel, you were unable to pull XML data directly from the internet and filter it using XPath. Using the two functions above, you can now emulate the ImportXML function of Google Docs without the hassle of function and spreadsheet size limitations. Below is an example of one way to do it.


By being able to pull market data from Eve-Central's database directly to your computer, you essentially skip the middleman (that is, Google Docs) and rely entirely on the performance of your own computer and internet connection to do the work. Using Google Docs relies on Google's servers and internet connection in order to gather and analyze data (which isn't to say that Google has bad servers, it's just that their Google Docs web application does not handle large spreadsheets with multiple dependencies very well.) Therefore, if you have an efficient computer and a quality internet connection, you will find that you will spend less time waiting on data to be gathered/analyzed and more time using that data to your advantage. As a personal testament, within a minute, I am able to call up and calculate data based on both the maximum buy and minimum sell prices for over 6,000 individual items using Excel 2013. Doing the same process on Google Docs is unwieldy and slow, if not impossible to do in the first place.

Hopefully this helps a few fellow industrialists!

Pro tip: Control+Alt+F9 refreshes your entire Excel workbook and all of its dependencies. That means it queries for all prices and re-calculates all data.

If you need help creating a station trading spreadsheet or are interested in getting started with station trading, EveBusinessInsider has created a two-part tutorial here and here. He does a very good job of explaining the basics behind manipulating the market and how to maximize your profit margins.

Comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. How do you have it set up so you can pull 6000 items without getting isp banned?

    ReplyDelete
  3. Are you telling me that pulling 10000 items is going to get me isp banned?

    ReplyDelete

Post a Comment

Popular Posts