November 2008 Newsletter

HARP Reports in Excel

By: Candace Wintering, TechComm Services Manager

The Quadax healthcare A/R management software, HARP, has extensive reporting capabilities. The reports are formatted to easily print on a standard page. But you may have a particular need to rearrange or perform calculations on data that is difficult to do in this format, or even have reports with more data than fits on the page width. Fortunately, many reports have an option for creating comma-delimited text files that can be imported into a spreadsheet application such as Microsoft Excel®. The file contains all the information from the report (plus additional data for some reports), but Excel enables you to sort, filter, and sum the data to find the exact information you need.

Spreadsheet files must be created when reports are generated in HARP. Users who access HARP through Reflection download these files directly from HARP, but those who access HARP through the Quadax Portal must download them from the Portal. The HARP Report Manual has a Report Reference Guide, which shows which reports have a spreadsheet option, and a section on downloading files directly from HARP. This article will focus on downloading spreadsheet files from the Portal. Look for articles in future newsletters on formatting and analyzing HARP report data in Excel.

Access to Spreadsheet Files in the Portal

The Secure File Exchange (SFE) section of the main Portal page has download links for files sent to the Portal. Downloaded files are securely transferred across the Internet to your PC, protecting the confidentiality of patient and financial data.

You can ask your Quadax Service Consultant to have files sent here automatically during processing, or you can send files to the Portal yourself with the Send to ASP function in Download Files or View/Print Reports in HARP.

Reports and spreadsheet files are also available under HARP Reports in the Portal if you have that Portal privilege. (Contact your Quadax Service Consultant for privilege changes.) Select the HARP Reports link under the Secure File Exchange section, and then choose options on the Report Selection Criteria page to open a list of reports. If a report has a spreadsheet file, a paper clip icon appears in the fourth column. Click it to download the spreadsheet file.

The download link could also indicate files that are not formatted for spreadsheets so cannot be imported into Excel. For example, some Aged Accounts Receivable report files contain data formatted for the Management Letter, but others are spreadsheet files. Check the Create File option for the report to see which kind of file it is.

Downloading Spreadsheet Files

After clicking the download link, the File Download box appears; click Save to download the file.

 Pay attention to the location where the file is saved. It’s a good practice to create a special folder for your downloads with a name such as Reports or Downloads; that way you will always know where to find them.

Report spreadsheet files sent to the Secure File Exchange in the Portal are compressed (zipped), so the receiving PC must have software, such as Windows Compressed Folders or WinZip®, to decompress them. Find the zipped file on your PC and decompress it according to your software procedures. The unzipped spreadsheet file has no file extension so add the text file extension .txt at the end. Files downloaded from HARP Reports already have this extension. You may also want to give the file a more descriptive name.

 Once a spreadsheet file is downloaded from the Secure File Exchange, it may no longer be available there.  

Opening Spreadsheet Files in Excel

Spreadsheet files are specially formatted text files with comma-separated values (CSV). The HARP Report Manual has instructions for opening them in Excel. Important points to remember are:

Open Excel first, and then open the file from within Excel (File > Open).

By default, the file open dialog box lists the files that are Microsoft Excel files. Since a report spreadsheet file is a text file, it will not appear on the list until you change the Files of Type (drop-down list on bottom of box) to All Files.

 If many files are in your download folder, click Date Modified to display the most recent file at the top.

The Excel Text Import Wizard automatically appears when you try to open text files. You must choose certain options in order for the data to be imported correctly.

• Step 1: Choose Delimited for original data type.
• Step 2: Choose Comma for the Delimiter and double quotes (“) for the Text Qualifier.
• Step 3: Convert data values to the correct format. The default for all columns is General, but you should change it to Text for columns with numeric codes, such as insurance codes.

If you import codes in the General format, leading zeroes are dropped so the codes may not be correct.

To change a column format, select the column to highlight it, and then choose Text in the Column Data Format box. You may also choose a date format for columns with dates. The first column is initially highlighted. If the report title in the first field is very long, it may be necessary to move the scroll bar to the right to see other columns or down to see the headings. You can select multiple columns by pressing the Shift key while selecting columns.

Click Finish—your data is now in Excel with all the data manipulation capabilities of the software. Before closing the file, save it as a Microsoft Excel Workbook (File > Save As) so you do not have to import it again the next time you open it.

The next article in this series will look at formatting reports in Excel.

E-mail the author: