Archive
Using Windows Phone and Excel to monitor blood glucose
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:
- Locates the last row in the data worksheet
- Opens the simple recording worksheet
- Locates the range of data
- Copies that data and pastes it into the next available row on the main data worksheet.
- Deletes all the data from the simple worksheet.
- 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.
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?
Carl Sprake 