Using Windows Phone and Excel to monitor blood glucose

Diabetes record

Last Friday was the last day I was in the office, before the holiday season. On the spur of the moment I went out for lunch with a couple of the other consultants and the guys from the Service Desk. I was struck by how little I knew these guys and vice versa when I tested my blood sugar when my mean arrived. None of them knew that I was diabetic!

One of them, Marko, who has been with us for a year, commented on the fact that I was making a note of my blood sugar in a good old fashioned little notebook. As an IT consultant he thought I should be adopting a more technical approach. And that got me thinking.

I do use an Excel workbook to log my blood sugar readings and insulin doses. I did this regularly when I was first diagnosed and for the first few years after that. But then I got lazy and just made a note on a piece of paper, that hardly ever got entered into a spreadsheet. Over the last year though I have got back into the habit, mainly because I want to get my D1 licence reinstated and then get an FMT 600 so that I can drive cadets round.

So I have an excel workbook. Could I then update this workbook using my Windows phone? It would seem not. While I can open the file on the phone I cannot edit it. A quick Google suggests that this is because the workbook contains formulas or formatting that the phone can’t handle. Quite probably as there are lots of formulas and pivot tables.

So, the next question was: Can I use a simple spreadsheet and copy the data across. Answer: Yes I can. I have a simple, single sheet Excel workbook where I collect the following information:

Month

Date

Time

Blood sugar

Novorapid dose

Levemir dose

Data types

Month

An integer from 1 to 12

Date

An integer from 1 to 31

Time

A decimal that represents the time.

When constructing the original spreadsheet I found that entering times as times was a pain as I needed to use a colon (:) to separate the hours and the minutes, rather than using a period (.), which was more convenient. I therefore constructed a formula that enabled me to enter the time using a period. If the time is in cell 1504 then the formula shown will convert a decimal such as 12.45 into a time such as 12:45:

=TIME(INT(Data!$E1504),100*(Data!$E1504-INT(Data!$E1504)),0)

(where Data is the name of the sheet).

To make life simpler still I record the time to the nearest quarter of an hour (00, 15, 30, 45).

Blood Sugar

My blood sugar reading in mmol / l. A decimal value.

Novorapid dose

I inject Novorapid before each meal. I adjust the dose based on my blood sugar reading and my expected carbohydrate load.

Levemir dose

I inject Levemir at night. I keep the dose regular. Currently I inject 50 units.

Transferring the data

I use my phone to collect this data and the file is stored online using my OneDrive.

The main blood sugar record file is also stored on OneDrive so I can access either of them from wherever I am.

I can them copy the latest data from the simple worksheet to the main record.

To make matters even smoother, I have created a macro that runs from the main record. This code:

1. Locates the last row in the data worksheet
2. Opens the simple recording worksheet
3. Locates the range of data
4. Copies that data and pastes it into the next available row on the main data worksheet.
5. Deletes all the data from the simple worksheet.
6. Saves and closes the simple worksheet.

Analysis

An overview of the analysis done by my diabetes record:

Formulas look at the date and time and work out whether the reading refers to:

• AM or PM
• Weekend or Weekday
• Which weekday

Pivot tables are then used to plot:

• Average blood sugar and Novorapid dose month on month
• Average blood sugar and Novorapid dose by weekday
• Distribution of blood sugar readings

Slicers are used to further filter the data.

Next fundraising ideas

I’m looking for something to inspire me, something to aim for, a reason for doing something. Ideally I would like to do something that I will enjoy, while being challenged, and at the same time raising money for one or more charities. Obviously, as a Type 1 diabetic I would like to raise money for Diabetes UK, but I was wondering how much to aim for.

The answer came when it transpired that a delegate on one of my training courses volunteers for the Mountain Rescue. I started to wonder how much it costs to run a Mountain Rescue team.

The answer would appear to be along the following lines:

To kit out one person: £1709

To run a team for a year: £25000

Cockermouth MRT had 49 call outs in 2011 compared to the 80 in 2010.

So that works out to be £312 – £510 per call out.

If I balance the contribution between Diabetes UK and MRT that works out to be a target of £1000. That should be achievable.

Objective

Wainwright wrote seven guides to the Lake District. A walk that connects the highest point in each book would make a good route from the north of the Lake District to Windermere.

Stages

Every journey has a beginning, a middle and an end. For this one:

• The Beginning
  Travel from Oxford to Aspatria, between Carlisle and Cockermouth
• The Middle
  Walk from Aspatria to Windermere
• The End
  Travel from Windermere back to Oxford

There and Back

The obvious way to get there and back is by train. Buses will take too long and driving will require two vehicles and other people. The only down side with the train is the cost – I wonder if I can get Virgin trains to sponsor me somehow?

The Middle Bit

As for the route itself the main points of interest will be:

• Skiddaw
• Grasmoor
• Great Gable
• Scafell Pike
• High Raise
• Helvellyn
• High Street

I’ve contacted Diabetes UK and they will help me with fundraising. Now to set dates and start planning….

Doctor’s Appointment

As a Type 1 diabetic I need to have a medical check up every six months. This means an appointment with a phlebotomist to take a blood sample, followed  few weeks later by an appointment with the practice nurse. Now, I used to have the check ups done at the diabetes center at the hospital. However, I managed to get the health center to agree to do them after I got fed up with waiting up to an hour to be seen at the hospital, even when I had made sure that I was the first person in the queue that morning.

So, I had all the appointments sorted out for the beginning of the new year with a phlebotomist appointment on the 4th Jan, followed by an appointment with the practice nurse a few weeks later. However, a colleague at work asked if I could move the appointments as there was an urgent piece of work that a client wanted to do, and they wanted to start on the 4th Jan. So, being the nice guy that I am, I moved my appointments.

Guess what? The work slipped a week and I didn’t have to do anything on the 4th, so I could have kept my appointment!!

Anyway, all of these shaningans meant that I had a new pair of appointments arranged. I went and had my blood taken by the phlebotomist and was hoping to see the nurse a few weeks later. However, more work came up and I had to move that appointment. So I rang the surgery to make any appointment for the nurse on the one day (a Thursday) that I was available. I told the receptionist that I was only available on that Thursday but she still went through “can you make Tuesday, Wednesday…?”

“No!! That why I said I was only available on the Thursday”. Which bit of that did you not understand?

So, she was going to make the appointment for the Thursday and then asked “Its not for a diabetes check up is it?”

So I said it was, as if that would make a difference.

“Oh the practice nurse only sees diabetes check ups on a Wednesday”. I knew that I would never win an argument against the bureacracy of the NHS so agreed to aWednesday appointment. The receptionist then asked about a blood test, and I told her that I had had the blood taken a couple of weeks ago.

“Oh thats no good”, said the receptionist. “The blood test needs to be done a couple of weeks before the appointment”. So I had to make another appointment for the blood test.

Grrrrrr!!!

What I want to know is, are the NHS going to reimburse me for lost earnings for the extra day needed for another blood test?