A blog by an historian, Pagan and fanfiction writer, with left-wing leaning politics. In short, I could be waffling on about anything.

Saturday, 31 December 2011

Happy New Year! 2011 in Review

Greetings to all at the end of 2011; let's make 2012 a brilliant new year. It might just be the turning of a calendar, but it's also an opportunity to draw a line and start again. We can start building on the past - taking the best of it and learning from the rest - and we can use that as the foundation stones for the rest of our lives. Bloody fantastic!

So my year in review. OMG where do I start?

This time last year, I was in a stable job that I enjoyed. Business was booming and I was being told that we'd probably even continue going through the hurricane season. Traditionally, we paused then for a couple of months, this being Mexico and a company facilitating tourism.

But it was a tourism that relied way too heavily on visiting Americans; and that was not something that either the American press nor their government really enjoyed. Not when there was similar weather and beaches down in Florida. The War on Drugs destroyed the business two-fold.

First there were all of the scare stories in the mainstream US press. They didn't take into account that the problems were solely in 4% of the country. To read those lurid headlines, millions were dying all over the country. Moreover, they were Americans! My Mexican friends responded in despair, but what could they do. They were living in absolute peace, never having seen a single murder, let alone a gang massacre, in places where Fox News was telling its viewers there were bloodbaths. The business started to waver.

Secondly President Calderon was promised money and support, if he'd just initiate a war on drugs in his country. It would be good for PR in Washington. But none of that was forthcoming. Is Calderon winning the war on drugs? Kingpins were taken out, the drug families responded in kind; and all of those previous inaccurate press stories started to take on a semblance of truth. The unwinnable war did spread, but still Cancun was safe. It didn't matter. No-one was coming and so the company went under.

It took until the end of July before we were all dismissed. It wasn't just me. The entire company went under and we were all back in the dole queues again.

I'd been there before and I didn't want to do it again. It's not snobbery. It's wishing to avoid the slow, drip-drip erosion of mind and soul, which comes from being told each fortnight that you're nothing, no good and probably on the fiddle. Finding a job in recession hit Britain is like pinning your future hopes on winning the lottery.

You quickly learn that no-one at the Job Centre actually wants you to find a job. It'll put them out of business for a start. There's all that lip service, usefully delivered in an accusing tone with a stern face. You're not looking hard enough. You're not enough. But there's the little things that give them away, like the time they told me to move a job interview because it clashed with signing on. Like the times (note the plural) that they've told me to take my degrees off my CV and never mention them to prospective employers. Like the fact that, as an hearing impaired woman, I'm handed over to a 'special needs' counsellor, who sits in a noisy open office. Like the severe pressure that I'm under never to write on the web.

I looked at my options and I looked at my savings. I decided that I had time to try and make it as a freelance writer. Let's just summarise here as the experiment nearly paid off. At the beginning of October, I thought I was going to be able to make my living reasonably soon; then Google took out my most lucrative site and it felt like it was back to square one. I was still living mostly on my savings.

In normal circumstances, I could eke that out for months by buying as little as possible. But these weren't normal circumstances. Once October hit, I'd have server fees, car insurance, car tax, two major family birthdays and Christmas. Even shopping around and being as careful as I could, those would still hit a massive hole in my savings. I had to face facts and sign on. It has been just as awful as I knew it would be. The Job Centre hate every article that I write on revenue sharing sites. They've thrown everything at me to stop, up to and including threatening to stop my benefits. This is despite the fact that they know I'm making no actual money yet. The potential is still there.

This is where I find myself on the eve of 2012. My assets are thus:

  • Wizzley. So far there are nine articles, one of which earned me an Editor's Choice award. They're all getting lots of hits, but none have translated into earnings yet. It's a really friendly site that I want to see survive.
  • Suite101. So far there are 127 articles, two of which have earned me an Editor's Choice award. Between August and October, I thought this was going to be the place that secured my dream of being a paid freelance writer. My income was steadily growing. Then it crashed under Panda. The admin are making huge promises about revamping the whole thing. They're hoping that it's going to bounce back and, by this time next year, it will be returned to its former glory. I have my fingers crossed on that.
  • The House That A Girl Called Johnny Built. So far there are 50 articles, some of which have been getting a huge amount of hits. But this isn't yet translating into income from Chitika. I've asked for help from their support desk and swapped e-mails with a really friendly, encouraging lady. But her only advice was to keep on writing. It'll come eventually. My main asset here is that, if I keep writing, I'll be able to apply for affiliate blog posts next August.
  • Jo's Library. I have 38 book reviews here, all with links to Amazon. It has translated into some sales, but not a huge amount. We're talking a couple of pounds, not even enough to buy myself a book! Again, it's a keep going and hope for the best situation. All that I've read says that the more articles you have, the more likely someone will find one, the greater the chance that they will click on your link and do their shopping on Amazon.
In short, I've written 224 articles since the end of July, across four different sites. I'm freaking exhausted. I'm grasping every straw of hope as it comes; but fortunately, some of those are really big straws.

Let me pause here to mention sareyva. This amazing woman has held my hand every step of the way. I might have written 224 articles, but she's taken it upon herself to both read and proof-read 224 articles. The reason that they're all so free of typos is because I awake to daily PMs from her correcting them. She does more. She encourages me. She stands behind me and tells me that she believes in me and that I can do this. Every single day. Sareyva is the antidote to the Job Centre's poison. She's the person who ensures that I know I'm not alone in this; and that someone has faith in me. If - when - I finally get my big break and succeed, then Sareyva is owed half of the credit.

I'm so grateful to her. I've told her often in PM that I appreciate all that she does, but I want to put that somewhere public too. I don't ask her to do any of this proof-reading for me, neither do I expect it, but I'm so desperately glad that she volunteers to do it. I owe her a bigger debt than I could ever pay. She's simply an unbelievably compassionate, wonderful person. I wish her all the best in her life and I'm inordinately grateful that she's in mine.

There's my family, particularly my parents, who've ensured that I won't starve and will keep a roof over my head. I'm still paying my way, as I did when I was getting proper wages, but the safety net is there. My real world friends have done their best, but distance, degrees and ill health have kept much of their attention elsewhere. I don't blame them for that. My issues are not the bigger deal here.

I also want to give a special mention to Miyamashi, who gives me a break from all the writing by RPing with me three times a week. He's there for a chat too, should I ever need to vent. He's a great friend and I can't wait for him to regain internet access. I miss him lots. Then there's BrookeStardust, who's bent over backwards trying to publicise my articles, find people for me to interview, ferret out obscure writing jobs and, on one occasion, chucking me a tenner for petrol in my car. Moreover, BrookeStardust is one of life's shining stars and a brilliant friend. All hail too the unstoppable Orangepunch! Amongst her many talents, she's pretty much single-handedly run my MangaBullet club this past month or two! That's been a huge relief for me, as I was severely running out of time each day.

Beyond that, I'm in a lot of great communities. The Twitter EHC gang, who so vividly bring my characters to life. Well, my characters should read their characters now. They own them. Guns and Games! Everyone there just buzzes with activity and I love stepping amongst them. Canting Away! My Runescape clan, who really know how to party in pixels.

So, as 2011 draws to a close, I'm stressed and tired, but still hopeful. I think 2012 might be the year when it all comes together; and I really can't wait!

Happy New Year everyone!

Monday, 26 December 2011

How I Stopped Search Engine Hijacking in Firefox

I love Firefox. It's my favourite browser of them all. None of the others even come close in my esteem and estimation.

Internet Explorer is just generally too clunky, slow and ridiculously open to being hacked. (I know website coders who deliberately add crap in for IE users. It's revenge for the hours of extra work that you have to do, in order for your website to display in IE.) Google Chrome is tracking everything that you do. Safari and Opera are just too unfamiliar; and none of the others have even made it onto my radar.

So when Firefox started to fail me, I pulled out all of the stops. Then I fixed it.

What Went Wrong with Mozilla Firefox?

It might be easier to tell you what went right during those horrible days. However, the very annoying things were these:
  • My toolbar search engine was hijacked. It was displaying results from search.insiteapp instead of Google etc.
  • Javascript failed on some sites. For example, the Adwords traffic estimator tool wouldn't load.
  • 404 pages redirected me to advertisement search engines.
  • Watching BBC iplayer whilst playing Runescape caused a) one of them to crash or b) blue screen of death and the whole computer crashing.
  • Application data folder disappearing from my files list.
  • Advertisement pages opening up in separate windows, as I browsed the internet.

There were a few other things besides, but they were the biggies. It was patently time for drastic action. It just took me several weeks of taking drastic action (running CCleaner ad nauseam, flushing dns, updating Java, updating Foxfire, altering the host's commands, tatting in the Firefox open source code, the usual) to find the one that worked.

Preparing to Start Again with Firefox

When all hope has gone in fixing your computer in any other way, you reformat it. That really is the last resort beyond which is scrapping it and buying/building a new one. I hadn't reached that stage, but I was about to embark on the browser equivalent.

The problem was solely with Firefox. I ventured (highly temporarily!) back into Internet Explorer, where I learned that the issues with websites were all gone there. That confirmed it was the browser not the computer. I'd need to reformat Firefox.

Preparation Check List:
  • Save your bookmarks. Top toolbar -> Bookmarks -> Show all Bookmarks. In there is an option to 'Import or Backup'. Choose to 'Export Bookmarks to HTML', then save them on your desktop.
  • Make a note of your toolbars. If you're anything like me, you have toolbars from your favourite sites. Top toolbar -> Options. Write down or screenshot everything that is ticked. You'll want to restore them from source later.
  • Download Firefox, but don't install it. Make a note where the exe set-up file has gone. If you're using Windows 7, then it'll default to your C:/ download folder.

NB You are about to lose all of your automatically remembered passwords. If you don't think you will be able to remember them later, then go through your sites and find out or change your passwords there.

Once I'd done that, I uninstalled Firefox fully. It won't work if you merely update Firefox, or if you uninstall but leave your personal data and settings. That box needs to be ticked that removes all trace of Firefox from your PC.

Frightening, isn't it?

Using CCleaner to Remove Residual Firefox Registry Files

I've mentioned before that I can't live without CCleaner. This was another moment when it came into its own.

Once Firefox was ostensibly gone from my PC, I ran CCleaner's main cache cleaning tool. Once that was totally devoid of any gunk, I switched to their registry cleaner. It took three wipes through before all of the deep-seated registry errors were cleared, but they made very interesting reading.

My Firefox profile had some corrupt keys in there. If I had tried reinstalling Firefox now, it would have looked the same as before. CCleaner made sure that wasn't about to happen.

Once the registry error finder was returning empty, I reinstalled Firefox.

The Return of Firefox in all its Glory!

I opened up my downloads folder and found the set-up exe application, which I'd downloaded earlier. I clicked on this and went through all of the options on the wizard. Firefox was back and all of the issues were resolved.
  • Toolbar search engine no longer hijacked.
  • Javascript running perfectly on websites it couldn't before.
  • 404 pages not redirecting to advertisement sites.

I then took the time to import my bookmarks (same place as you exported them) and to start the rounds of downloading all of my toolbars again.

Discovering the Initial Source of the Search Engine Hijack

While doing that, I discovered something very interesting. I play Runescape, so their toolbar has been a feature of my browser for a while. After I'd navigated to their site to reinstall it, I received a pop-up (my previous Firefox had been set to not allow pop-ups, hence this hadn't shown the first time around):

Every box there had been ticked by default. That explained the initial search engine hijacking.

Everything else had probably been damaged in my attempts to remove it without access to those settings afterwards. By loading Firefox again from scratch, all of the default settings were restored. This allowed things like the javascript to run again properly.

Rediscovering the Missing Firefox Application Folder

My guess is that none of the above had hidden my Mozilla profile folder. It was more likely to be a firewall error. Perhaps there had been a dodgy file alert once, that I was too busy to explore, so I just clicked to isolate the thing until later.

Or maybe it hid itself out of terror at me employing the Hands On Imperative. Who knows?

But while I was dealing with all other annoyances, it was time to hunt that down again. That was easy enough! Just follow these steps:
  • Open the main toolbar and select 'Help'.
  • Click 'Troubleshooting Information'. (This is the stuff that geeks will need on forums, if you're ever rushing for assistance there. You're not right now, so just know that it's there.)
  • Under 'Profile Directory', select 'Open Containing Folder'.
Your missing folder will be open again! But you'll need to find out where it was hidden, before you can return it to its place in the directory. You can see the address in your browser window:

Now trace back to the exact folder which isn't displaying. For myself, that was 'users'.
  • Click on the first hidden folder. (i.e. Users)
  • Right-click anywhere on that page.
  • Select 'Properties' from the pull-down menu.
  • Untick the box saying 'hidden'. (You don't want all of the sub-folders, just the main one.)
  • Press OK.
Your folder should now be displayed in the directory, where you wanted it to be.  Hurrah!

Happy Solstice to Me

Repairing all of this has left me with a Firefox browser which isn't annoying to use. Taking the time to really sort it out was my Yule present to myself.  I'm now feeling very smug and pleased with it all again.

Happy Holidays everyone!

Sunday, 25 December 2011

Happy Solstice and a Merry Christmas

I'd like to wish all the best of the season's greetings to those reading this blog. However you celebrate it, and for whatever reason, I'm sending my blessings to you this midwinter. Woot! The sun is coming back! We have tinsel and slightly longer days; not to mention a dram or two in the glass.

Here is my favourite 'carol' to give us a little background music. I know it's this time of year whenever I hear it.

As a Pagan, my own festivities are with the Winter Solstice. But this Sabbat chimes with the Christmas celebrations going on all around me, so that works too.

I didn't think I'd be doing much this year. I'd obviously be joining my family for Christmas Eve and Day, but otherwise there would be articles to write. I even mumbled a quick apology to Arianrhod, in advance, that I'd probably spend Yule working. I was wrong.

On December 20th, one of my closest friends paid me a surprise visit and stayed for a couple of days. We feasted, drank and swapped gifts, as is traditional at this time of year. More importantly, we chatted and put our respective worlds to rights. It was good for both of us and laden with peace and goodwill.

The Wiccan Yule is also a time for reflection - looking back over the last year and our lives in general - musing in relaxation and without censure. It helps us know where we are and highlights where we wish to be. It's a review and a sinking of deep roots. It's a kindness to ourselves; as well as a lovely and fundamentally vital period of down time in an otherwise hectic year.

My friend and I did this. We toured all of our old haunts and looked at photographs, remembering the past in laughter and one or two good tears. We shared our stories, as we recalled them, collectively piecing together our memories. It was both fun and a necessary retracing of our steps. Most of all, it reminded us that we'd once been in situations that had appeared insurmountable and the end of the world. A decade or two later, we'd not only survived, but could look back in fondness for the people we'd been, the people we were and the people we would one day become.

Once he had returned home, it was time to move from the Paganism into the Christianity of my family (without, it must be said, much in the way of discernible difference). There was still the tinsel and the tree; there is the music and the memories; there's the food, the drink and the sharing.

Christmas Eve is traditionally the Grand Tour of my extended family. My parents and I moved from house to house, always greeted with an open armed welcome. Drinks were pressed into our hands after the hugs; food filled our plates. Some of these people I only saw last week. One or two, I only see at Christmas. It was great! The conversation flowed (until some idiot puts the bloody telly on, which instantly shifts me from partial deafness into full deafness and counts me out of any further participation), catching up on each other's lives, laughing, joking, reaffirming the close bonds that bring us together.

Today is Christmas Day. It began, as they always do, with my Dad bouncing around like Tigger. I love how excited he is by the whole festival. He burst into my room just after 8am with a hearty, "He's been!" Santa Claus had indeed been. I had a pile of presents waiting for me downstairs, alongside those of my parents. We opened them in ooooohs and thanks.

Now I'm catching up with my on-line friends (and cyber family). Several fora have my contribution in cheers and season's greetings. There's such a great atmosphere everywhere.

Later it will be nuclear family time. I'll be travelling to my brother's house to engage in more hugs, gifts, drinking and feasting. No doubt I'll wind up being very merry by midnight tonight.

How are you spending your Midwinter? Season's greetings in love to you all.

Friday, 23 December 2011

June's Memories of the Landywood Great Stones

Another of Great Wyrley's older residents has come forward to share her memories of the Landywood Great Stones. I am enjoying this trend, as oral history is invaluable in piecing together all these clues.

Like Mr Wiggin, June has lived in Great Wyrley all of her life. She enjoys a tipple in Harrisons Club, in Wharwell Lane, where she bumped into my father. He asked her if she recalled where the Great Stones once stood, and she did.

June told him that the larger stone, which currently sits outside Landywood Enterprise Park, was once opposite the school. She described a grassy island in which the stone stood alone. It surprised her to hear that there had been others too.

Her recollections dated from the 1940s. She said that, as the end of World War II was announced, the people of Wyrley gathered close to the lone Landywood Great Stone. They built a bonfire to celebrate and spent the night dancing in its light. There was a lovely party, born of relief and the end of the fighting.

June's memory of the stone chimes with that of Mr Wiggin. He had previously told my mother that 'they were opposite where Landywood Primary School now sits'. The only difference being that Mr Wiggin spoke of multiple stones, while June recalled just one.

View Larger Map
The view outside Landywood Primary School

Saturday, 17 December 2011

Adventures of a Freelance Writer: Signing Up for Wizzley

Starting out as a freelance writer can feel so onerous. There is the love of writing, that makes this seem like a perfect career choice, but then there's everything else as well. Will the website succeed? Will you actually be able to make a living wage? Will the writing continue to be your passion in life, once you're knocking out articles for months on end?

Yesterday, I signed up for Wizzley. In many ways, it was just another site that I'm trying out for size. But this one feels just that little bit different. For a start, it's inserted a hefty dose of fun back into the writing and that would recommend it all on its own.

How I Found Wizzley

The advertisement seemed plain enough. There's the logo and my three major concerns outlined straight away.


On the other hand, there are about a billion other sites offering the same thing. I've signed up to a dozen in the past three months. Some I've explored, then wandered back out; others, like Suite101, have convinced me to invest hours of my time in researching and writing dozens of articles.

Wizzley came with a personal recommendation attached. The lovely Christian Dörr writes for its German counterpart, PageWizz, and he was gushing on the Suite101 forums about its virtues. I decided to give it a try.

Writing Articles for Wizzley

I've only written one article there so far: How to Write an Essay. That was mostly to try out the formatting and to see if this was the sort of site where I could fit in. I was very pleasantly surprised.

In all of my past writing platforms, I've been confronted with a single blank page, which I then have to fill with words. This is fine. I'm a writer and I enjoy filling blank pages with words. Wizzley is different. It employs a series of modules to fit together in order to create a page. The modules can include anything from videos, polls, content lists, RSS feeds, images, the list goes on! And, of course, text boxes, which is where I really come into my own.

It took me three hours to write that article. That wasn't anything to do with content, as I was relying upon personal experience. It was also nothing to do with actually writing it, as I can produce something of that length very quickly. The culprit was enjoyment. I was having so much fun working out what all of the modules could offer me and my readers! I inserted things, checked the page view, took them out again; inserted other things... Yes, I was playing very nicely. The geek in me was enthralled.

Of course, future articles there won't take half as long. I've checked out all of the pretty possibilities now, so I know what I can use in the future.

I should also note here that the user interface is very easy. The text boxes have standard controls, both in rich text and HTML (the former is the default). The potential modules work on the drag and drop principle.

Reasons That I Love Wizzley So Far

Bear in mind that I've only been there a day, with just a single article to my credit, so this really does reflect initial impressions only.

  • The fun aspect. Who can't love building pages by dragging and dropping a choice of modules into the main event?
  • The presentation potential. I've been slightly frustrated in the past, because I've wanted to insert items that would inform my readers a little better. For example, when I was writing about My Tram Experience, it would have been nice to insert the YouTube footage that I was discussing. On Wizzley, I can.
  • The page view. On many sites, you just have to write and hope it looks good when it's published. On Wizzley, you can do that before it's gone live. The author flits between page view and edit view, so it can be seen in all its glory before anyone else has to spot your typos.
  • The admin. I had a query almost as soon as I signed up. I'd discovered that you can get commission for referring people there (cheekily ninjas in a referral link). This isn't unusual and I should have checked that before I signed up, because Christian had recommended the site. He should have got the credit for that, even if he hadn't provided the link. I asked about it on their forums and, within a few minutes, one of the site's owners had replied. He told me who to e-mail with the name. I did that and he responded to the e-mail immediately. Christian was now credited with bringing me on board.

That last point might not seem like a lot to many people, but it made a very refreshing change to me. A lot of sites that I've signed up for ignore this aspect entirely. Wizzley's administration seems geared towards its writers (we're called authors there) and readers, rather than some distant business model. In this world of harsh, cold recession and dehumanisation of the masses, being simply treated like a valuable asset nearly brought me to tears.

This is why, out of all of the sites on which I've created accounts recently, I'm singling out Wizzley to big up in this blog.

Edit: Ooops! I forgot to mention something important for writers earning a living here - yes, you can insert adverts. They have their own module. You can put in Chikita, Amazon, Zazzle, AllPosters, Ebay and Adsense. You have the revenue on that. Wizzley also have adverts, from which you get a commission. But there are strict rules on the number of adverts that can be placed. They shouldn't be the main event here.

Friday, 16 December 2011

Amazing Video Posted on YouTube: Speech from the Great Dictator

Vladcubax, who uploaded this onto YouTube, wrote, 'WOW !!! YOU CAN REPOST, RE-UPLOAD, LINKS or whatever...peace and love to all.'

Accepted and in agreement.

Thursday, 15 December 2011

What to do if Paper.li Displays Mangled Posts

Paper.li is a wonderful on-line tool, especially for writers like me. It's a curating service that collects together all of your links, RSS feed articles, Twitter hashtags and a variety of other media, then displays them all in a newspaper format. This provides a single URL to which you can direct readers. Instead of trawling through a dozen websites or a billion Tweets, they have everything that you want to show them right there.

But what if something goes wrong and your articles appear in Chinese writing on Paper.li? This happened to me recently, but we have the solution to put it right!

The problem comes when there's a mismatch between your RSS feed and Paper.li's receipt. When I first started receiving some articles in the Chinese language, I contacted Birgit Seitz of Paper.li. She responded very quickly, very professionally, but with a friendly tone. Unfortunately the news she gave me made it clear that the initial problem had been at my end:

The feed defines that content is in utf-8:
While the content of the feed is served as iso-8859-1 (latin1).

I passed this onto the Tech Team at Suite101, as that was the site from which the articles were scrambled.

The issue was fixed for many Suite101 writers, but not for me. Happily, Suite101's Darren Roberts was equal to finding the extra step needed. After some experimentation, here is the fix that he gave me; and it worked!

Changing Paper.li Link Articles Back into English from Chinese

You will need to be logged onto Paper.li, in order to change your paper settings.

  • Click on your account icon in the top right-hand corner.
  • Select 'Paper Settings'.
  • Select the affected newspaper from the pull-down menu in the left-hand corner of your paper settings control board.
  • Click on the 'Content' tab.
  • Scroll down, below all of the stream settings, to the 'Paper Language' section.
  • Select 'English' (or your language of choice) from the pull-down menu.
  • Scroll down further and press 'Save'.

The next time that your Paper.li newspaper updates, your articles will all be in the language that you selected. The Chinese writing will be gone and you can enjoy the benefits of this wonderful curator tool again. However, you should note that the already published papers will remain as they are.

If these fixes didn't work for you, then I recommend leaving a ticket with the Help staff at Paper.li. They are very friendly and will answer promptly.

For more information about what Paper.li is, and how it might make your life easier, please check out a previous article that I wrote about it. Or, of course, peruse my own current newspaper: The Jo Harrington Times!

Wednesday, 14 December 2011

Great Wyrley History: The Fighting Ground

In 1888, Queen Victoria was on the throne and Robert Cecil, Marquis of Salisbury, was running the country. The latter was causing controversy by stating that no 'black man' would ever be elected to represent a British Constituency. (He was referring to Dadabhai Naoroji, a man of Indian descent, who went on to be the MP for Finsbury Central.) The Oaths Act became law, removing 'God' from the oath of allegiance sworn by all MPs when entering Parliament, thus allowing Atheists to run the country. Jack the Ripper claimed his first victims in Whitechapel; the football league was formed; and a cartographer wandered through Great Wyrley making a map.

In the place where legend would have it that the Landywood Great Stones once stood, the cartographer wrote a label. This was, the map now stated, the position of the Fighting Ground. So what was the Fighting Ground?

Great Wyrley in the 19th Century

Today, Great Wyrley is a sprawling expanse of housing estates, hemmed in by the ruins of old mining works and the motorways leading anywhere else in the country. In the 19th century, it looked very different. It was smaller for a start, with clear divisions between Old Wyrley, Wyrley Bank, Landywood, Little Wyrley, Churchbridge and Cheslyn Hay.

The old men and women could still recall when all of this had been forest. Their own parents had made their living cutting the trees all down. The old timers had earned their bent backs and wrinkles in the shallow cut mines that sprung up once the charcoal had been cleared. There were deeper shafts being dug now, in places like Streets Lane and Gilpins down by Churchbridge.

In 1842, a law had been passed, which banned all women and children under ten from entering a mine. It meant that the black faces, hacking coughs from lungs filled with coal dust and the blue tattoos from cuts healing with coal dust in it, all belonged to the men. It also ushered in a new age of poverty, from families used to having far more wages. Kids as young as two could earn money down the mines, opening and closing doors.

Great Wyrley's Fighting Women and Gorse Bushes

Wyrley's women supplemented their family's income by collecting gorse from the moor and hillside edging Gorsey Lane. This could be burned as fuel, or crushed between rocks to feed livestock. The flowers were human food too, making a lovely salad or boiled as a tea. But there was a bigger trade to be had there.

Gorse is also called Broom for a good reason. The branches can be chopped into shape and gathered together to make a traditional broom. Wyrley women were frequently found in the markets of Wolverhampton, Walsall and Cannock selling their brooms. They earned a reputation for rowdiness and hard-faced bargaining there. They were forever causing fights.

The problem was so great that Frederick William Hackwood recorded the complaints of a Walsall police constable, in his The Chronicles of Cannock Chase (1903), about Wyrley women. It seemed that females caught fighting in half of the Black Country would be wont to give Wyrley Bank as their place of origin.

It was often just a convenient lie, because fighting women were expected to come from Wyrley. Most of them had never even seen the town, but they and the police knew that little could be done along that line of enquiry. No uniformed officer asking questions in Great Wyrley would find any answers. It was viewed as a den of iniquity, with the residents banding together against all comers and refusing to co-operate with the law.

It seemed that little had changed since the 12th century wardens of Edward III had reported back to their monarch that 'nothing can tame those wild Wyrley folk'. Even as late as 1888, there were no police officers in Great Wyrley. In 2011, there's still no police station.

The End of Bare-Knuckle Fighting in Victorian Britain

Great Wyrley, with its reputation and isolation, became a Mecca for sports that the law frowned upon elsewhere. Amongst these was bare-knuckle fighting, which settled feuds or simply pitted one hard man against another. It was illegal in 19th century Britain, with police officers entering the fray to break them up and arrest all participants.

The Marquis of Queensbury had tried to channel all of this aggression into a series of rules, which would ultimately result in the accepted sport of Boxing. But in 1867, when he introduced them, even boxing was afforded only a dubious legality.

The whole weight of the justice system was being thrown against bare-knuckle fighting, particularly when people were betting on the outcome. In 1882, the Court for Crown Cases Reserved made a judgement that is still law in Britain (R v Coney). The Crown Justice ruled illegal any fight resulting in actual bodily harm, even if both participants had given their prior consent. Anyone found spectating could also be charged with aiding and abetting.

This was pretty much the death knoll for locally organised bare-knuckle fighting throughout the country. But not in Great Wyrley.

The Fighting Ground in Landywood

The wide expanse of land was scarred with the remains of open cast mines and the shallow gulleys forged to drain water away. It had once been forest and off, on the far Southern horizon, there were still the few dotted trees that were now Essington Wood. This was the Fighting Ground.

Holly Lane looped around it, leading in one direction to the Wyrley-Essington canal, along which walkers could eventually reach Wolverhampton and Birmingham, and in the other to the fields backing onto the old Roman Watling Street. Run that way and you had a choice between North Wales or London and anywhere else between the two.

Not that anyone would have had to go to such extremes. Landywood was just a short sprint away from Wyrley Bank, where people could just disappear because no-one would have seen them at all. Especially when a police officer was asking.

To the immediate north-east was Broom Hill, with its sweeping view over endless acres of Staffordshire and maybe even as far as Shropshire. This was the place where the Parliamentary army had camped during the Civil War for precisely the same reason. You can see people coming from miles away. A shout from the top of Broom Hill could probably be heard from the Fighting Ground. A lit bonfire would certainly have been seen.

In short, the bare-knuckle fights went on with impunity. There were no police officers in Great Wyrley. If any were called, then the look out on top of Broom Hill would see them coming. In any case, it would take a long time for them to get there. They'd have to arrive from Cannock or Hednesford, which was a fair trek, even on horseback, and someone would have to get the message to them first.

But in the extremely unlikely event that police constables arrived on the scene in time to interrupt any fight, there were escape routes everywhere. The majority would probably have just run south, where there was no barrier between them and the distant Essington Wood. It would have been a short stroll back along the canal later.

There is oral history of this still in Great Wyrley. Steve, who we last met in the hunt for the Landywood Great Stones, had heard all about it. The story passed down to him was that prize fighters used to come from miles around - from the Black Country, from Staffordshire, from Shropshire, Birmingham and perhaps even further afield - because Great Wyrley was one of the safest places to fight, as regards the law.

Monday, 12 December 2011

Part 6: Using Macros to Streamline your Runescape Excel Hiscores Tracker

Welcome to part six of my guide on using Excel to track your Runescape Hiscores. We've already created three tables, which record daily XP, work out your XP to the next level and determine your overall averages. This time, we will be running some macros to make them even more convenient to consult.

Please note that once you add macros into the equation, you will need to save your Excel workbook in a different mode. You'll be prompted to do this when you come to save it anyway, but the correct filename is: Excel Macro-Enabled.

Runescape Excel Hiscores Tracker: The Story So Far

This guide is written in the assumption that your workbook looks exactly like mine. For that to be the case, then you will have had to follow the previous entries. All of the data referred to and the tables created are outlined in them.

I've been asked if this could be used for other on-line games too. Yes! As long as the hiscores of that game are on a website somewhere, you can use the techniques in this guide to create a tracker for those too. If you encounter any difficulties in creating a World of Warcraft Excel Hiscores Tracker or whatever, then just comment here and I'll see what I can do to help.

Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker - Using the contents of websites to add data into Excel, notably a couple of ways to add your Runescape Activity Logs.

Part 5: Tracking Daily XP in your Runescape Excel Hiscores Tracker - Letting Excel calculate how much XP you had gained in Runescape today, as well as recording the levels gained and your rank changes.

But many of these tables are currently being sorted by hand. Let's change all of that by inserting a few macros!

Adding Macros to your Excel Runescape Hiscores Tracker

If you have never used macros before, then you will first have to access the tools to use them.
  • Click the Excel logo in the top left-hand corner of your workbook.
  • Click the button 'Excel Options' from the bottom right-hand corner of the pop up menu.
  • Tick the box next to 'Show Developer Tab in the Ribbon'.
  • Press 'Ok'.

Did you imagine the level up fireworks then? If so, then your reward for this level is to access macros. Congrats!

Using a Macro to Sort the Data in our Runescape Hiscores Averages Table

Until now, we have been either using a sort filter or else manually rearranging the data in our Hiscores Averages table. Half the time, we might mess up and accidentally sort the wrong columns. Retyping all those formulas is a pain, isn't it? It's time to make our life a lot easier.
  • Open up the worksheet with your Averages Table in it.
  • Click on the 'Developer' tab from the workbook's top toolbar.
  • Open 'Macro Security' and select 'Enable all macros (not recommended; potentially dangerous code can run)'. The warning is correct, but we need it open before we can create one! We'll disable it again later.
  • Press OK.
  • Open 'Record Macro'.
  • In 'Macro Name', type 'SortAverages'.
  • Ensure that 'this workbook' is selected under 'store macro in'.
  • Add a description if you want to.
  • Press OK.

  • Select the 'Home' tab from the top toolbar.
  • Highlight cells Q2 to S26, ie the data under the skills, level and XP columns of your Averages table.
  • Select 'sort and filter' from the 'Home' toolbar.
  • Select 'custom sort'.
  • Sort by Column S; sort on Values; order them Largest to Smallest.
  • Press 'OK'.
  • Return to the 'Developer' tab.
  • Press 'Stop Recording' from the 'Code' category.

Congratulations! You just created your first macro. Ok, you can't see it yet, but it's saved in the background waiting for you to use. In short, Excel has just recorded actions that you took. In future, it can simply replay them.

Of course, this is no use, if you haven't got anything to press to cause it to replay.
  • Click the cell where you want to insert a 'Sort' button. (I'm going for cell Q30.)
  • Click 'Insert' in the 'Developer' tab.
  • Select an icon or button. (I'm going for the very first that you see. It's called 'Button (Form Control)'.
  • Type 'Sort' on the button. (You can pretty it up in the 'Home' tab, where all of the fonts and colours are.)
  • Right-click the button and select 'Assign Macro'.
  • Select 'SortAverages' from the list.
  • Press OK.
Whenever you now press that button, it will replay your actions from earlier. The columns will be sorted.

Using a Macro to Sort the Data in our Runescape XP to Next Level Table

I had a sort filter already on my XP to Next Level Table. Before I start my macro, I'm going to remove that.
  • Highlight cells N1 to O26.
  • Click the 'Sort and Filter' button on the 'Home' toolbar.
  • Select 'Filter' from the pull down menu.

Remember that I only did that because I had a filter active on it. If you didn't, then skip the above step. It is removed when the icon beside 'Filter' isn't highlighted.

In the first image, it's active. In the second image, it's not. You want it off.

    Click on the 'Developer' tab.
  • Open 'Record Macro'.
  • In 'Macro Name', type 'SortXPNextLvl'.
  • Ensure that 'this workbook' is selected under 'store macro in'.
  • Add a description if you want to.
  • Press OK.

  • Select the 'Home' tab from the top toolbar.
  • Highlight the XP to Next Level table.
  • Select 'sort and filter' from the 'Home' toolbar.
  • Select 'custom sort'.
  • Sort by Column O (XP to Next Level); sort on Values; order them Smallest to Largest.
  • Press 'OK'.
  • Return to the 'Developer' tab.
  • Press 'Stop Recording' from the 'Code' category.

Now you just have to add a button in precisely the same way as you did before, except that you assign your latest recorded macro to it.

My worksheet now looks like this:

Your buttons probably look a lot more artistic than mine! LOL

Using Excel Macros to Record Yesterday's Runescape Hiscore Data

In part five, I told you that macros could be used to track your daily XP on Runescape. This is the fabled option three, which will copy data to populate columns C, D and E on your Daily Tracker.
  • Open your worksheet with the daily tracker table on it.
  • If you hid the previous data, click B and F to highlight their respective columns. Right-click and select 'unhide'.
  • Record a macro, entitled 'DailyXP', of you manually transferring the relevant data. (This was outlined as Option 1 in part five.)
  • Stop recording.
  • Hide your columns C, D and E again. (Highlight them, right-click and select 'hide'.)

You have now set a macro to do all of that sorting for you. You could insert a button, in the same way that you did to sort the other two tables, in you wished. However, we can make this even more automatic by asking Excel to run it for us.

Instructing a Macro to Run at a Certain Time

It is possible to ask Excel to run a macro at a specific time. This has obvious benefits for recording daily XP. It doesn't require you to remember to transfer your data.

Unfortunately there is a limitation too - the workbook has to be open at the time. For those people with enough Green awareness to switch their computer off at night, then this could constitute a problem. If you habitually play Runescape with your Excel Hiscores Tracker open, and finish at the same time each day/night, then there should be no problem at all.
  • Click on the 'Developer' tab on Excel's top toolbar.
  • Double-click 'View Code' in the 'Controls' category.

This area of Excel should come with a warning saying, 'Here there be dragons'. But we're all Runescape players, so a mere dragon holds no danger. Apply a mental dragonfire shield and sip some imaginary anti-fire potion and explore the coding section of Microsoft Office Excel.

On the left-hand side, you will see two lists under the heading VBAProject and the name of your workbook. Microsoft Excel Objects individually lists all of the worksheets. Modules has all of the macros that you've recorded.

If you double-click Module1, you'll see the Sort Averages code, the Sort XP to the Next Level code and the DailyXP code. All of these were created just now, as we recorded ourselves doing them. When Excel repeats our actions, it's merely reading what's in this module. This can be edited, if you wished.

That's just for information. To set our macro to run at a certain time, we need to place a bit of code in another sheet.
  • Double-click 'ThisWorkbook' from the 'Microsoft Excel Objects' list.
  • Copy and paste the following code into the pad that pops up:
Private Sub Workbook_Open()
Application.OnTime TimeValue("23:00:00"), "DailyXP"

End Sub
  • Close the Visual Basic area by clicking on the X in the top right-hand corner.
  • Save and close your Excel workbook.

We've closed the workbook because macros like these are only saved when you reopen the workbook once. Thereafter it will run without the need to close and open it again. So go ahead and do open up your Runescape Excel Hiscores Tracker again.

The code that we've inserted will cause the macro to run at 11pm every night. It takes the time from your own computer clock, so we don't have to worry about timezones here. But 11pm might not be the ideal time for you.

The part of that controlling time is ("23:00:00"). It runs in 24 hour clock - hour:minutes:seconds. If you want to change it, then open up the Visual Basic section again and double-click 'ThisWorkbook' to display the pad. Then you merely edit the time to one of your choosing.

Macros in our Excel Runescape Hiscores Tracker

There are a variety of ways in which macros can be used and set in Excel. If you can think of something else that you wish could be done automatically, then a macro can probably help. There are hundreds of websites devoted solely to walking you through macros, so just do a search to see if it's possible to do what you want to do. My recommendation is always to experiment!

I hope that you've found this section of the guide useful. Next time, I will be showing you how to add some XP and price calculators to your Runescape Hiscores Tracker.

Part 5: Extra: Correction and Apology

In part five of the guide, to making a Runescape Hiscores Tracker in Microsoft Office Excel, I told you to do this:

In cell I2, copy and paste the following formula: =D2 - H2

The formula was the wrong way round!  Whenever you gained a level, the table told you that you'd lost one instead. Ooops!  That should have read:

In cell I2, copy and paste the following formula: =H2 - D2

What can I say? I'm a noob.   I've just corrected that in the original blog entry, but if you've been following this as it's published, you'll need to alter that in your Daily XP Tracker too.

Apologies for that!

Saturday, 10 December 2011

Part 5: Tracking Daily XP in your Runescape Excel Hiscores Tracker

Welcome to my series on how to create a Runescape Hiscores Tracker in Microsoft Office Excel!

Yesterday, I hunted penguins, then spent my points on Summoning XP. Ths involved teleporting all over Gielinor. I also converted some charms into pouches, before chipping away at some gems with my chisel. In the middle of all of this, I caught an impling or two, as they flew by me. Finally I couldn't put off the dreaded thing hanging over me in-game. I had to have another attempt at killing Vanstrom Klause.

I tooled up in Edgeville Bank, where someone optimistically asked if anyone had 97 Construction to assist him with his effigy. Yes, I did. I helped him out. Then it was off to Darkmeyer, where I accidentally hit a vyrewatch, before fleeing to the safety of the bank. I fought Vanstrom with range. He turned into mist and I threw the Holy Water. But he wasn't lured onto it and it missed. He killed me.

Once the dust had settled and the nerves salved, the next question was how much XP did all of that gain me? The answer was in my Excel Runescape Hiscores Tracker. Glancing down the XP Gained column retold the story of my in-game activities in cold, hard facts. I'd netted 128,921 total XP, split up across eight skills.

This is the table that I'll guide you through creating for yourself.

Runescape Excel Hiscores Tracker: The Story So Far

This guide will be written in the assumption that your workbook looks exactly like mine. For that to be the case, then you will have had to follow the previous entries. All of the data referred to and the tables created are outlined in them.

Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker - Using the contents of websites to add data into Excel, notably a couple of ways to add your Runescape Activity Logs.

Let's get on with creating a daily XP tracker!

Building Your Table to Track Daily Runescape Experience

I want to chart my daily XP on the same page as I have my XP to Next Level and Hiscore Averages tables. There's still enough room there to see it all in one place. Before I can do that, I need to move those tables along. Skip this part, if you want to place your table in a different worksheet. Part four described how to do that.
  • Open up the worksheet with your tables in it.
  • Highlight the first column by clicking on the 'A'.
  • Right-click anywhere in that column.
  • Select 'insert' from the pull-down menu.
  • Repeat that until the 'Skill' column of the XP to Next Level table is in column N.
  • Click in cell B1,then drag your cursor to cell L1.
  • Colour and format them to match your other headers.
  • In cell B1, type 'Skill'.
  • In cell C1, type 'Previous Rank'.
  • In cell D1, type 'Previous Level'.
  • In cell E1, type 'Previous Total XP'.
  • In cell F1, type 'Current Rank'.
  • In cell G1, type 'Rank Change'.
  • In cell H1, type 'Current Level'.
  • In cell I1, type 'Level Gained'.
  • In cell J1, type 'Current Total XP'.
  • In cell K1, type 'XP Gained'.
  • In cell L1, type 'XP to Next Level'.
  • Widen any column necessary to display your headers.
Yes, this is going to be a huge table! But three of those columns will likely end up hidden by the end of it.  My worksheet currently looks like this:

Please click the image for a full-sized view.

Actually, I'm going to delete the background for a moment, just to make it easier to work with. Just skip this step, if you're fine.
  • Select the 'Page Layout' tab from the top toolbar.
  • Click 'Delete Background'.
  • If you've removed the gridlines, select the 'View' tab from the top toolbar.
  • Tick the box beside 'Gridlines', in the 'Show/Hide' category.
I can now see what I'm doing, so it's time to add some formulas.

Inserting Existing Data into your Runescape Daily XP Tracker Table

This is a big table, but you already know how to populate most of those columns. The data is in your datasheets and it's simply a case of putting in the address to display them.

  • In cell B2, copy and paste the following formula: =Data!B2 (NB: Change Data to whatever you called your data worksheet.)
  • Drag the formula down to cell B26.
  • In cell B29, type Total. (Rows 27 and 28 were hidden under the averages table. If you didn't hide them, then all that I say for row 29 replace with row 27.)
  • In cell F2, copy and paste the following formula: =Data!G2
  • Drag the formula down to cell F26.
  • In cell F29 (or F27), copy and paste the following formula: = Data!G1.
  • In cell H2, copy and paste the following formula: =Data!C2
  • Drag the formula down to cell H26.
  • In cell H29 (or H27), copy and paste the following formula: =Data!C1
  • In cell J2, copy and paste the following formula: =Data!F2
  • Drag the formula down to cell J26.
  • In cell J29 (or J27), copy and paste the following formula: =Data!F1
  • In cell L2, copy and paste the following formula: =Data!J2
  • Drag the formula down to cell L26.
  • In cell L29 (or L27), copy and paste the following formula: =SUM(L2:L26)

My table now looks like this:

Please click on the image for a full-sized view.

Basic Mathematics in Excel Spreadsheets

The mathematically minded amongst you will now be flashing a little smile, when you learn that some of these columns are going to include just basic subtraction.
  • In cell G2, copy and paste the following formula: =C2 - F2
  • Drag the formula all the way down to the end of your table. (It will all be in minus numbers at the moment, because we haven't got anything in column C2.)
  • In cell I2, copy and paste the following formula: =H2 - D2
  • Drag the formula all the way down to the end of your table.
  • In cell K2, copy and paste the following formula: =J2 - E2
  • Drag the formula all the way down to the end of your table.
That really was taking the number in one cell and subtracting it from the number in another.

This is how my table looks now:

Please click on the image for a full-sized view.

Inserting the Previous Day's Data into an Excel Worksheet

Our Runescape Daily XP Tracker is now poised and ready, but for three important columns of data. There are three ways of doing this, so it's a matter of personal choice which one you choose.

Option One: Manually Inserting the Data
  • Highlight cells H2 to H29 (H27).
  • Right-click and select 'copy'.
  • Open your XP Tracker worksheet.
  • Click cell D2.
  • Right-click and select 'paste special'.
  • Select 'Values'.
  • Press 'Ok'.
  • Repeat for C2, copying the values from F2 to F29 (F27).
  • Repeat for E2, copying the values from J2 to J29 (F27).
This is now ready to use, give or take a little colouring in. The values in columns C, D and E won't change until you repeat the above steps. The rest of it will continue to automatically update. As you gain XP and levels, this will be recorded in your table.

Please click the image for a full-sized view.

There is an obvious limitation here. You would have to remember to transfer your data before you started playing. The second two options will do it automatically.

Option two: Stealing the Data from Elsewhere.

The internet is full of websites which track your Runescape Hiscores data on-line. Part four was all about taking data off the internet and inserting it into your Excel spreadsheet.

Your latest quest is to find a website that tracks your daily history. One such site is Runetrack. You could set up an account there and then insert the information off that webpage into your Excel data sheet.

Once you have the data, then it's merely a case of asking your table to automatically update. You can do that by inserting the formula: =[nameofyourdataworksheet]![celltoread]

The benefits are obvious. The whole table again automatically updates, so if you forget to tranfer your data, it doesn't matter. Your previous stats update, whenever the previous stats on the website update.

However, this isn't really fair on the individuals, like the coders and owners of sites like Runetrack. They've put in the hard work and you're reaping the benefits daily, without going near their site again.

Also should Runetrack's Sword Kill11 (or one of his counterparts from another Runescape hiscores tracking site) decide to close down, your Excel tracker will simply stop working.

Option three is to use a macro to automatically copy your data at a set time every day. I'll be coming to macros in a later guide, so I'll see you there for this one.

Finishing off your Runescape Daily XP Tracker in Excel

Once you have the data in there, and it's all working nicely, then all that remains is to make it pretty.
  • Highlight columns C, D and E by clicking the letter at the top.
  • Right-click anywhere in the highlighted columns.
  • Select 'hide'.
  • Colour and format the remaining table.

Because we have some moving data here, we can spruce it up with some more icon sets. I walked you through this in part three. But I'll show you how I'm going to add some to this table too.

  • Highlight cells G2 to G29 (G27).
  • Select 'Conditional Formatting' from the 'Home' tab at the top of your Excel workbook.
  • Select 'Icon Sets' from the menu.
  • Select '3 Arrows (Coloured)' from the options.

  • Highlight cells G2 to G29 (G27) again.
  • Select 'Conditional Formatting'.
  • Select 'Manage Rules' from the bottom of the menu.
  • Click 'Edit Rule'.
  • Set the green arrow type to 'Number'.
  • Set the value as 1.
  • Set the yellow arrow type to 'Number'.
  • Set the value as 0.
  • Press Ok.
  • Press Apply.
  • Press Ok.

Just repeat that for I2 to I29 (I27) and K2 to K27 (K29) and you're all done.

Here is how my worksheet now looks:

Please click image for a full-sized view.

Next time, we will finally get around to adding some macros.

Thursday, 8 December 2011

Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker

Welcome to my series on creating your own Runescape Hiscores Tracker in Microsoft Office Excel. This time, we will be looking at how to add player Activity Logs and more.

For those just joining us, this guide is written in the assumption that you have already completed the first three:
Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Now it's on to grabbing more data and creating a chart to show off your latest Runescape achievements.

Creating Another Data Worksheet in Excel

We already have one data worksheet, but I'm going to recommend another. This is for no other reason than to keep it looking neat and tidy, especially as we're about to insert a massive chunk of automatically up-dating information.
  • Right-click the 'Sheet3' tab at the foot of your Excel workbook.
  • Select 'Rename'.
  • Type 'Data2' and press enter.
  • Left-click the 'Data2' tab and drag it in between your other two tabs.

We will be working on the Data2 sheet for the next part.

The Runescape Activity Log Data Feed in your Hiscore Tracker

The very first task that we did, in this series, was to take data from a web query. That produced Jagex's hiscore lite text feed, which was lovely, but now we're going to take this to the next level.
  • From Excel's top toolbar tab, select 'Data'.
  • Select 'From Web' from the 'Get External Data' category.

A mini browser will pop up. This gives you access to anywhere on the web, which will prove extremely useful in getting data for our Runescape Excel Hiscores Tracker.
  • Copy this URL: http://services.runescape.com/m=adventurers-log/rssfeed?searchName=[player name]
  • Paste it into the address bar of the pop up browser.
  • Change [player name] to your own Runescape name. (Mine is http://services.runescape.com/m=adventurers-log/rssfeed?searchName=Merch Gwyar.)
  • Click the little yellow and black square to highlight the contents of the browser.
  • Press 'Import' at the bottom.
  • Excel will tell you that the XML source does not refer to a schema. We don't care. Press OK.
  • Press OK on the Import Data box. (But only if it's got 'XML table in existing worksheet' ticked for the cell $A$1.

You should now be seeing why I recommended a separate data worksheet, as the information fills the page. This works on any RSS feed, so do experiment with adding in any that you think will be useful. It will automatically update as soon as you log out of the game or, more specifically, as soon as the Activity Log updates on the Runescape site.

Displaying your Runescape Activity Log in Excel

The beauty of Microsoft Office Excel is that it allows you to have more than one tab. I'm going to create a new page to display my Activity Log.

  • At the foot of your workbook, click the final tab. (It has an icon of a worksheet with a little gold star in the corner.)
  • Rename 'Sheet4' to read 'Activity Log' (or the name of your choice).
  • Click cell A1 and type 'Activity'.
  • Click cell B1 and type 'Description'.
  • Click cell C1 and type 'Date'.
  • Copy and paste the following formula into cell A2:

  • Drag that formula down to cell A51.
  • Widen column A, so it easily displays the information.
  • Copy and paste the following formula into cell B2:

  • Drag the formula down to cell B51.
  • Widen the B column to fit the information.
  • Repeat the process in C2, but with the following formula:


You should now have all your Activity Log handily showing in your Runescape Hiscores Tracker. You will not need to touch that data again, so let's make the page go away.
  • Right-click the 'Data2' tab.
  • Select 'hide'.
Bye-bye Data2 Worksheet. Should you ever need it again, just click to create a new worksheet. If you right-click the tab of a blank worksheet, you'll find the option to 'unhide'. Selecting that will list all of your hidden sheets and Data2 will there amongst them. Once you click the name, Data2 will be visible again.

All that remains now is to colour, format and do whatever else you would like to make your Activity log look pretty. These techniques have been walked through before, so I won't repeat them here.

My Activity Log now looks like this:

Click the image for a full-sized view.

An Alternative Way of Adding Activity Log Data to your Hiscores Tracker

We've added text lite and RSS feeds from the web, but we can also insert the information from whole web-pages. As that updates, then so will the raw version in your Excel workbook.
  • Create a new worksheet.
  • Name it 'Data3' and drag the tab to a position after your main datasheet. 
  • From Excel's top toolbar tab, select 'Data'.
  • Select 'From Web' from the 'Get External Data' category.
  • Copy and paste the following URL into the address bar of the pop-up brower:

    http://services.runescape.com/m=adventurers-log/display_player_profile.ws?searchName=[player name]
  • Replace [player name] with your Runescape name. (My URL is: http://services.runescape.com/m=adventurers-log/display_player_profile.ws?searchName=Merch Gwyar.)

  • Click the yellow and black box to highlight the whole page.
  • Press the 'Import' button.
  • Press OK on the pop up 'Import Data' box. (But only if 'Existing Worksheet' is selected and =$A$1 is the cell named in the box.)

A lot of raw data will be copied into your Excel worksheet. It doesn't look like the webpage that you nicked it off, but it will update as that page does. By scrolling down and comparing the two, you will see what data you have. Anything can be taken from this worksheet and added to a table in the usual way.

Creating a Small Runescape Activity Log Tracker from Website Data

As this isn't the full RSS feed, the number of activities is much reduced. We want a table with three columns and five rows.
  • Chose where you wish to place it and create that table.
  • Colour it in, then format it with borders.
  • Type 'Activity', 'Description' and 'Date' in the cells heading each column. These are the headers.
  • In the cell under the 'Activity' header, copy and paste this formula:


Just a refresher for those unfamiliar with that formula. It is basically an address. 'Data3' is the name of the worksheet; A292 is the cell that from which we wish to copy the contents.

  • In the cell under the 'Description' header, copy and paste this formula:

  • In the cell under the 'Date' header, copy and paste this formula:

  • Repeat this for the next row, using these three formulas in each consecutive cell:

  • Repeat this for the third activity row, using these three formulas:

  • Repeat this for the final row, using these three formulas:

  • Hide the 'Data3' worksheet.
My little Activity Log looks like this:
Please click the image for a full size version.

Website Data and Tables for your Runescape Hiscores Tracker

The main point that I wished to demonstrate, with the last table, was that you can take disparate cells and put them together to make your own charts. The sprawl of data that you took from the Runescape Activity Log webpage has plenty more opportunities to feed tables.

For example, you could add another column to your XP to the Next Level table, by altering that formula to read the data telling you the percentage of a Level completed. That's listed in the Data3 worksheet. Just find the cell letter and number, then add it to =Data3! and paste it in.

This works with every webpage out there, so happy hunting for things to add to your Runescape Excel Hiscores Tracker!

Next time we will be looking at ways to track your daily XP gains.

Wednesday, 7 December 2011

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker

Welcome to my series on making a Runescape Hiscores Tracker in Microsoft Office Excel. Once set up, this spreadsheet will automatically update each time you log out of the game.

So far, we've grabbed some data, then made a chart to work out the average of the whole for each skill. In a bonus section to part two, I answered some questions about formatting.

This is written on the assumption that you've been following the series so far. Now let's grab some more data and create a new table. This one will automatically calculate how much XP is needed to your next level in every Runescape skill.

Runescape Excel Hiscores Tracker: Adding More Data

Before we can calculate the XP needed, we need to know the amount that causes you to level up. This is probably the easiest insertion of data for you, because I'm going to provide it!

  • Open your Runescape Hiscores Tracker Excel Workbook.
  • Select the worksheet with your raw data in it.
  • Copy and paste the following in cell H1:


  • Copy and paste the following in cell I1:


All done! My data sheet looks like this:

Runescape Hiscores Tracker: Naming Your Ranges

There is a formula coming up that's going to require us to name a range. We'd better do that then.

  • Click on cell H1 (which should currently have a 1 in it).
  • Drag your mouse down to H99 (which should currently have a 99 in it).
  • Right-click anywhere in the highlighted area.
  • Select 'Name a Range' from the pull down menu.
  • Call it 'Lvl' and click 'OK'.

  • Do the same for cells I1 to I99.
  • Call it 'Exp'.

Now that our columns of information are named, we can use that in a formula that looks things up on them. This is precisely what we're going to be doing next.

Looking Up The XP to Your Next Runescape Level in Excel

The worksheet, with all of my data in it, is imaginatively called 'Data' in my Excel workbook. It's important that you note what you called your tab, because it's going to be used in the next formula.

  • Click on cell J2.
  • Copy and paste the following formula:

  • Change Data to whatever you called this page. (Or leave it alone, if you called the worksheet 'Data'. :p)
  • Press enter.
  • Woot at the fact that the XP to your next Attack level should be revealed.
  • Click on cell J2 again.
  • Left-click on the tiny square in the bottom, right-hand corner of the cell.
  • Drag it down to cell J26.
  • Happy dance.

This is how my data sheet looks now:

We have our data all calculated. All that remains now is to format it into a pretty table.

Creating a Table Charting the Experience Remaining

I'm going to walk you through creating a table that looks like this:

Obviously, you might want to add your own artistry to it. Please do feel free to colour and decorate it as you wish!

Open up your main worksheet, where you last created the Averages table. Mine is huddled up in the first five columns, so I'm going to move it along a bit.
  • Click the A to highlight the first column.
  • Right-click and select 'insert'.
  • Repeat that another three times.

  • Click cell E1.
  • Right-click and select 'copy'.
  • Click cell B1.
  • Right-click and select 'paste'.
  • Drag that over to C1, using the little corner box.
  • Click on C1.
  • Type 'XP to Next Level'.
  • Widen the C column, by clicking your cursor on the wall between C and D and dragging.
  • Highlight the cells B2 to C26.
  • Colour them in using the paint bucket.

This part is really formatting the cells to look pretty. I went through it this when we were making the Averages table, and added some more tips in the bonus blog, so please consult them.

I'm going for a light grey with full borders and gridlines.

Once it's all pretty, it's simply a case of adding a couple of formulas.
  • Click on B2.
  • Copy and paste this formula: =Data!B2.
  • Drag the formula down to B26.
  • Widen your B column, if necessary. (The word Dungeoneering will probably make it necessary.)
  • Click on C2.
  • Copy and paste this formula: =Data!J2.
  • Drag the formula down to C26.

Voila! All of your information in a handy table! But I want to add a bit more decoration, just to give it a splash of colour.
  • Highlight cells C2 to C26.
  • Click on 'Conditional Formatting', in the styles section of the Home tab toolbar.
  • Select 'Icon Sets'.
  • Select 'More Rules'.

  • Pull down the menu beside 'Icon Style'.
  • Select '4 Traffic Lights'.
  • Tick the box beside 'Reverse Icon Order'.
  • Click Ok.

There are loads of icon sets in there. If this one isn't thrilling you, then try another in the same way. But before we declare this table done, there's a big decision to make about filters.

Excel Worksheets: The Limitations of the Sort Filter

You could add a sort filter, as you did with the Averages table. Unfortunately a limitation of Excel is that you can only have one of these filters on any single worksheet. There is a work-around using macros, but that's a bigger job, so a tip for another day. For now, just decide which table you wish to have the handy button filter on. The other can be sorted manually by highlighting it and using 'sort and filter' to arrange it.

NB Do not sort the columns headed 'Average' and 'Average Minus 1st/Last', else it will lose the formatting.

I've opted to put the button filter on the XP table.

Two Runescape Hiscore Tracker Tables in Excel

There we have it. You now have two lovely and useful tables, nestled side by side on your Excel worksheet.

Next time, we'll look at how to add Activity Logs and more.