Monthly Archives: June 2013

Importing price data into spreadsheets

Importing up to date price data into your spreadsheets is a large part of being a successful manufacturer or trader in Eve online. Some bypass it by using tools, but the tolls will still have to do it.

I’ve posted about how to load the data from eve central into a web page, but I thought it was time to talk about how to do it, into a couple of different spreadsheet  packages. Namely Excel and Google’s spreadsheet. Open Office is quite a bit harder to do XML imports with, so I’m just mentioning one option right down at the bottom, which is a lot less flexible.

Your first choice is the source of your data. There are two main sources, Eve-central, and eve-marketdata. They’re pulling data from pretty much the same source, so there’s not a lot in it.

Eve Central’s API

This is fairly well documented at http://dev.eve-central.com/evec-api/start. The short version is:

  • Create a URL to pull the data you want.

This will look something like: http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002

If you want more types, just add them to the url with &typeid=36 etc. The numbers are the typeIDs from invTypes.   Just grab an up-to-date copy from my data dump conversions when you can’t find something. It’s not a bad idea to include this, or a cut down copy consisting of typeid,typename,typeid in your spreadsheet on a separate worksheet, for use in vlookups.

Eve Market Data’s API

I’d recommend this one if you’re going to be pulling a lot of data, as it’s possible to pull the entire market in one go, for a particular station or region. Documentation can be found on their site

The url will look like this: http://api.eve-marketdata.com/api/item_prices2.xml?char_name=demo&type_ids=34,12068&region_ids=10000002&buysell=s

Just add the typeids to the comma separated list. Or remove &typeid=[stuff] from it, if you want everything. Change the character name to your own.

For Both:

If you want the station IDs: https://www.fuzzwork.co.uk/dump/latest/staStations.xls.bz2
If you want system IDs: https://www.fuzzwork.co.uk/dump/latest/mapSolarSystems.xls.bz2
If you want the region IDs: https://www.fuzzwork.co.uk/dump/latest/mapRegions.xls.bz2
If you want the typeids: https://www.fuzzwork.co.uk/dump/latest/invTypes.xls.bz2 or http://www.fuzzwork.co.uk/resources/typeids.csv

 

Excel

Be glad of heart, for Microsoft made this really simple. All of this applies to Excel 2007 and above. My screenshots are from Excel 2013 (Office 365 home premium is pretty good.)

On the Data tab

data

Pick ‘From Web’

import

Fill in the url you have, into the Address bar. Hit go. When it’s loaded, it’ll look like this. Hit import.

query

Just hit OK to this. It’s not important.

where

I normally put it into a new worksheet, to keep it out the way.

Once it’s loaded in, you can update the data by hitting the ‘refresh all’ option on the data tab. It /should/ refresh when it’s opened.

You now have market data in your workbook, that you can get up with a vlookup. See later for an example. You can download the example workbook here.

Google Documents

I’m not going to go into screenshots. You can find an example of how it’s done here. I thought this was probably the base way to do it.

Short version is:

Get the url, and use importXML() with it.

With Eve market data I suggest, instead, using importdata(), and the text format. It makes it a /lot/ easier to get everything you want out of it. The url is almost the same, except it’s txt, rather than url. you then split it on tabs ( char(9) )

Be aware, google isn’t great when you’re working with a lot of data. A desktop spreadsheet package works a lot more smoothly. Google will run into processing limits pretty quickly.

Vlookups

 

Learn to love this function. It’s very very useful for what you’ll be doing.

=vlookup(what you want to search for, where you want to find it, which column to return,if it’s not ordered data put true here)

so you could use =VLOOKUP(34,Sheet2!D:AT,34) to get, from the excel sheet above, the percentile price for Tritanium (typeid 34)

And you could use use =vlookup(‘Tritanium’,typeids!B:C,2,true) to get the typeid for tritanium from a worksheet called typeids, with typeid,typename,typeid as columns.

 

Open Office/Libre Office

Doing an XMl load into open office is a complete PITA, involving a lot more work. There are no screenshots, as I don’t have it installed any more. What I’d suggest doing is using the text option from eve market data.

Use ‘Insert’->’Sheet from file’

When it asks for a file, give it the full url (like http://api.eve-marketdata.com/api/item_prices2.txt?char_name=demo®ion_ids=10000002&buysell=s )

Hit open and wait. It’ll take a while to work the first time. Eventually, it’ll pop up the text import screen. Make sure tab is selected. Hit ok. It’ll take a while before it’ll become responsive, but eventually ‘sheet1’ should show up in the from file box. Make sure the link checkbox is ticked. Hit ok. You now have a sheet that should reload whenever the workbook is opened. It can be vlookuped in the same way as everything else.

 

 

T1 Industrials and the value of skills

T1 Industrials are in the process of being rebalanced, and this is leading to significant discussion on the forums.

The root cause of this trouble, other than the Mammoth/Hoarder issue, is that some races have more Industrials than others. So either some skills open up fewer ship roles, or some ships are, still, pretty much useless.

What I’d suggest is that it’s fine to have some skills providing access to fewer ships, as long as those ships are more generally useful, than the ships which are opened up by another skill. That doesn’t mean that the ships are all round better, however.

What I’d suggest is that the Amarr and the Caldari, with 2 ships each, get:

  • one with the highest general cargo bay (40k m3 or so, after expansion)
  • one with a good tank (around 30-40k ehp seems reasonable.) Few lows, lots of mids. The Amarr armour tanking makes this troublesome.

The Minmatar should have:

  • one fairly large one (38k or so)
  • one tanky one (25k or so)
  • one fast aligning/high speed one.

The Gallente, with their annoyingly large range, should no longer have the largest general cargo bay (sorry Itty V) But they should have:

  • A Gas specific one. 50k gas bay
  • An Ore specific one. Maybe minerals too. 50k Ore bay (Itty 5 here?)
  • A Fleet support one. 5K Fleet hangar and refitting.
  • One not quite as large as the big Minmatar one (around 34k)
  • One not quite as tanky one. (20k or so)

 

That way, there’s a reason to get any of the skills (Amarr and Caldari are duplicates, so you wouldn’t get /both/, but they’re still useful) with best of breed in each. No skill is ‘useless’.

Probing – Now and the Future

This is mostly prompted by a comment on twitter, about the player skill levels involved in probing. 140 characters isn’t enough, much of the time. To be clear, I’m talking primarily about site probing, rather than combat probing, which requires a slightly different method.

Now

I generally like the changes which have been made to probing. The primary changes are:

Launch all your probes at once

Sure, some people don’t like this, but it just wasn’t fun, sitting there and manually cycling your probe launcher. It added nothing except a minor annoying time sink. The only downside to this is for combat probing, as the victims have less time to catch the ship of the person launching them, before they cloak.

Auto recall on probes

As someone doing exploration, it’s all good. As a manufacturer, this is a little annoying. That’s the one downside. The probe market will have to adjust to fewer lost probes, so being smaller. However, RSS probes should become more popular. (due to not losing them any more)

Probe Formations

Oh god yes. This is probably the bit where people are saying ‘No skill any more’. The only real differences between now and the way it used to be is: No tedious moving probes into formation, and no needing to hold shift, resize probes, hold alt, move probes. It’s just move and resize in one action. Sure, it’s easier, but it’s hardly different skill wise. Just less tedious.

Notification of signatures, without needing to scan

Again, not a skill change. Just a tedium removal. No more ‘launch probes, shift to cover everywhere a signature might be, scan, get nothing, next system, rinse, repeat.’

8 probes

This does make life easier. It’s the one thing I’m less sure about. You’ll get better signature resolution, because you have more probes.

 

All that’s really been removed are the fiddly tedious bits. Localizing signatures takes all the same steps, with just a slight reduction in time required. The skills to do that, haven’t changed.

 

The Future

It’s all been a very good start. Some tweaking is required, however, to come close to ‘perfect’

Probe strength vs skill bonus

With the 8 probes, people with low skills just got a bump to their scanning ability. I’d suggest dropping the probe strength a bit, but increasing the bonus from Astrometrics.

Probe Groups

Right now, all your probes are treated as a single group. resize the group, and you resize all your probes. Ideally, with custom formations, you’ll be able to deal with groups. Say, 4 and 4, so you can resize just four, while leaving the other 4 to localise a different signature.

Custom Formations

Exactly how these would be defined would be interesting. I’d suggest leaving it entirely out of game, with an XML import. Doing it in game is a bunch of work, for a limited market, as the two basic ones are enough for the casual prober.

I’d suggest a format based on 3 coordinates per probe, with 0,0,0 being the centre of the formation, and the units used being in probe scan radius. So if you resize it, it doesn’t matter, everything keeping the same formation, at 0.25 au or 32 au. Leave the interface to create the formations to third party developers. It could be done fairly easily in a web site, using something like WebGL. And an XML file is simple to move around. Like overview files.

Single probe launch

For most of us, launching all the probes at once makes sense. But there are situations you may wish to launch them one at a time. So a toggle, or slider for ‘how many to launch’ makes sense. Stick it on a ‘advanced’ tab, to keep it out of our way.

 

With all of that in place, probing is pretty close to perfect. If I’ve missed something you consider important, give me a yell and we can discuss it. I’m always interested to hear about edge cases.