How to make an orcsports spreadsheet

In this forum, some different strategies of OrcSports are brought up in articles and/or discussions.

How to make an orcsports spreadsheet

Postby tomchaps » Mon Jan 15, 2007 5:54 pm

In the interests of improving the level of play, I'm offering to email anyone a copy of the spreadsheet that the now-retired Cazior and I have been using to help with our drafts. If you're interested, send me an email at tomchaps@yahoo.com, and I'll send it to you.

This thread will help explain how to use it. It takes a bit of work at first, but eventually you should be able to get an entire homeland into the spreadsheet in less than ten minutes.

Important! I've been playing orcsports less, and have NOT updated the spreadsheet to automate clans. It should be an easy fix, but I have not done it yet. In the version I'll email out, I just did it by hand--sort by clan, add one to the entire clan's relevant stat. The leafy unclanned orcs get hurt a bit by this, since I don't take into account the hidden minus-stat the other orcs get, so keep that in mind.

I'm presuming you know the basics of Excel--most importantly, how to sort.

Before showing you how to import, here's what the spreadsheet does--it calculates the skill of each orc at each position, weighted by the numbers you assign in the yellow box at the top of the spreadsheet.

For example, in this league I gave points to goalies as follows:
Goalie level: 8
Eek: 7
Slam: 9
Get: 2
Lose: 1
Tough: 4

This happens to add up to 31; so, roughly, I value lose as 1/31st of a goalie's value, and slam 9/31st. The stat "Goalieness", or GNess, is calculated by multiplying these by the orcs stats (so, an eek 5 orc would get 7*5=35 points), adding them, and dividing by five times the sum of the multipliers (31, in this case.)

Wow, that was unclear. Basically, each orc is rated for GNess, DNess, WNess, CNess and Lness (Looteriness). A perfect orc in a novice league would approach a score of 1--in later leagues, scores over 1 are possible. What's nice is that you can adjust the numbers in the yellow window, and the spreadsheet automatically updates.

So, if you don't like my belief that Get is worth less than 1/20th of a wing's value, bump that number up, and resort!

In an attempt to find cheaper orcs, I recently added Goaliene$$, DNe$$, etc. This compares orcs to the average of all orcs in the homelands. (Technically, it's the square of the ratio of the orc's XNess to the average, divided by the square root of the orc's cost. Why? I'm not a statistician, but it seemed to give me decent results. I hope others can improve upon this...) This is still rough, but it's why there is an "Averages" line at the bottom of the spreadsheet.

---

Okay, here's how to import your homeland. First, erase Columns A through O from the rows below the heading. (Row 7 down, but make sure to keep room for the Averages line at the bottom. This spreadsheet was from a Season II homeland, which I believe has fewer orcs than a novice homeland, so you might need to bump the Averages line down.)

Next, go to the homelands page, and (in Explorer), go to View, then Source. You'll get a window full of garbage. Scroll down until you see the orcs names begin. It will look like this:

<tr><td><img src="images/smalltribe4.jpg" border=0><a href="showorc.php?id=1504">Agmirwax</a>&nbsp;<a style="text-decoration:none" href="javascript:void(0);" onMouseOver="overlib('<b>Goalie 1</b> Eek 4; Slam 4<br><b>Defense 2</b> Sneak 3; Pound 3<br><b>Wing 3</b> Get 3; Lose 5<br><b>Center 2</b> Tough 3')" onMouseOut="nd()" ><img src="images/stats.jpg" border=0></a></td><td>962</td></tr>

Yikes! Anyway, highlight this information for all orcs on the page--there's a bit of stuff at the bottom. Paste into Microsoft Word. Go to the next page in the homelands, and repeat, pasting in the same Word document.

Once you get all orcs, start with the Replace All (Control H) function, to cut all of that mess down into a row of numbers (and one name), separated by commas. So, looking at the example above, cut <tr><td><img src="images/smalltribe leaving the clan number, and replace with nothing. Then replace .jpg" border=0><a href="showorc.php?id= with a comma. At the end, you'll have a line like this:
4,1504,Agmirwax,1,4,4,2,3,3,3,3,5,2,3,962

[Note--if an orc is hurt, there will be some junk at the end of the line. Remove it by hand--there's never more than one or two.]

Next, in Word go to Table; Convert: Text-to-Table. Check "Separate Text at: Comma."

This will make you a nice table. Now paste this into a waste sheet in Excel (I use Sheet 2, tabbed at the bottom) in order to easily see the number of rows. Copy, and paste into Columns A-O of the main spreadsheet, Row 7 down. Again, be careful not to copy over the Averages line at the bottom.

Depending on the number of orcs in the homeland, the range of the Averages row might need to be adjusted. Again, this is just for my experiment with XNe$$, or orc value, which is FAR less important than the regular Xness rating...

Start sorting, and have fun!

I use Pick to list on which day I plan to bid on that orc. Pos is the position I plan for them to play, Bid my maximum bid, etc. When I'm feeling motivated, I use the Herald to put in which team got which orc.

Anyway, this is actually quite easy to do, and I hope others find it useful.

Warning: I fiddled with my ratings, so as not to give TOO much of my strategy away. And be warned that my rating for a novice homeland would look very different...

I'm sure many of you are better at Excel and at statistics: I'd love to swap ideas on how to improve this.
User avatar
tomchaps
Senior Overlord
 
Posts: 212
Joined: Wed Mar 01, 2006 11:49 am

Postby Bogdanovist » Mon Jan 15, 2007 8:23 pm

I'm sure the spreadsheet system I use is less sophisticated, but on the issue of clans I average out the unknown effects, so for the clan bonus instead of +2 they get (2*(5/6)+1*(1/6))=1.83. For all other stats except toughness they get -(1*(1/6))=-0.167. For toughness they get +(1*(1/6))=+0.167.

This is also not ideal, but IMHO is better than ignoring the clan negatives and possible Tgh bonus. It represents the best guess to their stats in a probabilistic sense.

I havn't got it automated for clans though either, it's a bit of a pain to do manually.
User avatar
Bogdanovist
Senior Overlord
 
Posts: 199
Joined: Mon Jun 19, 2006 3:05 am

Postby Mad Jackal » Mon Jan 15, 2007 8:36 pm

Good news. I think I have an "easy" solution to automate this for clans.

I 'll try to write the code tonight and test it, but I'm betting it'll be until tomorrow night before I can even attempt to get it done. I'll let you know.

I think I'll be able to do two versions. 1 with the add 1 to clan like Tomchaps uses and one with the +1.83 and -.167 etc. that Bog uses...

It'll be at least 4 new columns, but it should automate out "fine"...

And thanks Tomchap, this looks good so far.
Mad Jackal
Senior Overlord
 
Posts: 122
Joined: Wed Aug 09, 2006 9:29 pm

Postby tomchaps » Mon Jan 15, 2007 10:30 pm

Yep, that's roughly what I was planning to do, Bog, when I got around to it.

Wait, clans give a +2 bonus in the stat, not a +1?
User avatar
tomchaps
Senior Overlord
 
Posts: 212
Joined: Wed Mar 01, 2006 11:49 am

Postby Mad Jackal » Mon Jan 15, 2007 10:36 pm

tomchaps wrote:Yep, that's roughly what I was planning to do, Bog, when I got around to it.

Wait, clans give a +2 bonus in the stat, not a +1?


If I recall correctly.

If they are not the toughnes family. Then they give +2 to thier clan attribute and -1 to another..

If they are toughness family, then they give +1 to clan attribute and +1 toughness.

I would share my secret for knowing if an orc on your team is his clan's Toughness family, but then it wouldn't be a secret anymore. (and Besides I'm sure most of everyone knows already.)

edited because Bog was nice enough to correct me.
Last edited by Mad Jackal on Mon Jan 15, 2007 11:11 pm, edited 1 time in total.
Mad Jackal
Senior Overlord
 
Posts: 122
Joined: Wed Aug 09, 2006 9:29 pm

Postby Bogdanovist » Mon Jan 15, 2007 10:44 pm

Not quite. As far as I know (and I think this is from Ice, though I can't remember were the post was) it's +2 for the bonus stat and -1 for some other stat except in the case of the tgh family in which case it's +1 +1.

And yeah, I know how to work out if they are from the tgh family too :D
User avatar
Bogdanovist
Senior Overlord
 
Posts: 199
Joined: Mon Jun 19, 2006 3:05 am

Postby Mad Jackal » Mon Jan 15, 2007 11:02 pm

This is the thread.

http://www.orcsports.com/forum/viewtopic.php?t=414&start=0

that I know of.

so it is +2/-1. and +1/+1.

That is good to know.
Mad Jackal
Senior Overlord
 
Posts: 122
Joined: Wed Aug 09, 2006 9:29 pm


Return to Strategy Articles and Talk

Who is online

Users browsing this forum: No registered users and 1 guest

cron