Translate this page

Some beautiful music to read the blog with

Sunday, 22 September 2013

Automatic Jita prices in my spreadsheets

In the comments to my last post "Started in Manufacturing" Steve Anderson, author of the site https://www.fuzzwork.co.uk/ opened my eyes to taking data off the internet into excel.

Currently, i have one spreadsheet for my trader in Dodixie which has the price of an item to sell in Dodoxie and the price of that item selling in Jita, and compares the two to see the profit, as shown below:


In the above table i am able to sell the Amarr Fuel Block Blueprint for 12.5m ISK in Dodixie and it sells for 12.0m ISK in Jita, allowing me to make a profit before fees / taxes / hauling costs of 500k ISK, or a margin of 4% - not very good.  The Arbitrator Blueprint though is much better . . . . . but sells very slowly!  And the spreadsheet goes on for many rows and there is another one for Hek.

As an aside, i also have a column indicating how many items trade each day.

The spreadsheet allows me to input the number to buy in Jita and so calculates the cost of my purchases and the expected profits to be made after all costs.  Another part of the spreadsheet then calculates the hauling cost and so showing me my total expected profits - most recent summary is below:

 
In the above example i was hauling 2.6bn ISK of items to make sales of 3.1bn leaving me with profits of 381m ISK after all costs - not a big haul for me and indeed no skill books were being hauled to Dodixie that day either.

The items on each spreadsheet are also on my quickbars.

Up until now i would manually type in all the Dodixie/Hek and Jita prices for items that i had completely sold out of in Dodixie/Hek - which was somewhat boring.

I was aware that Eve-Central could display the data but i was not sure how to access this on my spreadsheets (Excel 2007).

The data on Eve-central is only as good as the players contributing - so i suspect very timely and accurate for Jita but perhaps not as good elsewhere.  I don't know if the data will be timely enough to get accurate prices for Dodixie and Hek.

But now my eyes have been opened to getting the Jita prices at least onto my spreadsheet.

One of Steve's posts goes through importing XML data to allow Jita prices from Eve-Central to appear on my spreadsheet (at least that is what i think i happening).  Steve provides an example spreadsheet which i used and added to.

Hence, i now only have to update my prices for Dodixie and Hek - a big time saving on my daily routine!  The Jita prices now come automatically from Eve-Central to my spreadsheets.

And the Jita data has also been added to my manufacturing spreadsheets.

5 comments:

  1. I prefer google docs to microsoft... the tools are stronger IMO and it's way easier to share with groups.

    If you're interested in learning, I wrote up a tutorial here: Google Docs Spreadsheets Tutorial

    Also, I have tutorials about Invention Mechanics and Using Decryptors. Welcome to manufacturing

    ReplyDelete
    Replies
    1. Google docs have a couple of downsides.
      The xml import isn't so good when you want multiple attributes.
      There are more processing limits.

      Otherwise, yup, you're entirely right. :)

      Delete
    2. You give a little, you get a little. That gdoc comes with imbedded SQL-like query(), as well as common script commands like unique(), spit(), and regex() is reason enough to win me over for quickly crunching data.

      Nothing beats custom code and a real database, but when you need a stable prototype, I've found gdoc to deliver in spades.

      Also... fuck microsoft. There are very few things that excel does better/faster/easier than gdoc, in my humble opinion.

      Delete
  2. With google docs you can always write your own custom javascript based functions to pull data. Additionally, you can write quite powerful custom spreadsheet functions in the same manner.

    I ended up pulling data from eve-marketdata in csv format but you can aslo easily parse xml from eve-marketdata or eve-central. In fact there's a library of functions available from the script gallery to pull from eve-central.

    ReplyDelete
  3. Great post, thank you.
    For station trade use http://www.evetrader.info/jita-trade.html

    ReplyDelete

Note: only a member of this blog may post a comment.