Przykład Candlestick Chart Googla z danych giełdowych pochodzących z archiwum stooq.pl wyświetlany na domyślnej przeglądarce.   strona główna:
A po co ten Excel ;-)
 
 
 
  Candlestick Charts
W "Danych historycznych" notowań na 
 
stooq.pl można pobrać dane w csv :-)  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Link ten wykorzystamy do importu danych do naszego wykresu.  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  Candlestick Charts
Google udostępnia narzędzia do tworzenia wykresów na podstawie ich interfjesu. Jednym z nich jest wykres Candlestick Charts  
Kod tworzący taki wykres wygląda następująco:  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Teraz naszym zadaniem jest połączenie danych ze stooq oraz możliwości tworzenia wykresów Google:  
 
Option Explicit  
 
Private Declare Function URLDownloadToFile _  
    Lib "urlmon" _  
    Alias "URLDownloadToFileA" ( _  
        ByVal pCaller As Long, _  
        ByVal szURL As String, _  
        ByVal szFileName As String, _  
        ByVal dwReserved As Long, _  
        ByVal lpfnCB As Long) As Long  
               
Sub ssss()  
    Const strPAth As String = "C:\Users\tkuchta\Desktop"  
    Const strFile As String = "temp.txt"  
      
    Const strSymbol As String = "usdpln" '"eurpln" '"cdr" '"wig20" '"usdpln"  
    Const d1 As String = "20170101"  
    Const d2 As String = "20170607"  
 
    If URLDownloadToFile(0, _  
                        "https://stooq.pl/q/d/l/?s=" & strSymbol & "&d1=" & d1 & "&d2=" & d2 & "&i=d", _  
                        strPAth & "\" & strFile, _  
                        0, 0) = 0 Then  
          
        Dim strHTML As String  
        strHTML = strHTML & "<html>"  
        strHTML = strHTML & "  <head>"  
        strHTML = strHTML & "    <script type=""text/javascript"" src=""https://www.gstatic.com/charts/loader.js""></script>"  
        strHTML = strHTML & "    <script type=""text/javascript"">"  
        strHTML = strHTML & "      google.charts.load('current', {'packages':['corechart']});"  
        strHTML = strHTML & "      google.charts.setOnLoadCallback(drawChart);"  
        strHTML = strHTML & "  function drawChart() {"  
        strHTML = strHTML & "    var data = google.visualization.arrayToDataTable(["  
 
        Dim nr As Integer: nr = VBA.FreeFile  
        Dim i As Long  
        Dim strLine As String, arr As Variant  
          
        Open strPAth & "\" & strFile For Input As #nr  
            Do While Not EOF(nr)  
                Line Input #nr, strLine  
                i = i + 1  
                If i > 1 Then  
                    arr = VBA.Split(strLine, ",")  
                    strHTML = strHTML & "['" & arr(0) & "'," & _  
                                    arr(2) & "," & _  
                                    arr(4) & "," & _  
                                    arr(1) & "," & _  
                                    arr(3) & "],"  
                End If  
            Loop  
        Close #nr  
 
        strHTML = strHTML & "    ], true);"  
        strHTML = strHTML & "    var options = {"  
        strHTML = strHTML & "      backgroundColor:'white',"  
        strHTML = strHTML & "      bar:{groupWidth: '80%'},"  
        strHTML = strHTML & "      legend:'none'"  
        strHTML = strHTML & "    };"  
        strHTML = strHTML & "    var chart = new google.visualization.CandlestickChart(document.getElementById('chart_div'));"  
        strHTML = strHTML & "    chart.draw(data, options);"  
        strHTML = strHTML & "  }"  
        strHTML = strHTML & "    </script>"  
        strHTML = strHTML & "  </head>"  
        strHTML = strHTML & "  <body>"  
        strHTML = strHTML & "    <div id=""chart_div"" style=""width: " & i * 15 & "px; height: 800px;""></div>"  
        strHTML = strHTML & "  </body>"  
        strHTML = strHTML & "</html>"  
 
        nr = VBA.FreeFile  
        Open strPAth & "\Temp.html" For Output As #nr  
            Print #nr, strHTML  
        Close #nr  
          
        ThisWorkbook.FollowHyperlink strPAth & "\Temp.html"  
        'VBA.Kill strPAth & "\Temp.html"  
        VBA.Kill strPAth & "\" & strFile  
                                
    End If  
End Sub  
 
W efekcie otrzymujemy:  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Taka zabawka :-)