CSM 13 results

Evening All,

Just a few numbers out of the election results.

First off: The Judge is the beneficiary of Creecher’s disqualification. The results with him disqualified (so the ‘actual’ results. The number on the front is just an identifier. it doesn’t mean anything. I’m being lazy by not removing it.)

7 “Suitonia”
8 “Aryth”
22 “Jin’taan”
28 “Innominate”
31 “Steve Ronuken”
32 “Merkelchen”
38 “Sort Dragon”
41 “Brisc Rubal”
43 “The Judge”
44 “Killah Bee”

The result without Creecher’s removal:

7 “Suitonia”
8 “Aryth”
11 “Creecher Virpio”
22 “Jin’taan”
28 “Innominate”
31 “Steve Ronuken”
32 “Merkelchen”
38 “Sort Dragon”
41 “Brisc Rubal”
44 “Killah Bee”

Going into the audit log, we find that Aryth and Sort Dragon each got enough votes to get elected without any transfer. First stage transfer from Aryth was enough to put Merkelchen over the edge. Not quite enough to get Innominate above quota, but pushing him into the top four.

It takes quite a few eliminations before it’s more than those 3 elected. Down to 23 candidates remaining, before minor transfer from Aryth and Merkelchen get Jin’taan over the edge. (it’s like 57 votes. He’s received enough transfer from eliminated candidates to get him there)

By the time we hit 18 remaining, Jin’s in the top 3 candidates, getting elected without any transfer.

Down to 16 and Killah Bee is above quota after transfer from Sort Dragon.

Down to 13 candidates, and I get on by transfer and elimination. I pick up a few from people who voted for everyone above me.

Down to 12, and I have enough to get on by myself.

Total votes which are totally exhausted before anyone gets elected: 2325

This year, with only a single member, Aryth would be it.

2: Aryth and Jin’taan.
3: Aryth, Jin’taan and Sort Dragon
4: Aryth, Jin’taan, Sort Dragon and me.
5: Aryth, Jin’taan, Steve Ronuken, Merkelchen,Sort Dragon
6: Aryth, Jin’taan, Steve Ronuken, Merkelchen, Sort Dragon, Brisc Rubal
7: Suitonia, Aryth, Jin’taan, Steve Ronuken, Merkelchen, Sort Dragon, Brisc Rubal
8: Suitonia, Aryth, Jin’taan, Innominate, Steve Ronuken, Merkelchen, Sort Dragon, Killah Bee
9: Suitonia, Aryth, Jin’taan, Innominate, Steve Ronuken, Merkelchen, Sort Dragon, Brisc Rubal, Killah Bee
11: Suitonia, Aryth, Jin’taan, Innominate, Steve Ronuken, Merkelchen, Sort Dragon, Tikktokk Tokkzikk, Brisc Rubal, The Judge, Killah Bee
12: Suitonia, Aryth, ExookiZ, Jin’taan, Innominate, Steve Ronuken, Merkelchen, Sort Dragon, Tikktokk Tokkzikk, Brisc Rubal, The Judge, Killah Bee

(I’m disappointed. Last year it would have been me in a single person CSM 😉

Getting all market aggregates from Market.fuzzwork.co.uk – Excel

This is for Excel only I’m afraid. And only 2013 and up. Google Sheets just won’t load the size of file that this uses.

On https://market.fuzzwork.co.uk I generate aggregate data for all items sold on the market. This is things like average price, minimum price, maximum price, and the average price if you were to buy the cheapest 5% of the market. (handy for evening out any outliers.) This is available via an api, which is documented on the site. There’s a function for google sheets, and instructions on how to use it with power query in Excel.

However, this isn’t wonderful if you want to get everything. To that end, I’ve started dumping out a file with all the data in it. This is updated once every 30 minutes (or so), and is about 24MB uncompressed. Google sheets chokes on it. Excel handles it ok though. So that’s what I’m going to talk about here. I use Excel 2016, so this is focused towards that. I think it’ll work with 2013, but I have no way to test that.

The URL for the data is https://market.fuzzwork.co.uk/aggregatecsv.csv.gz and is a gzipped csv. With headers. The first column is the region id, the typeid, and if it’s a buy or sell order.

To load this into Excel, create a new sheet. On that sheet, create a new query (Data ribbon, get data, from other sources, blank query). In the advanced editor, you want to paste in:
let

let
Source = Binary.Decompress(Web.Contents("http://market.fuzzwork.co.uk/aggregatecsv.csv.gz"),Compression.GZip),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"what", type text}, {"weightedaverage", type number}, {"maxval", type number}, {"minval", type number}, {"stddev", type number}, {"median", type number}, {"volume", type number}, {"numorders", Int64.Type}, {"fivepercent", type number}, {"orderSet", Int64.Type}})
in
#"Changed Type"

That should then load all the aggregate data into a sheet for you. Call that sheet something appropriate, like marketdata

Then to query it:

=VLOOKUP(“10000002|34|true”,marketdata!$A:$J,9,FALSE)

Referring to the sheet name you configured. you can assemble the lookup value as you want to. Region ID, typeID, if you want buy or sell orders. true for buy, false for sell.

Something like

=VLOOKUP(“10000002|”&A1&”|false”,marketdata!$A:$J,9,FALSE)

works. Where A1 contains the typeid you’re interested in.

Google Sheets – Updating from a Menu

Something which has been a problem people have run into, is that google sheets have a problem with automatically updating figures from custom functions. You’ll often find functions which suggest having a useless reference to a cell which you can then use to trigger the updates.

This is obviously somewhat suboptimal. So as a potential solution for you good people, I give you the menu based price list.

Google Sheets allow you to have custom functions, but you can’t write from them, only return data. However, app script called from a menu option can write to its hearts desire. The downside is that you can’t pass it any parameters. So this means you must have the input data stored somewhere it can retrieve by name, and somewhere to output; named range is one option, but I’m taking the lazy option and using sheets with specific names.

So, with the link above, you can see how to do it. There’s an example sheet you can copy.

As a little explanation:

The onOpen function adds menu in. It should be possible to update the data from here, but I ran into a bug there, so it’s purely on refresh.

 

The updatePrices function is pretty much the same as the one for use with a custom function, with a couple of key differences. First, you get the two sheets which are important. Clear the output sheet so we just append to it.

Get the region id from the cell A1 of the typeid sheet.

Now it just runs through the first column, from row 2 (so it ignores the region) to the last row. Deduping the list comes next. Sure you could get the data twice, but this is more efficient.

Then it runs through the typelist 100 ids at a time, getting the json, running through it, and appending the results to the sheet.

Memoirs: Death

For most capsuleers, Death is a constant companion. If we’re not dying, we’re killing. If we’re not killing, we’re trading in the implements of death. If we’re not trading, we’re manufacturing new and improved ways of killing people.

Funny occupations for someone who’s functionally immortal, don’t you think?

I guess there’s some truth to the common perception that capsuleers are sociopaths who don’t care about baseliners. I mean I’ve personally caused the deaths of tens of thousands, if not hundreds. I don’t think I’ve hit the millions, but that may be wishful thinking on my part. Of course, they all had it coming (Except possibly my crew. But they’re well compensated). You take the coin from a pirate, you trade in slaves, and you give up any rights you have to life.

These days, I tend to keep a few steps removed from it all. A Merchant of Death, rather than a deathdealer. Are my hands any cleaner? Probably not. Still doesn’t interrupt my sleep. Only thing which does, is thoughts of my own death. Like with the H4-RP4 Kyonoke outbreak. Sure I had a backup, but that’s not the same. At least not for this me.

I guess it comes down to keeping your friends close, but your enemies closer. Death’s the greatest fear of a capsuleer. True Death, that is. So we flirt with it. Hold it close, wear it like a cloak, spreading it where ever we go. There are times that I think on the religions of my childhood, and fear the day it all comes crashing down.

Memoirs: Kyonoke Inquest

The inquest at the H4-RP4 facility. Kyonoke. That wasn’t a good time. I couldn’t not go, not when I received the invitation. If I hadn’t received one, I’d have kicked up whatever trouble I could, until I got one; But I have to admit, I was still terrified. I mean, I live in a bubble. My immune system isn’t the best, even against normal baseliner disease. And yes, I probably could have jumped out if I were infected, as long as it hadn’t progressed too far. Or reverted to a backup.

Backups aren’t something I like to talk about. Feels too much like dying. No “explode, then open my eyes elsewhere.” Knowing that another you went on, lived life, then died, before you you woke up, without any memories of that time. Thankfully, up to that point, I’d never had to use one. So yeah, terrified. But not quite enough to stop me going. I may not be the best Son of Matar out there but when my people are suffering and I have a chance to use what influence I have to help them, or at least stop it spreading to others, I didn’t really have a choice.

So with my backup up to date, and a clone body prepared, I set out. Preparations were fairly minimal. Embedded recording system. Best immune system I could get into it in the time I had. Features structured to gain some respect. A beard; greying of course, to simulate a patriarchal feel. A paunch, for prosperity, and to be less threatening. Tall, but not too tall. That kind of thing.

Other than a Cheetah, a fairly barebones crew on it, and a hold-out, that was me. Oh, and the research staff back home. I can’t provide the same resources as a State, but I’m not without some resources.

Using ESI with Google Sheets

I’ve been meaning to write this post for a while now, so here it finally is; How to use ESI, with google sheets.

Now, the unauthenticated endpoints are relatively easy. You’ll still need to write a little code for them, or steal it from somewhere else, but other than that, it should just work. Authenticated endpoints are another matter.

ESI uses Eve’s SSO for authentication, which means, at some point during the setup, you’ll need to handle the OAuth flow to get what’s known as a refresh token. This token is much like an XML key from the older API, allowing you to access the endpoint without any human intervention. (It’s actually used to get the access token, which allows you to do things for about 20 minutes. The refresh token can be used at will to create new access tokens)

Setup

Due to the way it works, this means you’re going to have to go to the developers site, and create a new application. Each application has a client id, and a secret, and that secret should be held closely and not revealed to the general public. So you need to create your own.

So, go to the site, log in, and click on ‘application’ up at the top. You can then click on ‘create new application’.

Give it a name and a description. I’d suggest something meaningful to you for the name, but the description isn’t important; you just need something there. Pick ‘Authentication & API Access‘ as the type.

Now you need permissions. These are what are known as ‘scopes’ in OAuth. They’re like the various things you can pick in the XML api, allowing access to various different things. What scopes you pick, is dependent on what you actually want to do with the login. When actually doing the login, you tell it which scopes you want. As long as they exist in the application definition you’re filling in now, you can get them. You don’t need to ask for them all.

For this example, I’m asking for the esi-universe.read_structures.v1 and esi-markets.structure_markets.v1 scopes. This allows me to pull the market data from a structure I have access to (and get its name). Just click them, and they should move over to the box on the right.

Now comes the ‘complicated’ bit. The callback url. When you use OAuth, you get sent to the login server, log in there, then get redirected back to the source. The callback url is where you’re sent. As you don’t have a webserver for this (probably) we’re going to use a tool called Postman to handle this step.

Fill in https://www.getpostman.com/oauth2/callback as the callback url. Click on ‘create new application’.

Finally, click on ‘view application’ next to your newly created one.

Postman

Postman is a chrome app which is designed for working with APIs. We’ll be using it to handle the initial authentication. Get it from its site. Once you’ve installed it, run it.

It should look something like this. On the authorization tab, change the drop down to ‘OAuth 2.0’. Then click the ‘get new access token’ button.

Auth URL: https://login.eveonline.com/oauth/authorize

Access Token URL: https://login.eveonline.com/oauth/token

Client ID: The ID from the application page you just created

Client Secret: the Secret Key from the application page you just created

Scope: the scopes you want, separated by spaces. So in this case:

esi-universe.read_structures.v1 esi-markets.structure_markets.v1

Finally, hit ‘request token’. This should send you to the login page for Eve. If it doesn’t work, make sure you have no extra spaces at the beginning or end of the various options.

Once that’s done, and you’re authorized, you should see a token in the existing tokens box.

This lists your access token (you can ignore this, tbh. you’ll be getting a new one.), and your refresh token (this is important, so take a note of it, along with your client id and your secret.)

That’s you finished with Postman, until you want a new token for something else.

Google Sheets

Google Sheets doesn’t handle JSON or OAuth natively. This is a bit of a pain, but the ability to add custom functions takes care of that nicely. Especially when someone else writes them for you 😉

For this example, we’ll create a new workbook.

On your new workbook, create a new sheet and call it config. Put the client id, the secret, and the refresh token into separate cells on it.

On the Data menu, pick ‘Named Ranges…’. This will open a sidebar. select the clientid cell, and click ‘add a range’. call it clientid, and hit done. Repeat with the secret, and the refresh token, calling them ‘secret’ and ‘refresh’.

you can then close the sidebar. This is just to make it easy to refer to them later.

Now, click on Tools->Script editor. This will open a new window. switch to it.

Delete the contents of Code.gs (it should be an example myFunction only. if not, you’re in uncharted waters)

Paste in the functions from my Github. I’d explain them all, but that would massively balloon this post, and you’d need to understand Javascript. (They’re relatively simple)

Finally, on a sheet without data you care about (as this is a new workbook, sheet 1 works) use

=getCitadel(1023164547009)

 

and it should (eventually) populate with all the orders from that citadel. get the citadel id from a bookmark, asset id, or market order.

 

I need to do more work on this, as it’s not storing the access token anywhere (there’s an issue with the sheet doing updates on cells from custom functions)

Ore compression calculator

I’ve updated the compression calculator with a couple of new features, and stomped out a bug.

The bug was it had the wrong volume for some ores. (oops. it was hooked up to an old database.) That’s now sorted, and hooked up to the DB which updates when a new version comes out.

New Features:

Output values: You are told how many of each mineral you should get out in the end.

Ore selection: you can choose between all ores (the default) and highsec ores only.

 

Are other options for ore selection useful to you? Would you like to be able to specify nullsec only? Lowsec only? Be able to pick and choose, with max limits for certain kinds? So only 15 compressed spod, no arkanor but as much of others you want? Presets are simple from an interface perspective, full customization is somewhat more painful.

The backend, now that I’ve re-familiarized myself with it, is simple enough to update.

As always, the code is on my github respository and is can be freely used. However, it does require an external third-party library, which makes it a bit more painful to install. Especially if you’re on shared hosting (probably impossible then)

EDIT:

Nullsec only, and lowsec/nullsec (for the rig) filter added.

Bug stomped wrt Dark Ochre

CSM voting suggestions

I am, of course, suggesting that you put me (Steve Ronuken) first on your ballot. I want to be elected after all 😉

I would recommend you read the campaign posts, maybe do a little more research.

 

So, onto other candidates to consider:

Third party Candidates:

Highsec Candidates:

Looks like we’ve got people forming a part this time. Which is good. Gives them a chance to share the votes and get at least one of them elected.

Incumbents:

I’ve worked with these people before. They’re competent. (Don’t take not being here as not being competent. I just don’t want to overload people.) They’re less likely to need your vote, but it’s not a bad idea putting them further down.

 

Anyway, Vote when it opens. I’d recommend putting multiple people on your ballot (me at the top 😉 ) That way, you get the most value out of your vote. See a previous post on how the system works.

CSM Stuff – The MER changes

If you’ve been paying attention since the second CSM summit happened, you’ll probably have notice that there’s been some controversy kicked up by a change which is planned for the MER (Monthly Economic Report)

The short version is: Aryth asked for the figures to stop being on a regional basis, out in nullsec, because it exposed information which isn’t available anywhere else; the information being useful for keeping an eye on what people are doing, in a way that can’t be countered.

For a real world parallel, in war-time, people don’t publish their GDP figures.

Was this a self-interest request from Aryth? Yes, of course it was. But just because the person making it has their own interests in mind, doesn’t mean it’s inherently wrong.

When it came up, it was at the tail end of a session, and not extensively discussed. Aryth just asked that it be considered by CCP, and suggested that the data be presented in quadrants for nullsec, rather than regions. Lowering the granularity, rather than total removal.

I fully admit, when this came up, I didn’t put in any arguments against it. I can fully understand where Aryth was coming from, and I was working on the idea that it’d be reduced granularity, rather than total removal. Still allowing for trend analysis, but not letting specific groups be targeted. (Yes, I know there are sub-regional groups out there.)

I didn’t expect the level of backlash against it. (I did expect some. This is eve. Change the color of a couch and you get people saying the sky is falling.)

I have passed along to CCP that people have concerns about this. I’ve probably been a little more combative than I should have been, when asking people why they wanted the data. The why is important, because “Because I want it!” is a very bad argument. As is “Because we used to get it.” (Remember, it’s not me you have to convince. It’s CCP. Who have accepted that Aryth’s request has merit.)

I’m not ignoring you. I have passed on concerns to CCP. What I can’t do is tell you “Things won’t change.” Because the CSM cannot tell CCP what to do. Not one member, not the whole council. All we can do is collate and present the arguments.

CSM Stuff – The Voting System

You’ll have seen (probably) that the elections for CSM 12 are coming up in the near future (Voting starts on the 6th of March). I’m not going to go into details for what the CSM is (There are resources out there.), or who to vote for (Me, obviously 😉 )

What this post is about is the voting system itself. The elections use what’s known as the Wright Voting System. The key part of the system is that you don’t vote for a single candidate; instead you vote for a number of candidates, in the order you’d like to see them elected.

If your first candidate doesn’t get enough votes to get elected (we’ll get to this), then your entire vote gets transferred to the next on the list. If they get more votes than they need, then part of your vote transfers down. So if they get double the votes, then 50% of your vote transfers (and 50% of everyone else’s who voted for them. All going to the next person who voted)

There’s a quota to get elected. This is the total number of voters, divided by the number of seats plus one, plus one. So if you had 20,000 people voting, and 10 seats, that’s (20000/11)+1 or 1819 votes.

This does make things easier for large blocks, such as Goons, as they don’t need to make sure they split up their votes to make sure they get two people. You can just have people vote a set ballot, and let the system take care of it. If you have enough votes, they just transfer over.

It also makes things easier for more marginal candidates. If they arrange between themselves to have people who support them support someone else too, then the votes aren’t going to be totally wasted.

Here’s the key part: If there aren’t enough people hitting quota in the first round, you knock off the person with the fewest votes, transfer all the votes to the next person on the ballot, and start over. If there were people who only voted for the eliminated person, their vote is ignored, and the quota is recalculated, so you have to hit a slightly lower number.

Ideal world: you vote for the people you’d like to see on the CSM, in the order you’d like to see them elected. If lots of people agree with you, then your vote will help multiple people get elected. If a smaller number (but still enough) agree, then your vote gets one person on.

An example would be: There are five wormhole candidates. None of them would attract the votes to get elected. But if they all have everyone voting for them also vote for the others (in whatever their order they like) then whoever gets the fewest gets eliminated, and the votes redistributed. If there are enough votes to elect 2, and they all vote some combination of the list, then there are 2 wormhole CSM. It handles the organisation for you. 😀

This is a total pain in the ass to do manually. Thankfully, there’s no need to do so. There’s software to do it for you. CCP have made it available. This is how the election for CSM 11 would work out, with 10 places available. It’s the full audit log, so you can see how each round works out. (I’ve pre-eliminated Apothne, which makes a few differences.)

The only way to not be represented, with a STV system, is to not vote. (Or to be so marginal that there aren’t enough other people who think like you)