Read values from graph on website using excel macro

Question

I have a problem for which I can not find the solution on my own. I tried to read out the values from the following website: https://datawrapper.dwcdn.net/6E03v/580/. I think I have managed to find the corresponding part in the code of the website which is THIS ONE. As per my understanding the values can be found with tag "span" or class "fg", but none of them seems to work. This is the code I am using for it in the version I use tag "span":

Dim WertFG As Selenium.WebElement
Dim WerteFG As Selenium.WebElements
Dim strTargetTab As String
Dim lgNaechsteFreieZeileZwiSpTblFaelleNachAlter As Long
Dim lgSpalte As Long
Dim lgNaechsteFreieZeileReiterNTVCoronadaten As Long


'Wertzuweisung Variablen
    Set ChromeBrowser = New Selenium.ChromeDriver
    

'Chrome starten und auf die relevante Seite für die gesuchte TabelleCoronaVirusPandemieParameter gehen
    ChromeBrowser.Start baseUrl:="https://datawrapper.dwcdn.net/"
    ChromeBrowser.Get "/6E03v/577/"
    
    
'Werte auslesen
    strTargetTab = ThisWorkbook.Worksheets("ZwiSp Tbl Fälle nach Alter").Name
    ThisWorkbook.Worksheets(strTargetTab).Activate
    ThisWorkbook.Worksheets(strTargetTab).Range("A1:A50").ClearContents
    
    Application.Wait (Now + TimeValue("00:00:03"))


'Tabellenwerte auslesen
    Set WerteFG = ChromeBrowser.FindElementsByTag("span")
    lgNaechsteFreieZeileZwiSpTblFaelleNachAlter = ThisWorkbook.Worksheets(strTargetTab).Cells(Rows.Count, 1).End(xlUp).Row + 1
    lgSpalte = 1
        
        
    For Each WertFG In WerteFG
        ThisWorkbook.Worksheets(strTargetTab).Cells(lgNaechsteFreieZeileZwiSpTblFaelleNachAlter, lgSpalte).Value = WertFG.Text
        lgNaechsteFreieZeileZwiSpTblFaelleNachAlter = lgNaechsteFreieZeileZwiSpTblFaelleNachAlter + 1
    Next WertFG
        
        
    ChromeBrowser.Close

Does someone have an idea, why this does not work? Has it something to do with the fact, that the values on the graph are only shown when you hover above the corresponding part of the graph?

Thanks for your help! Oliver

Addition: What I want to do: Read out all the values for tag “span” to a worksheet in excel. Each value should be written in consecutive cell in the worksheet, i.e. A2, A3, ….

What the macro does: Reads out the values for tag “span” for the first 4 rows then delivers 11 rows with no values and then again shows the remaining values of the website for the element “span”. I assume that in the 11 empty rows the numbers to the graph (this is what I need) would be shown, if the macro would work correctly. I have also attached a screenshot of the read out results to this post: Read Out Results Excel Worksheet

*"none of them seems to work"* is no usful error description. Instead describe what went wrong, which errors you get and where or what your code actually does versus what you expected it to do. Also to know what already works can be useful to know. Does `WerteFG` contain any elements? Note that this `ChromeBrowser.FindElementsByTag("span")` will find **all** `span` elements in the entire html code. — Pᴇʜ, Mar 26 '21 at 08:21
Thanks for your reply and sorry for not being precise in the first round, still have to learn how to do posts in a “programmers” community. I am aware that the way I have designed the code all span elements will be read out. I only have limited skills and there were not too much of them, so the design was good to go with for me. I have made additions to the original post to give more info. — FlyingKeys, Mar 26 '21 at 10:24

score 1 · Accepted Answer · answered Mar 26 '21 at 21:42

There is a wait needed before download is pulling from that page.

Also, it would be better to target specific spans e.g.

Dim values As webelements, labels As webelements, r As Long

Set values = chromebrowser.FindElementsByCss(".dontshow span")
Set labels = chromebrowser.FindElementsByCss(".series span")
r = 0

For i = 1 To labels.Count Step 2
    Debug.Print labels.Item(i).Text
    Debug.Print values(i).Text
    Debug.Print values(i + 1).Text
    r = r + 1
Next

However, data comes from a csv that you can download. The csv has a timestamp parameter which may help with caching. I doubt server does much with it. @TimWilliams wrote a very nice little function to generate unix timestamps which you can use to construct the csv download url.

So, if there is other stuff you want on that page you can just chromebrowser.get to the constructed url and it will download:

Public Sub GetCovidNumbers()
    
    Dim downloadUrl As String
    
    downloadUrl = "https://static.dwcdn.net/data/6E03v.csv?v=" & CStr(toUnix(Now))
    Debug.Print downloadUrl
    
    'd.get downloadUrl

End Sub

Public Function toUnix(dt) As Long
'https://stackoverflow.com/a/12326121 @TimWilliams
    toUnix = DateDiff("s", "1/1/1970", dt)
End Function

Or, if you only need that, you can set a download path and use urlmon to download from constructed url e.g.

Public Const folderName As String = "C:\Users\<user>\Desktop\covid.csv" '<=Change as required

Public Sub downloadCSV()
    Dim ret As Long
    ret = URLDownloadToFile(0, "https://static.dwcdn.net/data/6E03v.csv?v=" & CStr(toUnix(Now)), folderName, BINDF_GETNEWESTVERSION, 0)
End Sub

In all cases, you need to tidy up the headers in output and the age category 5-9. I would simply ignore those as they are constants so you can have them stored elsewhere.

Thanks so much for your help!I like the procedure with the download of the CSV file very much and implemented it into my procedure which works out just the way I want it. Thanks again! — FlyingKeys, Mar 27 '21 at 08:41

Read values from graph on website using excel macro

1 Answers1