+ Reply to Thread
Results 1 to 14 of 14

COVID live update

  1. #1
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    COVID live update

    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:
    Name:  Capture.JPG
Views: 495
Size:  44.3 KB

    Check attached file for details.
    Attached Files Attached Files
    Last edited by Pepe Le Mokko; 03-23-2020 at 12:20 PM. Reason: Moved to Tips and Tutorails section
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: COVID live update


  3. #3
    Valued Forum Contributor Eastw00d's Avatar
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016, 2019
    Posts
    833

    Re: COVID live update

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

    Cheers
    Erwin
    PS.
    Might be due to my VPN connection....
    Last edited by Eastw00d; 03-23-2020 at 01:05 PM. Reason: Update
    I started learning VBA because I was lazy ...
    Still developing.... being more lazy...

  4. #4
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: COVID live update

    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

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: COVID live update

    Quote Originally Posted by Eastw00d View Post
    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 …

  6. #6
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: COVID live update

    Quote Originally Posted by Howardc1001 View Post
    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...

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: COVID live update

    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
    Name:  0.JPG
Views: 425
Size:  76.2 KB

    EDIT: Forgot to add link to unzip function.
    https://sql10.blogspot.com/2016/06/r...erquery-m.html
    Last edited by CK76; 03-24-2020 at 09:12 AM.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  8. #8
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: COVID live update

    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.

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: COVID live update

    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.

  10. #10
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: COVID live update

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

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: COVID live update

    Yep for snapshots

    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.

  12. #12
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: COVID live update

    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

  13. #13
    Registered User
    Join Date
    07-08-2020
    Location
    Ukraine, Kiev
    MS-Off Ver
    Microsoft Office 2010 (64-bit)
    Posts
    15

    Re: COVID live update

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

  14. #14
    Registered User
    Join Date
    06-16-2020
    Location
    Kharkiv, Ukraine
    MS-Off Ver
    Office 2010
    Posts
    9

    Re: COVID live update

    Quote Originally Posted by Marc L View Post

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Coronavirus disease (COVID-19) advice for the public
    By avk in forum The Water Cooler
    Replies: 4
    Last Post: 04-02-2020, 09:41 AM
  2. Live Scheduling Update
    By severett2 in forum Excel General
    Replies: 2
    Last Post: 06-12-2018, 12:57 PM
  3. Live update between 2 different spreadsheets
    By misterjam in forum Excel General
    Replies: 1
    Last Post: 11-18-2015, 05:29 PM
  4. Live Update results to web
    By mjcloward in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-26-2012, 10:50 AM
  5. how to make live update of cells?
    By adds007 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-30-2010, 08:52 PM
  6. Live update of excel from databsae
    By Draperman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-03-2009, 10:34 AM
  7. Live update with excel?
    By Zirus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-27-2007, 12:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1