Tuesday, May 20, 2014

Nerdy Text Only Version: How To Use the Giant Medicare Physician Database (2 GB, April 2014)

Separately, I have posted a main article on the giant Medicare physician data base, here.

From that webpage, you can access a 40-slide PowerPoint explanation, and also access a 20 minute YouTube video in which I narrate the PowerPoint.

If you don't want the PowerPoint and/or the inspiring online video with the eloquent soundtrack I recorded, I am also posting some step by step instructions here...in writing alone.


For the CMS homepage for all the data files, click here.

  • The comprehensive database is a 1.7 GB data base that can't be used in Excel, but only in dedicated database programs.  (I didn't touch it.)

  • The aggregated data files are of two types:

    • PHYSICIAN AGGREGATED DATA.   This is each physician in the US, with his NPI number, address, gender, total billings, total beneficiaries he saw, etc.   
    • HCPCS AGGREGATED DATA.   This is every CPT and HCPCS (Supply/drug) code used in CY2012.by both USA aggregate and by state. 
  • The Physician/Supplier Data is so large that CMS breaks it down alphabetically.
    • The A physicians.
    • The B physicians.
    • The C-D physicians.
    • And so on.
    • Each is small enough it will open (although it may take 2-3 minutes) in Excel.  Each data base is less than 200 MB and less than 1M lines.

At least on my Windows 7 system, when I download the files (3-5 minutes), and click to open in Excel (another 2-3 minutes), I get a yellow bar warning the file is downloaded from the internet and may not be safe.  If you click on that, to use the file, my system AGAIN takes 3-4 minutes to save the file before I can manipulate and use it.  So there went 10 minutes of so.  At this point, I click "Save" so I don't have to go through that again, but of course, it triggers a couple minute Save.  But from then on the files will be faster to use and will be ready on your hard drive.

How big is the data?  The largest files approach 200 MB.  If a file is 900,000 lines long x 27 columns, it is 24.3 million cells.  And there are about 10 such files, so the CMS physician data files may be close to 200 million cells in total.


FILTER.  The physician supplier databases are 27 columns wide.   Each column contains a "filter" box at the top.  For example, you can filter first on provider type "Allergist" and then on CPT code 99202 (20 minute new patient office visit.)   If you were in the "A" provider file, you now are looking at all "A" name allergists who billed for a 99202 type new patient visit.

SORT.  If you want to sort columns, I suggest first deleting Row 1, an AMA Copyright line, since it may mess up the sorting process.

To sort, click your cursor in the top data box of the column you want to sort on, go to top right, and select Sort, Z to A (to sort from small to large).  You can also use the Sort/Custom Sort feature to select the column you want to sort on from the menu options found there.


After you filter and sort, the database will still be as huge as you started with.  To copy only what you see:
  • Click Ctl-A to select All.   Go to Find & Select at top right of the Home tab.   Click Go To Special, click Visible Cells Only.  Now click Ctl-C to copy everything.  Go to a new spreadsheet and paste.   Often, you will have reduced a 20M to 200M dataset to only a few hundred Kb by this maneuver.
From A to B to C to....Z, instead just "A-Z".   
Since you are working with one provider alphabetic group at a time, if you have just done this for "A" providers, repeat it for B providers and then the C providers and so on.  Then collate them all into one A-Z data page.  Even if it's now 500 KB or 1 MB, you've pulled that out of the original 1.7 GB of total data spread across the ten or twelve Excel files.

Skim the Data.
If you research a topic in the "A" file, it is about 5% of the total data.  The HIJ or KL files are about 10% of the total data.   So, for example, if 50 doctors under "HIJ" used a certain code, the total number in all the files will likely be ~500 doctors.  

Leave your Master Files intact.
I suggest if you have manipulated and filtered the original 200MB database files, do NOT save them unless these are the filter settings that you want to see when you open it again.   Just close them so they stay in their original state.

Procedures or Costs per 1000 Beneficiaries
If you have state level data, normalizing it by the number of beneficiaries per state is often helpful.  Beneficiaries per state can be found here which is one of the tables in a large set, here.  For example, for one service I researched, by state, variably 0.5 to 5 Medicare enrollees per 1000 got the service.  Spending on the service (per 1000 patients) varied by 10X among the 50 states.

How to Record PowerPoints as Videos, my lessons learned are here.

Tuesday, May 6, 2014

How to Record PowerPoint Talks as Videos (By Someone Who Barely Can)

I'll walk you through my slow path to success, but my conclusions and recommendations are at bottom ("Final Advice.")

What you're trying to do is called "screencasting."   The tech website LifeHacker has a recent review of software options, here.   Numerous options, with editorial and user reviews, are at CNET, here.

I won't belabor the several options I tried, but it was something like this.

Research.   I researched using LifeHacker and other Google searches.   (See the "here" links just above).

CamStudio is free, with numerous high ratings on CNET, but it downloaded some "crapware" to my browser even after my clicking "no" on all the crapware options that appeared during installation.  The interface seemed awkward to use and it stored very large files, 20 MB/minute, odd since my PPT slides were not changing for 20 seconds at time.

Jing is very friendly but saves only 5-minute files.  It's hard to find where they are on your computer (they're meant for cloud storage).  It's from the manufacturer of a $299 program, CamTasia, so the upgrade path from the 5 minute version is pretty steep.  It will save a cryptically named SWF file on your harddrive (like "1k382948374d33dd.swf"), but it's hard to find except by a hard drive search for recent .SWF files.

LiteCam seems to get good reviews but one user wrote, "loaded a lot of spyware" so I never tried it.

Just Use Powerpoint - No, Don't!!

I then learned via Google that you should be able to record your "next slide clicks" along with microphone narration in Powerpoint  - and then save them as movie files!   But I had a miserable time with this and PowerPoint refused to save (as WMV video files), my timing and recording efforts, or it saved them with no sound, and I gave up.  This was so painful I don't recommend trying.  Maybe with small files (5 slides and 90 seconds) it would work.  One person online recommended letting PowerPoint save the WMV movie "overnight" - even when I tried that, it failed (PowerPoint just stopped at some point overnight, leaving behind a 4 minute movie from my  15 minute presentation).

What I Ended Up With 

I ended up using Screencast-O-Matic, and after getting it to work as freeware for a half hour, I shelled out the $15 annual fee.
For $15, you can now record "over 15 minute" videos, it keeps track of more than one video at a time, and there is no watermark.  You can pay the $15 annual fee on PayPal, reducing the risk of paying the fee forever.  This $15 fee also instantly unlocks a lot of software options, like video editing, I probably won't ever have time for.

Screencast-O-Matic is pretty self explanatory.   You set up a screen capture window pane over your PowerPoint slide, and click start.  You have a 3 second countdown, during which you should click somewhere on your PowerPoint program to ensure it is the active program (e.g., you want to use the "page down" button on your keyboard to advance slides as you talk).  If you bring your pointer onto the screen area, Screencast shows it as a yellow circle, so you can draw attention to parts of the slide.  (Probably this is best, not to overuse).

When you get finished, click DONE and you come to a display screen that lets you save in various formats or auto upload to Youtube and other cloud sites.   "Video File" means the option to just save your movie the old fashioned way on your hard drive.   MP4 videos in Screencast-O-Matic saved at 2 MB/minute or 20 MB/ten minutes.

Youtube - if you have a free account, you already automatically have upload privileges.  You may have never noticed the "upload" button at top right when you are logged into Youtube.  It takes you to an upload window and can just "drag" the MP4 file onto it.  My blog host, Blogger, is owned by Google which owns Youtube, and I should be able to 'embed' the videos almost automatically into the blog, but I haven't figured that out yet.  Embedded means a YouTube box shows up right inside your blog, rather just like a link like the word "click here."  Rather than accepting your YouTube link for your video, Blogger takes you to a YouTube verbal search box, and I couldn't find my own video that way.

  • (I later used Blogger to embed an undesired YouTube video four times in a row into my website.  Then I manually edited the Blogspot HTML code sequentially with the correct Youtube video records for my training videos, which are like "XeiS32f.")

Documents for Users.   The PowerPoint documents I linked to, so they people could download the plain PowerPoint file, are stored on my Google Drive account, where they are individually set to a public "share" function.  You just write this very long Google Drive Share Link into your blog  with the link (the links based based on very long code names like 2l3id8392sgT47dksfd839284 but all my reader sees is "click here").  Many different cloud document websites like DropBox will do this, too.

  • Figuring out how to do these four, ten-to-twenty minute videos took one whole evening, and half of another.  
  • Most of the time was spent on getting CamStudio crapware out of my browser, and slow miserable failures with PowerPoint recording by itself using its internal functions.
  • Follow this post, and you can be up and running in 15 minutes.

Good Audio Presentation is Challenging.
These training videos aren't perfect - they could be re-done three or four times with audio improvements, but this has been a first attempt.  You also realize that while it is easy to make a PowerPoint tutorial recording, all the aspects of speed, tone, timbre, clarity, hesitation become obvious to the listener, probably more obvious than in a live conference presentation.  This project took 30 days of daydreaming about doing it, and about 3 hours to do it, so I was happy to just get a first version out there.   I can re-make the video files with more experience and practice for style.

With the Right Set-Up, Recording is Easy
For Screen-O-Matic.  Below, you see the PowerPoint program, and the dotted square (single arrow) shows the current window which is ready to record.   The Screencast-O-Matic program controls are shown by the double arrow.   If you clicked the round red button down there, it would count down flashing "3-2-1" and then start your video and audio recording.   When you clicked "Done" (which appears during recording) it would take you to a save-or-upload-your-work screen.  During recordings there will appear the buttons for Restart or Cancel (X).

  • Download Screen-O-Matic.  
    • Don't try the internal PowerPoint recording and make-a-movie function.
      • Very painful evening for me trying PowerPoint alone.
      • Only recommend this to your mortal enemies.
  • When the Free Screen-O-Matic is OK.
    • None of your planned tutorials will be longer than 15 minutes.
    • You will save the movie with "Video File" function after pressing the DONE key  
      • Free version only keeps track of 1 movie at a time.
    • A free software watermark is OK.
  • When to Pay $15 for Full Version Screen-O-Matic.
    • Movie is longer than 15 minutes.  You're free of their company watermark.
    • You do get many extra saving and edit features, although I haven't used them.
    • It's an annual $15 fee, so I worry about free renewals showing up on my credit card after I've forgotten about the software, website, and passwords.  I think if you pay with PayPal, this can't happen.   (But you'd need to renew manually if you want it in a second year.)
    • After you press "Done," Screen-O-Matic is able to upload onto your YouTube account.  But, instead, you save to your hard drive and then upload  manually on the YouTube website as people have done for years.  
      • I had to upload my videos manually to YouTube, since I created a little YouTube free "channel" that is different than my account name - which is the primary channel available to Screen-O-Matic when you enter your YouTube account info.  
      • If there is some way to auto upload to my secondary YouTube channel I've made, I don't understand how, and it's not worth it to try and figure it out for me.  
        • Once I have the movie done, I can manually drag and drop it onto my YouTube channel very easily while I had the correct YouTube account/channel on screen.  Note that YouTube options call your secondary channel name a secondary account, so you click Blue Google Square at upper right and then "Switch Account" to get to the secondary channel.

Practice Insert Video

Practice Insert Video


This was a screencast from S-O-M and direct upload to Youtube Account.

Saturday, May 3, 2014

CMS Regulation for Preventive Services

§410.64   Additional preventive services.

(a) Medicare Part B pays for additional preventive services not described in paragraph (1) or (3) of the definition of “preventive services” under §410.2, that identify medical conditions or risk factors for individuals if the Secretary determines through the national coverage determination process (as defined in section 1869(f)(1)(B) of the Act) that these services are all of the following:
(1) Reasonable and necessary for the prevention or early detection of illness or disability.
(2) Recommended with a grade of A or B by the United States Preventive Services Task Force.
(3) Appropriate for individuals entitled to benefits under part A or enrolled under Part B.
(b) In making determinations under paragraph (a) of this section regarding the coverage of a new preventive service, the Secretary may conduct an assessment of the relation between predicted outcomes and the expenditures for such services and may take into account the results of such an assessment in making such national coverage determinations.
[73 FR 69933, Nov. 19, 2008, as amended at 75 FR 73615, Nov. 29, 2010]