# Off Topic > Tips and Tutorials >  >  COVID live update

## KOKOSEK

In this difficult time for all of us (I hope you are all well) I've invented live COVID cases macro if you want to add it somewhere (dashboards or something else....).
It is based on https://www.worldometers.info/coronavirus/
and getting info from your country subsite, like for me: https://www.worldometers.info/coronavirus/country/*uk*/

For most of countries it works well (because syntax of html is the same), just change you country (open you country from here: https://www.worldometers.info/coronavirus/#countries) and paste http address into sheet1 (cell A1).

I did not paste macro here as it contains html and firewall does allow me to do it even if I wrap it into html tag.

Necessary references:


Check attached file for details.

----------


## Marc L

A map : Coronavirus COVID-19 (2019-nCoV)

----------


## Eastw00d

thanks, but this map is most of the time not available...

Cheers
Erwin
PS.
Might be due to my VPN connection....

----------


## Howardc1001

Great workbook. South Africa does not have a hyperlink on the Website

I would like to update the Stats for South Africa daily. It would be appreciated if you could develop a macro to extract those countries that do not have a hyperlink on the Website below


https://www.worldometers.info/coronavirus/country


Thanks


Howard

----------


## Marc L

> thanks, but this map is most of the time not available...



Never had such issue since weeks, well works on our side whatever the day / night time

----------


## KOKOSEK

> It would be appreciated if you could develop a macro to extract those countries that do not have a hyperlink on the Website below



I am afraid that this table does not exist really. IMHO it is generated on-the-fly by javascript. I am analysing html code and so far without success...

----------


## CK76

I use following source provided by Johns Hopkins CSSE (daily compilation of data used for map link provided by Marc L).
https://github.com/CSSEGISandData/COVID-19/tree/master/

Use "Clone or download" and right click on "Download ZIP" option and copy link.

Updated daily @ 23:59 UTC.

I connect to it using Get & Transform -> Basic Web.

Using Mark White's custom function to unzip and read contents.

Their clean data is in csse_covid_19_time_series folder.

I then use binary combine method to combine Confirmed, Deaths, and Recovered data.

Note that their data for each day is cumulative total reported to that date (so don't use sum).

Their data grows by column for time series. Use dynamic unpivot to transform data for ease of use in DAX.

Initial query to bring in zip file and unpack to generate file list.

Name: FileList



```
Please Login or Register  to view this content.
```


Sample Transform for binary combine. This is where dynamic unpivot is done.




```
Please Login or Register  to view this content.
```


Transformation step for combined time series. Note that #"Invoke Custom Function1" is where binary combine is initiated.




```
Please Login or Register  to view this content.
```


DAX: Just change [Class] condition for Deaths and Recovered.



```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```


Sample plot done in PowerBI Desktop


*EDIT:* Forgot to add link to unzip function.
https://sql10.blogspot.com/2016/06/r...erquery-m.html

----------


## KOKOSEK

So maybe possible can be extract from master.zip only CSV file with biggest date (now 23-3-2020) then import this CSV into xl.
Then presentation can be done as everyone wish to.

----------


## CK76

Woops, I thought this was question forum. Didn't read carefully enough.





> only CSV file with biggest date



If using this data source. I'd recommend grabbing and transforming timeseries csv files. That way you get complete historical trend for each country.

----------


## KOKOSEK

Sure, for trends better from timeseries. Just for actual data I think better from 'dated'.csv.

----------


## CK76

Yep for snapshots  :Smilie: 

Though note that they don't clean Country/Region column in the dated csv files. And you may need to clean it to use data relationships etc.
Found out the hard way, when I tried to plot based on combined dated csv files.

----------


## Howardc1001

hi Kokosek

You workbook works great


Would you be able to set up a workbook to import the data for all the countries to extract ?

Coronavirus Cases
Deaths
Recovered

One could then have a separate sheet to extract the data for a selected county

----------


## DannyPhilips

Thank you for your work, I'll try to use it now!

----------


## kennystone

> A map



I like the worldometers resource better - there are more statistical data.

----------

