Another collection of mini-posts based on recent IT trials and tribulations…
Excel tips to display the worksheet name in a cell and to validate data
Last week, I was working on an Excel spreadsheet that acts as a plan for a series of tests. Each sheet has the same format, with some conditional formatting and associated logic to total up passes/fails and give a RAG score for the sheet. Those RAG scores are presented in an overview page – and data is copied between cells so that information is only populated once but appears on every sheet. I’m quite pleased with the result but I did need to work a little on some of the tricks.
Firstly, data validation in lists (for the pass/fail). This is fairly straightforward but I usually forget how to do it so it’s worth reading the TechNet Productivity Hub post on restricting data entry in Excel with lists.
The second trick was to read the name of each worksheet and use that information in a cell (so I could name a worksheet after a set of tests, and see that name displayed as a header on the page too). Here, the SuperUser site came to the rescue and the code I needed in the cell was:
Every now and again, I find myself wanting to create a PDF from an email, just to upload a receipt to Xero (the expenses system I use at work). Unfortunately Xero isn’t happy with XPS files – and Windows 8.1/Outlook don’t create PDFs, but a quick Internet search turned up XPS2PDF, a simple, fast and apparently secure way to convert my files. There’s an API for those who want to make the conversion programmatically too.
A few more short mini-posts from the items that have been cluttering my browser tabs this week…
Lync/Skype for Business meetings start in the Web App
A few days ago, a colleague highlighted to me that, whenever she joined a Lync meeting from our company, it opened in Lync Web App, rather than using the full client. Yesterday I noticed the same – I tried to join a call hosted by Microsoft and the Skype for Business Web App launched, rather that the Lync client installed on my PC. It turns out that this behaviour is driven by the default browser: mine is Chrome and my colleague was also using something that’s not IE. Quite why I’d not seen this before, I don’t know (unless it’s related to a recent update) but for internal Lync meetings I do tend to use the Join Online button in the meeting reminder – that doesn’t seem to appear for external meetings. Of course, you can also control which client is used by editing the URL…
Using Bitlocker on drives without TPM
When my wife asked me to encrypt the hard drive on her PC, I was pleased to be able to say “no need to buy anything, we can use Bitlocker – it’s built into Windows”. Unfortunately, when I tried to enable it, I found that her PC doesn’t have a trusted platform module (TPM) chip. I was pretty sure I’d worked around that in the past, with a netbook that I used to run Windows 7 on and, sure enough, found a How To Geek article on How To Use BitLocker on Drives without TPM. It’s been a while since I had to dive into the Local Computer Policy but a simple tweak to the “Require additional authentication at startup” item under Computer Configuration\Administrative Templates\Windows Components\Bit Locker Drive Encryption\Operating System Drives was all it took to let Windows encrypt the drive.
Finding my files in Finder
One of the challenges I have with the Mac I bought a few months ago, is that modern versions of OS X seem to want to hide things from me. I’m a “browse the hard drive to find my files” kind of guy, and it took a tweak to the Finder preferences to show my Hard Disk and bring back the shortcut to Pictures.
MyFitnessPal streak ends – counter reset
Last weekend some connectivity issues, combined with staying away with friends meant I missed the cut-off for logging my food/exercise with MyFitnessPal and my “streak” was reset (i.e. the login counter). Knowing that I’ve been logging activity for a certain number of days is a surprisingly motivational piece of information but it turns out you can get it reset using the counter reset tool (which even predicted how many days the value should be – 81 in my case).
Some snippets that were too small for a blog post of their own…
Directly embedding images from Dropbox
There’s little doubt in my mind that Dropbox is a useful service with an excellent sync client and I use it extensively (alongside the consumer version of Microsoft OneDrive, although I’ve given up on OneDrive for Business). Recently, I’ve found myself wanting to embed images held on Dropbox within forum posts. Unfortunately, the link given out when sharing a file doesn’t work for embedding; however, as Canton Becker notes, if you replace ?dl=0 with ?raw=1 on the end of the URL, the link will work for an embedded image (more details on the Dropbox website).
Not only does MyFitnessPal seem to have a decent UK food database (albeit one that could do with some tidying up for consistency in naming – although that’s probably just my pedantry again) but the app is pretty good (just as good as the MyNetDiary app I paid money for…) and, more importantly, the ecosystem of connected apps is pretty good (Strava and Fitbit are both there, which is what I need – but many more besides). It’s growing too; only yesterday Endomondo emailed to say they were joining the “Under Armour Connected Fitness suite”, which includes MyFitnessPal. The only slight downside (and it’s really not an issue when I think about the data that I need to keep, long term), is that MyFitnessPal bundles up each meal into a summary when it passes it to Fitbit:
So, this is what my activity tracking ecosystem looks like now:
Ultimately, I only have to enter or capture each item of information once (exercise via Strava unless automatically captured on my Garmin Edge 810 cycle computer; food/drink/weight via MyFitnessPal; daily activity/calorie burn automatically from my Fitbit Charge HR) and it flows into Fitbit and onwards each day to Microsoft HealthVault.
I’ve been doing a fair amount of work with Office 365 in recent months (including passing certification exams) and, along the way, I’ve found a lot of snippets of useful information. Normally I’d write a blog post but I expect to be constantly adding to the information so I thought I’d create a different solution this time.
So, I’ve started to create what’s currently known as Mark’s Office 365 Resource Centre. It’s work in progress – and I’m sure the structure will change as it grows over time – but at least I’ve found something to do with the public website on my Office 365 subscription!
Over the last few weeks, I’ve found myself making quite a few conversions of maps between different formats, for different uses (mostly cycling-related). The things I’ve found might, or might not, be useful to others… so I’m writing them down anyway!
Firstly, I wanted to see what the profile of a route was like. I had a GPX file for the route and used the excellent GPS Visualizer site to create an elevation profile. And then quickly decided it had far too many bumps!
Finally, I bought a Garmin Edge 810 (cycle computer). After months of saying “I don’t need a Garmin, I have Strava on my iPhone”), I gave in. And I’ve been pretty glad of it too – already it’s been great to monitor my stats as I climbed Holme Moss last week (does 98% maximum heart rate mean I’m 2% off a heart attack? </joke>) and last weekend I decided I was 20-odd miles from home and bored of my ride, so the sat-nav could show me the best way back to my starting point (even if it did mean cycling along some trunk routes…). Added to that, Mrs W has been glad to track my rides using the Garmin 810’s Live Tracking (although it didn’t work last time I was out…).
The Garmin comes with “base maps” but these are really just the main roads. As they’re probably not the ones you want to use for cycling, it’s handy to load on some more detail. Ordnance Survey 1:50K maps (GB Discoverer) may be great (the 3D view in particular) but at a penny shy of £200 I wasn’t prepared to pay that much, with Open Street Maps available for free. ScarletFire Cycling has made a video with an interesting comparison of the OSM and OS map options:
I can’t believe that the quarterly Milton Keynes Geek Night is nearly upon us again. I usually try to blog about the evening but I’ve failed spectacularly on recent attempts. I might fail again with this week’s MKGN – not because I’m slow to get a blog post up but because the tickets “sold” out in something crazy like 2 minutes…
September’s Geek Night was up to the usual high standard (including the return of David Hughes – seems you can’t escape that easily!) but included one talk in particular that stood out above all of the others, when Ben Foxall (@BenjaminBenBen) showed us (literally) the other side of responsiveness… but we’ll come back to that in a moment.
Back to front performance
First up was Drew McLellan (@DrewM)’s take on “back to front” performance. You can catch the whole talk on Soundcloud but for me, as someone who runs a fairly shoddy WordPress site, it got me thinking about how performance is not just about optimising the user experience but also about the back end – perhaps summed up in one of the first points that Drew made:
“Website performance is about how your site feels.”
That may be obvious but how many times have you heard people taking about optimisation of one part of a site in isolation, without considering the whole picture. As Drew highlighted, performance is a feature to build in – not a problem to fix – and it’s also factored into search engine algorithms.
Whilst many performance gains can be found by optimising the “front-end” (i.e. Browser-side), there are some “back-end” changes that should be considered – sites need to be super-fast under normal load in order to be responsive under heavy load (quite simply, simultaneous requests affect responsiveness – they use memory and the quicker you can process pages and release memory, the better!).
First up, consider hosting. Drew’s advice was:
Cheap hosting is expensive (shared hosting is cheap for a reason).
Shared hosting is the worst (rarely fast) – think about a virtualised or dedicated server solution instead. Constrain by CPU, then RAM, not disk space (that should be a red flag – it’s cheap, if not much is allocated it shows lots of people crammed on a server).
Consider what your project has cost to build when buying hosting! Use the best you can afford – and if they advertise with scantily clad ladies, they’re probably not very good (or to be encouraged)
Next, the content management system (CMS), where Drew says:
Think about the cost of external resources (going to database or web API, for example). Often these are necessary costs but can be reduced with careful architecture.
Employ DRY coding (don’t repeat yourself) – make sure everything only has a single representation in code. Do things once, cache and reuse (unless you expect different results). For example, if something doesn’t change often (e.g. post count by category on a blog), don’t calculate this on every page serve – instead consider calculating when adding/removing a post or category (called denormalisation in database terms)… be smart – consider how real-time is the data? And are people making decisions using this data?
Do the work once – “premature optimization is the root of all evil” is actually a quote from 1974, when line-by-line optimisation was necessary. Focus on the bottlenecks: “premature” should not be confused with “early” – if you know something will be a bottleneck, optimisation is not premature, it’s sensible.
Some frameworks focus on convention over configuration (code works things out, reduces developer decisions) – can lead to non-DRY code – so let’s make programming fun and allow the developer to work out the best way instead of burning CPU cycles. “Insanity is doing the same thing over and over again and expecting different results”.
The Varnish caching HTTP reverse proxy may be something to consider to speed up web site (unfortunately Drew ran out of time to tell us more – and my hosting provided found it caused problems for some other customers, so had to remove it after giving it a try for me)
Great talk at #MKGN from @drewm on performance. Front-end page-speed is nothing without good hosting and an efficient back-end.
The first of the five-minute talks was from Christian Senior (@senoir – note the spelling of the Twitter handle, it’s senoir not senior!). Christian spoke about managing client expectations. Whilst my notes from Christian’s talk are pretty brief (it was only 5 minutes after all) it certainly struck a chord, even with an infrastructure guy like me.
Often, the difficult part is getting a client to understand what they are getting for their money (“after all, how hard can it really be?”, they ask!) – but key to that is understanding the customer’s requirements and making sure that’s what your service delivers. Right from the first encounter, find out about the customer (not just who they are, what want, how much money they will spend – but browsers, devices available, etc.) and try to include that detail in a brief – the small things count too and can be deliverables (incidentally, it can be just as important to distinguish the non-deliverables as the deliverables). Most of all, don’t take things for granted. My favourite point of the talk though, was “talk to customers in a language they understand!”:
Or, to put it another way:
“Work in code, not talk in code!”
Great 5 min talk from @senoir about managing clients expectations, something I feel is important to a successful project #MKGN
As I mentioned in my introduction, Ben Foxall (@BenjaminBenBen)’s five minute talk on “the other side” of responsive design was nothing short of stunning. If I ever manage to deliver a presentation that’s half as innovative as this, I’ll be a happy man. Unfortunately, I’m not sure I can do it justice in words but, as we know from Sarah Parmenter (@Sazzy)’s talk at MK Geek Night 5, responsive websites provide the same content, constructed in different ways to serve to multiple devices appropriately.
Ben got us all to go to a site, which reacted according to our devices.
He then showed how the site responded differently on a phone or a PC – choose a file from a PC, or take a photo on a phone.
He tweeted that photo.
He showed us the device capabilities (i.e. the available APIs).
He updated his “slides” (in HTML5, of course), interactively.
And projected those slides in our browsers (via the link we all blindly clicked).
In summary, Ben wrapped up by saying that “responsiveness and the web needs to use the capabilities of all the devices and push the boundaries to do interesting things”. If only more “responsive” designers pushed those boundaries…
Following Ben’s talk was always going to be a tough gig. I’m not sure that I really grokked Tom Underhill (@imeatingworms)’s “Work in Progress” although the gist seemed to be that technology gallops on and that we’re in a state of constant evolution with new tools, programs, apps, books, articles, courses, posts, people to follow (or not follow), etc., etc.
Whilst the fundamentals of human behaviour haven’t changed, what’s going on around us have – now we need more than just food and warmth – we “need” desktops, laptops, smartphones, pink smartphones, smart watches. Who knows what’s in the future in a world of continued change…
Constant change is guaranteed – in technology, social context and more. Tech is a great enabler, it could be seen as essential – but should never replace the message. Brands, experiences and products change lives based on the fundamentals of need.
Not sure I understood much of @imeatingworms's "hierarchy of needs" but I'm sure others who are more intelligent than I did! #MKGN
The one minute talks were the usual mixed bag of shout-outs for jobs at various local agencies (anyone want to employ an ex-infrastructure architect who manages a team and really would like to do something exciting again… maybe something “webby”?), Code Club, the first meeting of Leamington Geeks, and upcoming conferences.
Fear, uncertainty and doubt
The final keynote was from Paul Robert Lloyd (@paulrobertlloyd), speaking on FUD – fear, uncertainty and doubt. Paul makes the point that these are all real human emotions – and asks what the consequences of abusing them are. He suggests that the web has been hijacked by commercial interests – not only monitoring behaviour but manipulating it too.
Some of the highlights from Paul’s talk make quite a reading list (one that I have in Pocket and will hopefully get around to one day):
As the web is largely unregulated, it’s attractive to those who want to increase their personal wealth; so we have to be optimistic that there are enough people working in the tech sector with a moral compass. Arguably, the Snowden leaks show that some people have integrity and courage. But Paul is uncertain that Silicon Valley is healthy – “normal” people don’t see customers as data points against which to test designs – for example a team at Google couldn’t decide on shade of blue so they tested 41 shades (and border widths). Paul also made the point that the team was working under Marissa Mayer – for a more recent example witness the Yahoo! logo changes…
Then there are the “evil” social networks where, as Charles Stross highlights, “Klout operates under American privacy law, or rather, the lack of it”.
Paul says that The Valley operates in a bubble – and that Americans (or at least startups) skew to the workaholic side of things, viewing weekends off as a privilege not a right. He also suggests that the problem is partly a lack of diversity – The Valley is basically a bunch of Stanford guys making things to fix their own problems. Very few start from a social problem and work backwards – so very few are enhancing society; they’re making widgets or enhancing what already exists. Funding can be an issue but governments are seeing the tech sector as an area of rapid growth and it’s probably good not to be aligned to a sector where you can launch start-ups without a business case!
Lanyrd shows that it is possible to start up outside The Valley (although they have been bought by Eventbrite so have to move) [TweetDeck is another example, although bought by Twitter] but Silicon Valley arrived by a series of happy accidents and good luck/fortune – it’s important that the new tech hubs shouldn’t be a facsimile of this.
Then there’s protecting out data from Governments. Although conducted before the Snowden leaks the Electronic Frontier Foundation’s annual survey asks “who has your back?” – and, although it’s still young, it seems companies are starting to take notice.
Choose your services wisely – we (the geeks) are early adopters – and we can stop using social networks too. It’s easier to change services if data can be exported – but all too often that’s not the case so you need to own your own content.
I consider iPhones to be overpriced but as I’ve never owned an Android phone I couldn’t really advise on alternatives. We did spend some time looking around in Carphone Warehouse but I had to leave Mrs W to it for fear of what I might say when the salesperson’s ill-informed “advice” got too much.
We liked the look of the HTC One X, but Mrs W was put off by some reviews on the ‘net (moral, never read reviews from non-tech writers…); some of the Samsungs looked OK too but whilst the form factor may have worked for my “man hands”, those of a daintier persuasion may find a large screen just a little too… large.
So, why not get an iPhone? After all, most of her friends have one. I’ve had one since the UK launch, either for personal use or for work. And “my” iPad seems to have become the family iPad now. I guess that means we’re pretty much an iOS household already.
But I still baulked at the price.
Buying second hand
Then, one of our friends mentioned smartfonestore.com. After waiting a few weeks for stock to come in, I managed to pick up a 16GB SIM-unlocked iPhone 4S for around £265, in mint (grade A) condition. Two days later and I had an SMS from Mrs W. saying how she loved her iPhone (yes, “love” was the emotion expressed by this non-geek consumer).
Ever since then, I’ve been admiring the 4S from afar, trying to convince myself that my Lumia 800 was better and that I could make do with the company-supplied 3GS. I tried to get myself another Grade A iPhone 4S but then, in a moment of weakness, I jumped for a Grade B condition unit instead. To be honest, when it arrived last Saturday, I couldn’t tell the difference!
SmartfoneStore send out the phones in their own packaging and both the iPhones I’ve purchased both came with a USB-dock cable and a SIM extraction tool. Mine also came with a screen protector and a bumper (although it’s not the Apple version, and not a great fit). I’ve yet to use the other side of the business (fonebank.com) but it may well be the destination for recycling my Lumia 800).
So now, we really are an iOS family. A 3GS, two 4Ss and an iPad.
(OK, so technically the 3GS is not mine – and I am considering replacing that with a cheap HTC Desire so that I can have a play with Android – but there are plenty of iDevices to go around…)
I’ve been impressed by SmartfoneStore (and remember, they were recommended to us by a friend too) so, if you’re in the market for a second-hand smartphone, they might be worth a look. Unfortunately there is no waiting list for a particular model to come into stock but you can register for notifications and soon enough, you should find something that suits for a lot less than the cost of a new handset.