Guild Wars 2

Kennt sich einer mit der Schnittstelle von GW2 aus? ist es möglich Werte eines Items (Einkaufspreis / Verkaufspreis etc.) direkt von Excel aus der Api (GW2 nicht Gwspidy oder GW2TP) zu holen?

Um den Beitrag zusammen zu führen. damit die zusammen stehen.

Silmar hat das Problem des Refreshes erkannt und ich habe die Lösung hierzu

VB Editor aufmachen
in DieseArbeitsmappe

Private Sub Workbook_Open()
Application.OnTime Now() + TimeValue(“00:05:00”), “aktualisieren”
End Sub

eintragen und in einem leeren Modul (bei mir Modul2)

Sub aktualisieren()
’
’ aktualisieren Makro
’
’ Tastenkombination: Strg+q

Application.CalculateFull

Application.OnTime Now() + TimeValue(“00:05:00”), “aktualisieren”
End Sub

eintragen. Das Makro kann mit Strg + q gestartet werden und aktualisiert die komplette Excelliste alle 5 Minuten. Zum Beenden muss Excel komplett geschlossen werden

Vorteil einer eigenen Liste ist, man kann bei berechnungen die aktuellen AH Preise einbeziehen und die Liste so gestalten wie man es gerne möchte.
Der Nachteil: je mehr Abfragen man hat, um so länger dauert der Refresh

Hey, cool, das ist noch einfacher als ich gedacht habe.
Die Funktion ist wie folgt:

Public Function myGetPriceByID(id, buyorsell)

  ' get price info from GW2 API
  ' https://api.guildwars2.com/v2/commerce/prices/19684

  Dim oRequest
  Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
  oRequest.Open "GET", "https://api.guildwars2.com/v2/commerce/prices/" & id, False
  oRequest.Send
  ' Debug.Print oRequest.ResponseText

  Dim Json As Object
  Set Json = JsonConverter.ParseJson(oRequest.ResponseText)

  myGetPriceByID = Json(buyorsell)("unit_price")
  
End Function

Dazu benötigt man dann noch die json Bibliothek von hier:

https://github.com/VBA-tools/VBA-JSON/tree/master/specs

Wenn man das dort vorhandene “VBA-JSON – Specs.xlsm” runterlädt und in Excel öffnet und dort in die VBA-Umgebung wechselt, findet man u.a. die Module Dictionary und JsonConverter. Diese beiden schiebt man mit der Maus in die eigene Arbeitsmappe und schließt das VBA-JSON – Specs.xlsm wieder – das und die anderen Module dort drin braucht man dann nicht mehr. Den json-Importer hat man dann im eigenen Sheet.

Damit funktioniert dann auch myGetPriceByID. Siehe Screenshots.
Der erste Parameter der Funktion ist die item-id, der zweite ist “buys” wenn man den höchsten Angebotspreis haben will, und “sells” wenn man den niedrigsten Verkaufspreis haben will.

War nur ne Stunde Bastelei
Der Nachteil obiger Funktion: sie hat absolut keine Fehlerbehandlung.

Edit: ganz so einfach isses nicht, Excel sträubt sich einer ständigen Neuberechnung der Formeln. Man muss immer noch eine explizite Neuberechnung anstoßen, mit Application.CalculateFullRebuild
Dafür, so scheint es mir, gibt es keine Option in der GUI.

Public Sub refresh_sheet()
  Application.CalculateFullRebuild
End Sub

Könnte man mit einem Hotkey verknüpfen oder hinter einen Button legen.
Edit2: Das eingebaute STRG-ALT-F9 tut dasselbe.

Ja, das ist möglich. Im englischen wiki Artikel über das API gibt es eine “Trading Post” Sektion einem Link zu einer “commerce/prices” API – das dürfte das sein, was du haben willst.

Auf diesem Blog macht jemand in Excel eine Abfrage auf eine andere JSON-API mit Hilfe von Microsofts Power Query – vielleicht gehts damit.

erst einmal danke, dass ihr geantwortet habt. Ich wenn ich die api abfrage, muss ich die IPs nennen, sonst bekomme ich immer eine errormeldung.
ich dachte eher wie im gw2tp
http://api.gw2tp.com/1/bulk/items.csv
wenn ich das aufrufe, bekomme ich die komplette liste die ich direkt in excel einfügen kann

Die offizielle API unterstützt nur JSON als Datenformat… und das kann Excel (soweit ich weiss) nicht wirklich gut verarbeiten. Bzw. über VBA kann man da wohl einiges machen, muss dann aber auch programmieren.

Wenn Du die Daten so willst wie gw2tp sie anbietet wäre es vermutlich am einfachsten, ein kleines Proxy-Skript zu basteln welches die Daten per API holt und nach CSV umwandelt und welches Du dann an Stelle der API ansprichst (Excel → Proxy → API statt Excel → API).

Nur erfindest Du dann im Grunde das Rad neu… dann kannst du auch gleich auf gw2tp zugreifen. Was mich zu der Frage bringt, wieso Du das nicht benutzen willst? Wegen den englischen Item-Namen?

Wenn ich die Sachen in Excel drin hab, kann ich mir Dinge bastel, die über GW2TP nicht möglich sind, außerdem hängt GW2TP mit seinen Preisen um 3-5 Minuten hinterher. Wenn man günstig einkaufen will, sollte es (in meinen Augen) schnell gehen.
ich häng mal ein Bildchen an, wie meine Excelliste (ein kleiner Teil aussieht). in dieser werden die Daten über GW2TP eingelesen. Nur das hinterherhinken, nervt mich etwas. Deswegen möchte ich die daten direkt vom Server ziehen

Anhand des Screenshots sehe ich, dass du dir einzelne Gegenstände herausholst, deren Preis du haben willst. Dann würde ich in deren Preis-Feld eine VBA-Funktion hinterlegen, die so aufgerufen wird:
=myGetPriceByID(4711)

Das soll den Preis des Gegenstands mit der ID 4711 holen.

Die Funktion myGetPriceByID schreibst du selbst im zum Sheet gehörigen VBA-Projektbereich. Die nimmt eine Gegenstand-ID entgegen (die du aus der Komplettliste von gw2tp oder gw2spidy entnehmen kannst).

In der Funktion führst du zuerst ein http GET aus, um vom gw2-api den json-kodierten Preis für diese ID zu bekommen.
Doku für den entsprechenden API Aufruf:
http://wiki.guildwars2.com/wiki/API:2/commerce/prices
Doku für das Ausführen eines http GET Requests aus vba heraus:
https://www.google.de/search?q=vba+http+request

Dann konvertierst du den json Text in ein vba Datenkonstrukt, Info darüber hier:
https://www.google.de/search?q=vba+json
Am vielversprechendsten sieht diese Bibliothek hier aus:
https://github.com/VBA-tools/VBA-JSON

Damit bekommst du die Daten in einen vba Array und kannst den gewünschten Wert als Rückgabewert der Funktion myGetPriceByID liefern.

Immer wenn das Excel-Sheet eine Neuberechnung durchführt, sei es implizit oder explizit, wird dann für alle deine Preise, die mit dieser Funktion geholt werden, die jeweiligen gerade aktuellen Preise direkt aus dem GW2-API geholt.

by the way, wenn du das fertig hast, wäre es nett wenn du den Code der Funktion hier posten könntest. Hätte ich auch Bedarf daran.

Leider habe ich mich ewig nicht mehr mit Excel & Co beschäftigt. Von daher wäre meine persönliche Lösung entweder die mit dem Proxy-Skript auf irgendeinem Webspace (in PHP dürften das nur ein paar Dutzend Zeilen sein) um die Daten als CSV zu bekommen oder gleich auf ein Google Spreadsheet auszuweichen… da gibt es ja schon x Leute die in Echtzeit Daten aus der API abgreifen wo man sich bedienen könnte.

Z.B. holt sich dieses Spreadsheet Daten in Echtzeit (einfach eine Kopie machen und alle Spalten einblenden um zu sehen wie es funktioniert -> im Prinzip lädt der nur den JSON-Kram in eine Zelle und zerpflückt die dann mit ein paar String-Operationen)

Mehr kann ich da leider auch nicht helfen.

Edit: Oder halt Silmar’s VBA-Variante So oder so wirste dann aber wahrscheinlich nicht ums Programmieren herumkommen.

Hey, danke Euch beiden. Ich will dann mal werkeln. wenn ich es hin bekomme, kann ich euch ja eine PM senden

Die einzelnen Aufrufe zusammenschnippeln ist kein großer Aufwand. Aufwendig ist das know-how und die exakten Aufrufe der Bibliotheksfunktionen zusammenzutragen. Ich denke nicht, dass die eigentliche Funktion, die ich umrissen habe, länger als vielleicht 20 Zeilen sein dürfte.

Dein Google Calc Spreadsheet macht im Prinzip das, was ich für VBA vorgegeben habe, der html Request ist dort in ImportDATA gekapselt (eine integrierte Calc Funktion) und die json→vba Konvertierung ist dort als Billigversion mit ein paar String-Schnipsel-Routinen implementiert. Kann man so auch machen. Evtl. gibts ja in aktuellen Excel/VBA Bibliotheken auch Routinen, die direkt http Imports machen können – so umfassend kenne ich mich da auch nicht aus, dass ich da jetzt jede einzelne Funktion kennen würde.

wie geil ist das denn, dank Dir dafür. ich bin leider nicht zum basteln gekommen (außer meine Terasse abzureißen)

Edit: Thema aktualisieren: kann man nicht auf eine andere datei in einem Intervall zugreifen lassen (wie bisher) oder refresht er dann nur die zu importierenden Daten?
(muss dringend Feierabend machen und das ausprobieren)

und damit ich auch einmal etwas Sinnvolles beitrage
wer das liest und ausprobiert und sich fragt, wie man die Werte als Gold / Silber / Kupfer anzeigen lässt muss eine Benutzerdefinierte Formatierung der Zelle eintragen

[>9999] ## “G” ## “S” #0 “K”;[>99]## “S” #0 “K”;#0 “K”

Wenn man sich nicht mit den IDs rumärgern will und im Spreadsheet auch immer die Namen der Gegenstände stehen hat, dann kann die folgende Funktion helfen, die man statt myGetPriceByID im Excel-Sheet einträgt. Die holt unter Angabe des Namens die ID aus einer Umsetzungstabelle und ruft dann myGetPriceByID mit der ID auf.

Public Function myGetPriceByName(name, buyorsell)
  
  Dim id, sht
  Set sht = Sheets("tp-all new")
  id = Application.WorksheetFunction.Index(sht.Range("A:A"), _
     Application.WorksheetFunction.Match(name, sht.Range("B:B"), 0))
  myGetPriceByName = myGetPriceByID(id, buyorsell)

End Function

Die Funktion geht davon aus, dass in einem Worksheet namens “tp-all new” eine Zuordnungstabelle von ID (Spalte A) und Item-Name (Spalte B) vorhanden ist. Wenn man das Excel-Sheet verwendet, das ich mal in meinem Trading Guide aufgebaut hatte, dann ist das genau sowas. Was in den anderen Spalten dieser Tabelle steht, ist egal. Man kann selbstverständlich auch eigene Tabellen von woanders nehmen, die entsprechende Spalten haben.

Das geht dann relativ komfortabel mit relativen Referenzen im Sheet, die man auch frisch und frei hin- und herkopieren kann.

Beispiel:
In Zelle A1 kommt: Glob of Ectoplasm
In Zelle B1 kommt: =myGetPriceByName(A1; “buys”)

Egal wo man dieses Paar jetzt hinkopiert, immer holt sich die Zelle rechts den Kaufpreis des Gegenstands, dessen Name in der Zelle links daneben steht.

Falls du so eine Datei brauchst: http://filedump.gw2info.net/2015-05-07_items.zip (etwas unter 1MB gross) – das ist der Stand von gestern mit deutschen Itemnamen. Das Ganze ist eine Textdatei in der die Felder mit Tab getrennt sind. Enthalten sind folgende Daten: id, name, type, rarity, icon, description, level, vendor_value, default_skin

Sollte problemlos in Excel importierbar sein.

die Datei bekommt man ja auch von GW2 Tp, allerdings nur namen, ID, Buys, sells, Demand und offer

http://api.gw2tp.com/1/bulk/items.csv

und das direkt im Excelformat (nur halt nicht minutengenau, hinkt ca. 1-15 hinterher, je nachdem wann GW2TP aktualisiert hat und man selber auch)

Rasselbande.2654:

die Datei bekommt man ja auch von GW2 Tp, allerdings nur namen, ID, Buys, sells, Demand und offer

http://api.gw2tp.com/1/bulk/items.csv

Yup. Hattest Du ja schonmal verlinkt Meine Datei ist halt auch nur als Basis für zusätzliche Spielereien gedacht (ggf. Filter über Typen, Icons anzeigen, etc.) und eben auf deutsch statt englisch Je nachdem was jemand machen will ist die eine Datei besser oder die andere (oder eine Kombi aus Beiden)

stimmt, ich hab ja auch nicht gesagt, dass Deine blöd ist. Je mehr Info man hat / bekommt, um so besser. Die Synergie aus dem Thread hier von Silmar, Tiscan und mir hat mir eine super neue Excelliste beschert. Ich muss die Abfrage(n) nur noch deutlich beschleunigen. das aktualisieren dauert bei mir 10-15 sec.
Aber auch da bin ich mir sicher, das die findigen Leute hier im Forum eine Idee haben

Das Problem ist wahrscheinlich, dass Du die Preise von X Items haben willst und die aktuelle Variante für jeden Preis einen eigenen API-Request sendet.

Das kann man im Prinzip nur lösen indem man die Abfragen zusammenfasst (die API erlaubt ja Listen von IDs als Parameter… du sagst dann: ich will die Preise von Item 1,2,3 und bekommst die dann gebündelt zurück statt 3 einzelne Abfragen zu machen).

das wollte ich ja vermeiden, jede einzelne ID als parameter daran zu hängen die ich benötige, sobald eine dazu kommt, muss man da wieder dran werkeln. und sofort alle IDs angeben ist auch Problematisch, sind derzeit 22450 IDs ^^

aber ich bleib an dem Thema dran

Naja… wenn Du alle Preise abrufen willst kommst Du nicht drum herum, 113 Abfragen zu schicken (AFAIK liefert die Schnittstelle 200 Items pro Request zurück → 22474 / 200 = 112,37) … und ich weiss nicht wie gut Excel die parallelisiert.

Dabei muss man dann mit Paging arbeiten oder am Anfang die Liste holen und selber aufsplitten.

Wobei halt die Frage ist… brauchst Du wirklich alle Preise?

Wenn nicht, würde ich das ganze so machen:

Ich würde mir ein Tabellenblatt als “Stammdatenverwaltung” machen. Da würde ich meine Datei importieren und hinten noch 5 Spalten anhängen → buy_qty, buy_price, sell_qty, sell_price und als letztes do_update.

Dann würde ich die Funktion zum holen eines Preises so umbauen, dass sie den Preis aus diesem Tabellenblatt holt. Nennen wir sie mal entsprechend Silmars Entwurf “myGetPriceByName()” oder “myGetPriceByID()” (je nachdem was man bevorzugt).

Beim Aktualisieren des Tabellenblattes würde ich eine Funktion (nennen wir sie updatePrices()) laufen lassen, die mir alle IDs aus dem Stammdatenblatt holt wo in “do_update” ein X steht. Für die holt die Funktion dann die Preise und trägt sie ein.

Der Trick dabei ist folgender: die Funktion die mir die Preise für Berechnungen holt (also myGetPriceByName() oder myGetPriceByID()) würde bei mir 2 Aufgaben erfüllen → steht in “do_update” ein “X” holt sie einfach nur den Preis… steht da aber nichts, schreibt sie ein “X” rein, holt sich den Preis per API, trägt die Werte ein und liefert die Werte zurück.

Hintergrund ist dabei folgender: brauchst du den Preis eines Items den das System noch nicht über den Bulk-Abruf in updatePrices() kennt, holt die Funktion ihn einmalig selber. Beim nächsten Aktualisieren wäre die ID dann in der Liste für den Bulk-Request.

Der Vorteil ist, dass so nur die wirklich benötigten Daten runtergeladen werden statt 22k evtl. unnötige Daten und Du Dir keinen Kopf über das Hinzufügen der IDs zu irgendwelchen Listen machen musst.

mit Übergabeliste arbeite ich sowieso, um doppelte abfragen zu vermeiden.
ich bin aber nicht der Profi was Programmierung angeht und bin auf Hilfe anderer angewiesen
ich habe nur die Ideen und was bis jetzt raus gekommen ist, ist schon dr Hammer.
Aber wie Dein Vorschlag ist (den ich sehr gut finde) brauche ich einen Fähigen Mann/Frau, der mir das programmieren kann, sprich die IDs aus Spalte 2 nehmen, in die Abfrage setzten und die Preise dann in Spalte 3 und 4 eintragen (EK- VK)

Ich würde eine Cache-Verwaltung hinter myGetPriceByID() hängen. Die würde folgendermaßen funktionieren, ganz so wie Tiscan das skizziert hat:

• es gibt eine expire-Zeit, nach der der Cache expired
• der Cache expired immer komplett, damit die Daten dort drin praktisch einen Schnappschuss vom selben Zeitpunkt beinhalten

Wird myGetPriceByID(id) aufgerufen, dann passiert das folgende:

• im Cache nachschauen, ob für id ein buys/sells Wert hinterlegt ist
• ist ein Wert hinterlegt, und ist der cache nicht expired, liefere den Wert aus dem Cache zurück und der Aufruf ist fertig.
• ist ein Wert hinterlegt, und ist der Cache expired, dann sammle sämtliche IDs, die im Cache hinterlegt sind, und mache einen bulk-Request im gw2-api für alle IDs und aktualisiere den Cache mit den Daten für alle IDs. Speichere die Uhrzeit dieser Abfrage. Liefere dann den Wert für die ursprünglich angefragte id zurück und der Aufruf ist fertig.
• ist die ID gar nicht im Cache hinterlegt, dann mache einen einzelnen Request im gw2-api für diese ID, trage sie in den Cache ein und liefere den Wert zurück und der Aufruf ist fertig.

Dabei ist dann der erste Aufruf bei völlig leerem Cache so langsam wie jetzt, wo alles einzeln ist, weil die Preise für alle IDs einzeln requestet und in den Cache eingetragen werden. Ist aber der Cache einmal gefüllt, dann wird der Cache immer komplett mit allen IDs aktualisiert, und zwar mit nur einen einzigen API Aufruf. Das sollte dann immer relativ schnell sein. Kommt eine neue ID hinzu, wird ihr Preis beim ersten Mal einzeln erfragt, aber danach immer beim Komplett-Aktualisieren des Cache.

Das alles mit Excel zu machen, ist zwar ziemlich bequem. Aber eigentlich ist das eine klassische Datenbank-Anwendung. Excel ist halt ne Datenbank für Arme
Dann wünscht man sich z.B. auch historische Daten, weil man mit einem nackten Einkaufspreis wenig anfangen kann – der Trend und die Varianz ist fürs Handeln sogar noch wichtiger.

Wenn man das alles konsequent weiterenwickelt, landet man früher oder später bei einer Anwendung wie gw2spidy oder gw2tp.