BEGINNERS GUIDE TO EXPORTING EMIS DATA TO EXCEL - DOC by donovantatehe

VIEWS: 24 PAGES: 12

									BEGINNERS GUIDE TO EXPORTING EMIS DATA FROM
           LV TO MICROSOFT EXCEL

               Dr Arun Aggarwal, GP, Ramsey, Cambridgeshire
                             arun.aggarwal@gp-d81087.nhs.uk


     OVERVIEW
     Ensure LV is set to have Excel as default spreadsheet on the PC you
      intend to work from.
     Build or use a simple search population- e.g. all diabetics.
     In search results, build and save a report of all the coded or
      numerical data you are interested in.
     Run it and print the report to Excel, accepting all the defaults
      offered.
     Give each column in the spreadsheet a unique label on the first row.
      Do not leave any blank.
     Use filters to home in on patients whose care could be improved.
     Use macros to tag their records using simple messages in consultation
      record, prescribing record or possibly the diary, to close the loop
     Use pivot tables to get an idea on quantitative comparisons, and more
      detailed powerful analyses.
     Save the data set and combine it with future years to get progress
      reports.
     Share anonymised data across PCOs to get useful peer group
      comparisons.
     Ensure that computerised data increasingly reflects the actual care
      your team are giving.



It is surprisingly easy to extract lots of interesting and useful information
about your patients from EMIS and manipulate it in Microsoft Excel once you
know how to carry out a few simple procedures. By the time you have read this
article you should be able to create complex reports on defined groups of
patients such as all your diabetics and analyze the results in Excel to answer
most of your practice audit requirements.




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel        1 of 12
GETTING THE DATA OUT OF THE PATIENTS’ RECORDS AND
INTO EXCEL- example focussed on diabetes


Set Up Microsoft Excel As The Default Spreadsheet
The purpose of this step is to enable LV to export search and audit reports
directly into a new Excel spreadsheet. Check that LV is set to work with Excel
as its default spreadsheet. Select Settings in the LV menu bar at the top of
the screen, click Advanced | Spreadsheet | Search. Type in Excel in the dialog
box and click Find. Double click “Excel.exe” once you have found it. Click Close.
Create/Check/Update Your Diabetes Register
You may already have a search for all your diabetic patients. If not, create one.
Form the Main Menu:

1. <ST> Search and Statistics
2. <B> Patients Searches
3. <A> Add a search (on today’s practice population)
4. <A> Add a feature
5. <C10> - is the Read Code for diabetes mellitus (presuming that all your
   patients with diabetes have this code in their records). No date range.
   Shared
6. <Return> - for no more features
7. Are these features correct? <Yes>
8. Give the search a meaningful title e.g. Diabetes Register. Store the search
   in the regular search directory or a diabetes folder of searches
9. Run the search now? <Yes>
10. When the search is complete go to Search Results, find the search in the
    directory where you stored it and choose the Age-sex table option to get an
    idea if the numbers of patients found in the search are about right.

You should have between 2 and 3% of your list in the diabetes population, unless
you have many students (lower-1.5%), Asians (up to 5-6%) or elderly. You may
have an under-estimate of diet-controlled diabetics, as they can slip through the
coding nets more easily.
You can double check the accuracy of the register by running a separate search
to look for patients taking diabetic drugs. Create a new search based on current
medication, drug group, either all drugs used for diabetes or diagnostic reagents



Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel           2 of 12
used for diabetes testing (use the O operator for either/or). Give it a
different name, e.g. “Diabetic treatment register” and check the numbers in the
same way.
Compare the total numbers found in the two different searches.
A slightly more sophisticated method will help identify those “on treatment” not
yet coded. Add a feature to the drug-based search: the C10 diabetes feature,
without a date range, but use the excluded operator rather than the shared one
this time. Run the search. Look at the results. Create a file to browse with
(option C) and toggle through the list of patients on medication but not yet
coded, using the F7 group key. The results may be zero, or thought provokingly
large. Save adding the code till later when you might usefully complete the
whole template of other data to add for these patients. See my article on
Closing the Audit Loop.

Building A Simple Export Report
This is how to build an export report containing the systolic blood pressure of
each patient found in a search:
   <S>.Search results. Choose a search.
   <F> Names and addresses and aspects of file.
LV prompts you to choose which data to export from the records of the patients
found in the search. I suggest
        EMIS computer number (to identify the data with individual patients)
        Name(s) (if only for use in practice but not if the report will be seen
         by anyone else!)- first, surname and telephone number
        Age in Years (to remove multiple duplicates – I will explain later)
        TIP- you can make a macro which always sets the above fields
        <D> Clinical aspects of record. Choose the Read term Systolic blood
         pressure (2469.).
        Choose which aspects of the Read code you want to export. Don’t
         export too much unless it will be useful. Take care in: selecting date
         ranges and note that dates can be into future e.g. earliest 1 1 06-
         latest 1 1 08 displaying all or just the latest entry (the aim is just to
         get a single entry in each cell in the spreadsheet with all columns lined
         up for successive patients, so I recommend latest only, almost always)
         and displaying the dates of Read code (often unnecessary, especially if
         the date range is short) and values.




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel              3 of 12
           One category of data to export (e.g. SBP) could lead to one or two
           columns of data transferred (e.g. a latest value and date) or many (lots
           of values and dates, the word “systolic BP”, the code “2469”).
        Once done, save the collection <F8> with an EMIS type of file name (up
         to approx 30 characters long) e.g. “BP in DM 2006. This allows you to
         reuse or edit the aspect set later.
        Now the magic starts. Press <P> to print or export. Choose the
         destination to be Excel (c), meaning a comma-separated file which
         Excel can open. Do NOT change the comma delimiter. EMIS will
         display a message: “Creating spool file”. Be patient. It may take a
         while. You will see a series of steps involving the print spooler file
         transfer, auto launch of Excel and opening of an Excel file with your
         data all laid out in columns. Exit from the Search and Statistics
         module by repeat <F1> until you get back to the Main Menu.
Once you have created and saved a search report you can export it to a floppy
disk and share it with other practices.
MACROS The building of complex or detailed exports to Excel is made easier by
creating macros in the ST menu right hand side. I suggest one macro for the
anon ID ( EMIS number, age, sex, ) one for non-anon ID (Emis number, surname,
first name, age, sex, tel.no.), one for value data (YNNNNNNY) and one for date
data (YNNY dates NNNN). If you have mental energy and spare macros unused,
you can build specific date ranges too( calendar years or QOF years!!)!

Tidying Up The Excel Spreadsheet
Give columns headers/titles:
   1. Insert a single row above existing row 1 – highlight and right click row 1
      and click Insert. Type meaningful titles in each column of row 1 that
      contains data. Ensure every title is different and that there are no
      cells left blank in row 1. It upsets pivot tables and ranking/sorting
      operations if there are any duplicates or blanks.
   2. Make the titles bold to se them better - highlight row, then click on “B”
      on toolbar.
   3. Ensure columns wide enough to read data - click on corner cell (shaded)
      next to A1, then double click on any vertical line between the column
      labels e.g. A/B, or C/D etc. Fixes all columns in one go.
Delete duplicate patients:
   Occasionally EMIS exports patients several times if your dialog box was not
   as explicit as it might have been. If the spreadsheet is sorted in EMIS


Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel            4 of 12
   computer number or name alphabetical order, you should be able to see the
   duplicates. Select and right click the duplicated row number and click
   Delete. Try to ensure the row you are keeping has all the key data.
   Duplicated patients will not have a duplicated age or sex so you can use these
   report aspects to quickly remove lots of duplications. Sort the spreadsheet
   by age and look at those with blank ages at bottom. Or filter for blank sex or
   blank names… Decide if any important data there and then choose what to
   delete.
Save as Excel workbook
   1. Click File on the menu bar at the top of the Excel window. Choose Save
      As, (not Save).
   2. The file name is likely to be “stew” initially. Change it to something
      appropriate. Save it to the C drive in an appropriate folder such as My
      Documents (rather than the espool temp folder it is likely to default to).
   3. A window at the bottom of “Save As” dialog box allows you to change what
      type of file it is saved as, and you need to choose Excel. It is usually at
      the top of list of file types, so scroll up.


MANIPULATING AND ANALYZING IN EXCEL


Setting Up The Spreadsheet Correctly
Ensure no blank rows or columns, and that every column has a different header.
If there are whole blank columns, analyses will stop at that column. The second
row must contain the first set of patient data.
Freeze panes
You can navigate around the worksheet without losing sight of the column
headers or patient identifiers if you freeze the panes. This means that as you
scroll around the spreadsheet the columns to the left of the freeze point and
the rows above always remain visible on screen. Click in the cell immediately
below the intersection of row and column headers that you want to freeze e.g.
B2, and go to the Window | Freeze panes on the Excel menu bar.
Sorting
This re-arranges the order of the rows to fit one or more rules that you specify.
It is very useful to re-order the patient’s record in descending order of blood
pressure.




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel          5 of 12
You can quickly sort the spreadsheet on the contents of one column by selecting
just one cell in the column e.g. age, sex or value of BP and click the A-Z or Z-A
icons in top row of the Excel toolbars. Instantly sorted!!
Do NOT select a whole column before pressing the sort icons. If you do, only
that column gets sorted and you have lost the horizontal link between the data
in that column and the patient it belongs to. (Use CtrlZ to undo your last
action(s).)
To carry out multiple sorts, click on the Data item in the menu at the top of the
Excel window and click Sort. Now you can sort up to three columns in order e.g.
by sex, then by diagnosis, then by BP, or by age then smoking then SBP
Filtering
You can look at the rows of data for patients that have specific results by using
Excel’s auto-filter feature. In the Data menu click Filter and then Auto-filter.
An arrow appears against each column header. If you click on one arrow you can
select to filter on almost any characteristic or value or combination thereof
that is present in the chosen column e.g. all the blanks with missing data or all
the patients with blood pressure above target. You can customise the filter by
clicking (Custom…) in the drop down menu. You can now choose numerical ranges
e.g. SBP>141, or “either SBP>141 OR equals blank”.
Can print the filtered spreadsheet as you see it on screen (except for the
arrows): WYSIWYG (what you see is what you get).




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel          6 of 12
Figure 1: Applying a custom filter to the systolic blood pressure column

After the filter has been carried out the arrow in the column used to create the
filter goes blue. If you click this arrow again and then (All) the filter is
cancelled. You can also undo a filter or multiple filters by Data | Filter |Show
all.
By combining sorts and filters you can achieve all sorts of printed reports on
your population of patients, especially for those needing action e.g. patents with
blanks in SBP or figure over e.g. 141 or 160 or whatever you can cope with as
first priorities.
Pivot Tables
To create a pivot table from                            Age (no blanks here, so
your data ensure you are                               should be able to group e.g.
clicked in a cell within the                                20 yr age bands)
main data set. Click Data |
Pivot Table and PivotChart        Count of BP check
Report and use the wizard. It      Count of HbA1c       Count of sex or name
automatically discovers the
                                 Count of eye check
size of your data set, to last
row and the last column with a
header. Use the Layout option to construct the analysis you are interested in,


Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel            7 of 12
by clicking and dragging tiles. Start simple, and do NOT use any quantitative
data where there are blanks for some patients, as there are some special tricks
to allow grouping (insert a value that is very low, e.g. 0.1. In Office 2000
onwards, you can insert a value 0, though that will alter the ”average” function
accuracy).




Figure 2: Creating a pivot table to look at the success in monitoring diabetes for different age groups



Please note that there are lots of permutations, and best way is to explore, and
that a half day is needed to familiarise yourself with this versatile menu area.
The example below shows how you can build up more complex useful analyses, and
then also get % calculations from which graphs can be built.




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel                                   8 of 12
Figure 3: It is possible to use cell formulae to calculate percentages from the pivot tables



Double clicking on any cell in the results area will reveal all the patients the data
relates to!
nGMS Contract Comments
The need for this approach has been significantly reduced by the advent of
Population Manager, certainly for all the Contract related queries. PopMan has
brought with it a set of searches at the bottom of your search directory that
allows you to look at population subsets that meet or more importantly fail to
meet the contract requirements. It is easier to work with these populations
than to mess around directly in Population Manager with its dreaded green arrow
bar to go back! From the search results, you can both create a file to browse
which is great and easier than the green arrow, using the F7 group key, but more
impressively, you can export to excel. For example, BP5 not included population
includes patients who are just above 150/90 as well as those massively and
dangerously over. By exporting those patients ID, name and age, plus the SBP,
DBP and date to Excel, you can rank them AZ or ZA and decide who needs action
first.




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel                              9 of 12
TIPS FOR PRINTING LARGE SPREADSHEETS


Shrinking The Size Of A Report To Fit A Page
Click FILE | PAGE SETUP, but this time click the PAGE tab. To control the
number of pages across or down, use the FIT TO option and type in the number
of pages wide or tall.
To get just the width controlled (for example, a report might need to fit on 1
page wide but can be any number tall), type the number 1 in the wide box and
leave the tall box empty
Freezing Headings On Printouts
This will print the column headings on each page if you have more rows of data
than will fit on one page when you print it out. With the spreadsheet open, click
FILE | PAGE SETUP. Click the SHEET tab. To keep the headings in row 1 fixed,
click in the box marked ROWS TO REPEAT AT THE TOP and then select the
row number 1 in the spreadsheet behind. Do the same for any columns you wish
to keep in view on each page.
Wrapping Text In Cells
Select the area of cells to have the text wrapped in and choose FORMAT, click
the ALIGNMENT tab and then click the WRAP TEXT box.


SUMMARY
I hope this guide gives you the directions you need to start working with EMIS
and Excel to really find out how your practice is managing at achieving the
targets of care you set yourselves, as well as the necessary work for QOF,
practice based commissioning and local and national enhanced services.
June 2006




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel         10 of 12
            Appendix: Useful Keyboard Excel Shortcuts


Working with Worksheets
   Insert new worksheet                    SHIFT + F11
   Rename a worksheet                      Double Click a Sheet name (tab at bottom)
                                            – then overtype it. Avoid \, -, / and symbols
                                            etc
   Delete a Sheet                          Right Click a Sheet name and select Delete

Working with Rows & Columns
   Select entire column                    Click on letter at top
   Select entire row                       Click on number at left
   Insert a Column or Row                  Right Click an existing Column letter / Row
                                            number and select Insert.
   Delete a column or row                  Right Click an existing Column letter / Row
                                            number and select Delete
   Autosize column width                   Double click join between column letters.
                                            Autosize entire spreadsheet by clicking
                                            above cell A1, then double click any double
                                            headed arrow appearing between any two
                                            column headers
   Extend a selection downwards            SHIFT + Down Arrow (one cell at a time)
                                            SHIFT + Page Down (once screen at a time)
   Hide a column                           Right Click column letter and select Hide.
                                            Try to avoid hiding column A- a hassle to
                                            unhide!
   Un-Hide a column                        Select columns either side, Right Click and
                                            select Show
   Select a column of data                 CTRL + SHIFT + Down Arrow
    (starting in top cell)
   Select a row of data (starting          CTRL + SHIFT + Right Arrow
    in left cell)
   Select entire block of data             CTRL * [asterisk] on number key pad (or
                                            Ctrl+shift+8 on alpha keypad)



Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel                  11 of 12
Moving Around
   Arrow keys                              Move by one cell in direction of arrow
   Move down one screen                    PAGE DOWN
   Move up one screen                      PAGE UP
   Move left / right one screen            ALT + PAGE UP or ALT+ PAGE DOWN
   Move to the beginning of the            HOME
    row
   Move to cell A1                         CTRL + HOME
   Move to the end of the used             CTRL + END
    area of worksheet
   Move up or down to the edge             CTRL + UP/DOWN ARROW
    of the current data region
   Move left or right to the               CTRL + LEFT/RIGHT ARROW
    edge of the current data
    region
   Move to the next sheet /                CTRL + PAGE DOWN or CTRL+PAGE UP
    previous sheet in the
    workbook
   Switch workbooks                        CTRL + TAB

Working with Data
   Wrap text in cell                       ALT + ENTER
   Fill a value all the way down a         Type in first cell. Locate black plus cursor
    column (to same depth as                in bottom right corner of cell. Double Click
    adjacent column)
   Enter the value you typed into CTRL + ENTER
    all the selected cells
   Cut – Copy - Paste                      CTRL X – CTRL C – CTRL V
   Undo                                    CTRL + Z




Beginners Guide To Exporting EMIS Data From LV To Microsoft Excel                    12 of 12

								
To top