Make Vaccine Scheduling "Scheduler Friendly"

Every family I know has (at least) that one person who has to have the planning calendar, and that other person who keeps track of all the important documents, and that other person that they call on for healthcare related stuff, and finally, the computer geek.  And they may all reside in the same person.  One of these is very likely the COVID-19 vaccine scheduler.

As I think about how vaccines are opening up, and my own experience in Massachusetts scheduling vaccines for my family, here are some of my experiences:

1. I have to enter the same insurance data each time for each different person I'm making an appointment for.  If only there was a standard for the layout and OCR font for insurance cards, or better yet, even a standard bar-code or QR format for insurance information, it could have made my life so much easier.
   
   
2. I could never schedule more than one person at the same time, even if there are two or three qualifying individuals that I need to schedule for at the same time (and appointments open).  This resulted in me making 2 or 3 different appointments for a two groups of people who each had to travel over 30 minutes to a total of 5 different locations during two different enrollment periods. In one case, I fat fingered the first appointment date, which meant I had to reschedule one of the appointments, which led to a three week delay in getting a replacement appointment.

I've seen six different scheduling interfaces (four for drug-stores, two for my state public health sites), not one of them is really designed for the person in the family who does the scheduling for most of the family members.  These same changes could readily enable others who volunteer to assist others in scheduling work more efficiently.

There are balancing factors. Making it easy for one person to schedule multiple appointments at the same time and location would benefit families, but single individuals living alone would be disadvantaged by such a system.  But if there are enough vaccines (and appointments) to go around, this would be less of a problem.

We're likely going to be scheduling shots for some time yet.  We've only gotten shots into the arms of about half of the US population, and these aren't likely to be the last COVID-19 shots that have to be given.  Booster shots are expected by some vaccine manufacturers.

Recovering Deleted Data in an Excel Tab (When you have a PivotTable)

 Life happens sometimes. Is this you?

1. You leave multiple programs running because you have a few meetings to get through for the day.
2. You hope to get back to that spreadsheet you started working on in the early morning.  
3. You operate Excel pretty well using the keyboard, and touch type in it and other programs.
4. Eventually, you get to the next day, discover you never got back to the spreadsheet, and so close and save it promising you will finish it up later that day.
5. You reopen it later and find out the tab you were working on was missing.
6. Because you downloaded it and never turned on "version control", you don't have the old version.
7. Or a copy of what you worked on (and the changes are to recently made to have a backup).
8. But it DOES have a pivot table summarizing the data in your missing tab.
9. Somewhere during your calls you hit both Delete and Enter at the same time and managed to delete the Excel sheet b/c you were typing and talking at the same time, and paying attention to the Zoom call, and didn't see that you'd deleted the tab you just spend hours working through.

This post may save your data, but only if you are a bit of a computer geek and know a little something about XML. Using the techniques below managed to save mine.

What you may not know is that:

• Later version of Microsoft Office tools, including Excel use the ZIP format to store the collection of XML files they use to store data in the new formats.  
• Whenever you create a Pivot Table in Excel from data in your sheet (or from other data sources), Excel caches a snapshot of the data for local processing, and only refreshes from the original source when you Refresh the Pivot Table.  If you DON'T Refresh the Data in the Pivot Table, it's still in your spreadsheet.

Here is what you need to do:

1. Make a copy of the affected file in a new work folder.
2. If the copied file is in .XLS format, open it in Excel, and Save as a .XLSX.  This will simply change the file format, the data you need will still be in it, but now in a format that can be more readily accessed.
3. Rename the file from *.XLSX to *.ZIP.

Next, look for xl/pivotCache/pivotCacheDefinition1.xml and xl/pivotCache/pivotCacheRecords1.xml in the ZIP file.  If you have more than one Pivot Table, you might need to look at the files ending in a different number.

The pivotCacheDefinition file contains the column heading names in the cacheField element.

You can verify the data source in the worksheetSource element.

The pivotCacheRecords file contains the rows of the sheet in the <r> elements.

Empty cells are reported in <m/> elements.

The values are found in the v attribute of elements named <b> (boolean), <d> (date), <e> (error), <n> (numeric) and <s> (string).

Some elements where the values are repeated a lot use <x>, in which case the v attribute indicates the index of the sharedItems found in the cacheField element in the pivotCacheDefinition file.  This gets a little bit complicated.

Assuming you've extracted those two files, the following XSLT will regenerate the sheet in CSV format when run over the extracted pivotCacheRecords.xml file.

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns:xs="http://www.w3.org/2001/XMLSchema"

    exclude-result-prefixes="xs"

    xmlns:m="http://schemas.openxmlformats.org/spreadsheetml/2006/main"

    version="2.0">

    <xsl:output  method="text"/>

    <xsl:variable name="doc" select="doc('pivotCacheDefinition1.xml')"/>

    <xsl:template match='/'>

        <xsl:for-each select='$doc//*:cacheField/@name'>

            <xsl:if test='position()!=1'>,</xsl:if>

            <xsl:value-of select='.'/>

        </xsl:for-each>

        <xsl:text>&#xA;</xsl:text>

        <xsl:for-each select='.//m:r'>

            <xsl:for-each select='*'>

                <xsl:if test='position()!=1'>,</xsl:if>

                <xsl:variable name="pos" select="position()"/>

                <xsl:choose>

                    <xsl:when test="self::m:m"></xsl:when>

                    <xsl:when test="self::m:x">

                        <xsl:variable name="v" select="@v + 1"/>

                        <xsl:variable name="x" select="$doc//m:cacheField[$pos]/m:sharedItems/*[$v]/@v"/>

                        <xsl:if test='$x'>"</xsl:if>

                        <xsl:value-of select="replace($x,'&quot;','&quot;&quot;')"/>

                        <xsl:if test='$x'>"</xsl:if>

                    </xsl:when>

                    <xsl:otherwise>

                        <xsl:text>"</xsl:text>

                        <xsl:value-of select="replace(@v,'&quot;','&quot;&quot;')"/>

                        <xsl:text>"</xsl:text>

                    </xsl:otherwise>

                </xsl:choose>

            </xsl:for-each>

            <xsl:text>&#xA;</xsl:text>

        </xsl:for-each>

    </xsl:template>

</xsl:stylesheet>

From Risk to Opportunity

If you've been in healthcare or IT for a while, you've probably heard about Risk Assessments.  And if you have been through a few of these, you might recall the process of:

1. Identifying assets to protect
2. Enumerating threats to those assets in a list of risks
3. Assessing the likelihood of the risk happening
4. Assessing the level of impact of the risk (i.e., how bad it would be if the threat occurred)
5. Using the categories values from #3 and #4 to assess the risk level (to understand what needs to be mitigated) using something like the matrices below:

Several resources are available from HL7 and IHE to do these tasks, including the HL7 Security Cookbook and the the IHE Risk Management in Healthcare IT Whitepaper (from which the two images above were drawn).

But I'm not really here to talk about Risk Assessment.  I'm going to the other end of the spectrum to talk about how you can use this same framework to prioritize efforts for opportunities, and it works pretty much the same way.

1. Identify the assets to capitalize on (assets being used very loosely here, it could include processes and skills that you are good at, as well as the usual notion of assets).
2. Identify the value of those assets in a list of opportunities.
3. Assessing the likelihood of the opportunity succeeding.
4. Assessing the level of impact of the opportunity (e.g., ROI).
5. Assessing the importance of the opportunity.

The same way a risk assessment helps to identify the risks to mitigate, an opportunity assessment can help you identify opportunities to explore further, and skip the ones which are of lower importance.  You could also replace "likelihood" with "cost", where cost is essentially a proxy for likelihood, but high cost is equivalent to low likelihood, and so you'd have to flip one axis of the grid.

The number of levels of opportunity (or risk for that matter) that one puts in the grid is really up to the organization performing the assessment, I'd recommend using at least 3 and no more than 5.

The benefit of using this framework for assessing opportunities is very similar to the befit for following it for risk assessments.  It puts a structure around the work that you are doing, and adds some degree of objectivity to the assessment process.  It still requires judgement (and that may be subjective), but the results will give you more insight and confidence in the outcomes.

For what it's worth, this isn't my idea.  I THINK I first heard about this from Gila Pyke about seven or eight years ago, probably over Sushi somewhere at an HL7 or IHE meeting.