Using The Pharmly Cloud Data API In Excel

– By Greg Mills, Founder of Analytic Health

Accessing healthcare data is a simple and enjoyable process...
… when you have the right tools for the job🛠

Our Programmatic REST API

Data-driven healthcare decisions are more important than ever but accessing the data is not always as simple as we would like. So let’s take a look at how simple this can be with a REST API, which in simple terms means a process to share data, or not-so-simple terms… an Application Programming Interface for Representational State Transfer (let’s stick to the former).

Along with my business partner (and data-science star ⭐) Veerle van Leemput, we created a REST API to make an enriched set of England prescribing data freely available. Here we’ll demonstrate how to access that data, and as Microsoft Excel is still one of the most commonly used tools we’ll base the demo in Excel.

So, let’s see how we can impress all our colleagues by using a REST API to get some live data! You can follow these 7 simple steps to create the Excel workbook yourself or even download a fully working demo version here*. 

_{* You will need to adjust privacy settings for this example to work (step 2 below). Excel displays the warning: ‘this setting could expose sensitive or confidential data’. So I recommend you only adjust these if you have permission and know what you’re doing. We can’t access your data, it is merely a requirement from Excel, to enable the API functionality. Chis Webb gives some nice background in his article here. We don’t record any personal information relating to the requests to this data source and we don’t ask for any of your details to use the service.}

7 Simple Steps

1. Open Excel.
2. Click Data > Get Data > Query Options > Privacy > Ignore the Privacy Levels and potentially improve performance > OK

3. Click Data > From Web > copy and paste this URL: https://apps.analytichealth.co.uk/REST_API_tryout/prescribing-pco?active_ingredients=aciclovir > click OK

4. If you see this pop-up, just click Connect

5. A new window will open. Click To Table > OK (leave the default settings)

6. Click the button to expand the columns > Untick the column prefix option > click OK.

7. Click Close & Load, which will show you the full table in an Excel sheet 😎.

Great work! You should now have a fully automated workbook of England prescribing data. Each month just hit the Refresh button to keep the data live!

Available Data

As part of this demo, we made a selection of active ingredients available, including Aciclovir, Baclofen, Paracetamol and Risperidone. Why not see if you can build a more dynamic way to retrieve the data for each of these! (This article might help you get started).

_{(This list of active ingredients may change- you can always contact us to enquire what is currently available)}

I hope this helps you to better understand what a REST API is and how it can be used to access data. And if you’ve managed to follow the process yourself, then well done you for learning some great techie skills! ⭐️

For more details about Pharmly Cloud Data – the application that inspired this demo – take a look at our website here. We also wrote a white paper about the challenges of accessing high-quality data and some of our solutions, which you might find interesting. You can download it here.

We’ll be following up on this example with a Tableau dashboard using the same dataset to see how we can analyse prescribing trends across the UK.

See you next time!

References

https://analytichealth.co.uk/pharmly-cloud-data/

https://easy-excel.com/how-to-create-a-refresh-all-button-in-excel/

https://helpcenter.itmplatform.com/project/use-parameters-in-api-calls-from-excel-cells-named-ranges/