Office: (Office 2019) JSON Datei mit mehreren Listen in Excel importieren

Hallo,
ich möchte gern diese Datei in Excel importieren.


{"expires":1687436992815,
"entityType":"FUND",
"entityValue":"179881079",
"market": {"name":"Frankfurt","codeMarket":"_FRA","nameExchange":"Frankfurt","codeExchange":"FRA","idNotation":305692817,"isoCountry":"DE"},
"idInstrument":179881079,
"idNotation":305692817,
"isoCurrency":"EUR",
"unitType":"STK",
"datetimeStartAvailableHistory":"2020-09-11T00:00:00.000+00:00",
"datetimeEndAvailableHistory":"2023-06-21T23:59:59.000+00:00",
"idTradingSchedule":66,
"datetimeLast":[1672660800,1672747200,1672833600,1672920000,1673006400,1673265600,1673352000,1673438400,1673524800,1673611200,1673870400,1673956800,1674043200,1674129600,1674216000,1674475200,1674561600,1674648000,1674734400,1674820800,1675080000,1675166400,1675252800,1675339200],
"first":[108.3,110.18,108.66,107.46,107.42,108.78,108.46,109.8,110.96,110.98,111.78,111.38,112.46,110.78,109.74,112.04,114.28,113.58,114.0,115.32,116.92,115.5,116.3,119.2],
"last":[109.52,107.58,107.72,107.7,108.16,110.38,109.36,109.8,110.54,111.0,111.8,112.34,111.72,109.8,111.04,114.48,114.52,111.9,115.0,115.98,116.0,116.2,115.94,122.62],
"high":[109.52,110.18,108.66,107.7,108.16,110.38,109.36,109.88,111.54,111.0,111.8,112.34,112.56,110.82,111.04,114.64,114.6,113.7,115.0,115.98,116.92,116.2,116.68,122.62],
"low":[107.44,107.58,107.7,107.46,107.2,108.78,108.28,109.8,110.54,110.66,111.38,111.38,111.72,109.74,109.74,111.82,114.06,111.9,113.98,115.32,115.68,115.26,115.94,119.2],
"volume":[30.0,20.0,48.0,0.0,314.0,126.0,18.0,0.0,0.0,10.0,9.0,0.0,0.0,12.0,100.0,1000.0,23.0,100.0,173.0,10.0,1890.0,0.0,100.0,54.0],
"numberPrices":[11,11,13,5,11,8,15,6,12,8,10,7,9,17,15,14,13,13,12,12,16,7,10,10],
"displayUnit":"EUR"}

Man bekommt diese Daten, wenn man folgende URL aufruft:
https://api.onvista.de/api/v1/instr...ation=305692817&range=M1&startDate=2023-01-02

Ich bräuchte aus diesen Daten den Inhalt aus den Listen datetimeLast und last als Wertepaare in einer Tabelle. Ich bekomme es leider nur hin, die Daten aus einer Liste als Tabelle nach Excel zu bringen. Hat jemand einen Tipp für mich, wie ich das realisieren kann?

Vielen Dank für Eure Hilfe!

 

Datei kopieren, in z. B. copie.txt speichern.

Code:

Sub test()

    Dim i&, p&, p1&, t$
    Dim DTL$(), L$()

    Open "d:\#1\copie.txt" For Input As #1
    Line Input #1, t
    Close

    Cells(1, 1) = "datetimeLast"
    p = InStr(t, "datetimeLast") + 15
    p1 = InStr(p, t, "]")
    Debug.Print p, p1
    DTL = Split(Mid$(t, p, p1 - p), ",")
    For i = 0 To UBound(DTL)
        Cells(i + 3, 1) = DTL(i)
    Next

    Cells(1, 2) = "last"
    p = InStr(t, "last") + 7
    p1 = InStr(p, t, "]")
    L = Split(Mid$(t, p, p1 - p), ",")
    For i = 0 To UBound(L)
        Cells(i + 3, 2) = L(i)
    Next
End Sub

 

Vielen Dank für die Antwort, aber ich verstehe nicht, wie ich in dem Datensatztool den VB Editor aufrufen soll.

bzw. ich weiß nicht, welche Schritte ich machen soll in diesem Datensatztool.

 

Hallo,

einen Rechtsklick mit der Maus auf den Arbeitsblatt-Tab (zB. "Tabelle1" ...) machen, den Befehl "Code anzeigen" auswählen und du bist im VBA-Codemodul des betreffenden Arbeitsblattes. Dorthin den VBA-Code von knolli kopieren.

Ich habe dir mit PowerQuery eine alternative Lösung erstellt.
Diese greift auf folgendes JSON-Dokument zu:
= Json.Document(Web.Contents("https://api.onvista.de/api/v1/instr...ation=305692817&range=M1&startDate=2023-01-02"))

1) Wenn du in die erste strukturierte Tabelle im Bereich A2:B21 einen Rechtsklick ausführst und den Befehl "Aktualisieren" auswählst, führst du vorgenannten Befehl aus und es wird dir der aktuelle Inhalt der Datei im Bereich A2:B21 angezeigt.

2) Wie du siehst, enthält dieser Bereich 7 Einträge mit dem Wert "List"
Jedes dieser 7 List-Objekte kann danach einzeln abgefragt (extrahiert) werden:
Die Liste "numberPrices" kann abgefragt werden, indem du wiederum einen Rechtsklick in den Zellbereich D2:D26 machst und den Befehl "Aktualisieren" ausführst.
Die Liste "volume" kann so im Zellbereich E2:E26 abgefragt werden.
usw.
Die Liste "datetimeLast" im Zellbereich J2:J26

3) Weiters gibt es in B6 noch den Wert "[Record]" namens "market". Diesen kannst du ebenfalls abfragen durch Rechtsklick in den Zellbereich L2:M8.

Datei liegt bei.

 

Das ist genau das was ich gesucht habe. Vielen Dank!

So wie ich das jetzt verstehe, setzen sich die Zellen D2 bis J26 aus mehreren Abfragen zusammen, oder? Wie bekommt man die so zusammen dargestellt? Also wie komme ich zu diesem Ergebnis?

Also die erste Tabelle kann ich so erzeugen, aber alles was ich ansonsten erzeuge erscheint auf einem neuen Tabellenblatt

 

Hallo,

diese setzen sich aus 7 Abfragen zusammen, sie splitten das, was im "List" in PowerQuery (ein Datentyp in PowerQuery) bereits enthalten ist, im Excel-Arbeitsblatt im Detail auf.
In der beiliegenden Datei habe ich das vereinfacht, indem ich daraus eine einzige Abfrage gemacht habe.
Das gleiche gilt auch für "Market", dem PowerQuery-Datentyp "Record".

Nicht notwendigerweise. Man darf halt nicht übersehen, dass man die Zielangabe wie gewünscht vorgibt.

Aber so wie die Abfragen vorliegen, braucht man ja eh keine Änderung der Zellbereiche für die strukturierten Tabellen.

Landet jedoch eine Zieltabelle (durch Versehen) auf dem falschen Arbeitsblatt, kann man sie mittels Ausschneiden/Einfügen an die korrekte Stelle verschieben. Eine strukturierte Tabelle ist in Excel ein selbständiges, einheitliches Objekt, das man beliebig (als Ganzes) verschieben kann.

 

Das ist ja noch besser mit der JSON Abfrage! Vielen Dank! Damit kann ich jetzt wunderbar arbeiten.