Bexio API Datenzugriff mit Excel Power Query

Chris Burger
Digitalisierung für Startups und KMU
8 min readAug 15, 2020

Bexio bietet mit der REST API Zugriff auf alle Daten, die für ein gutes Unternehmensreporting wichtig sind. Leider gibt es noch nicht wirklich eine Dokumentation, wie man die Daten von Bexio möglichst einfach und aktuell in Excel reinziehen kann. Dieser Artikel schliesst diese Lücke.

Voraussetzungen

Damit die folgende Anleitung funktioniert, brauchst du folgendes:

  • Microsoft Excel 2010 oder höher auf Windows. Excel auf Mac OS ist leider NICHT unterstützt. Bei den Versionen 2010–2016 musst du Excel Power Query aktivieren.
  • Einen API-Key für dein Bexio Account. Diesen kannst du über “Settings -> All Settings -> Apps & API -> API key” in deinem Account generieren. Am besten kopierst du den Key irgendwo in ein Textfile. Wichtig: Diesen Key solltest du behandeln wie ein Passwort. Wenn er in falsche Hände gelangt, kann das verheerende Folgen haben!
  • Wenn du auch auf die Lieferantenrechnungen zugreifen möchtest, solltest du das neue Einkaufsmodul von Bexio aktivieren lassen.
  • Grundlegende Excel-Kenntnisse.
  • Etwas Programmiererfahrung.

Grundsätzliches

Die folgende Methode, Daten von Bexio in Excel darzustellen und zu bearbeiten unterscheidet sich grundlegen von der normalen Arbeitsweise in Excel. Excel Power Query erlaubt es, eine Excel-Tabelle auf Knopfdruch zu aktualisieren. Im Hintergrund wird dabei eine Verbindung zur Datenquellen erstellt, sämtliche vordefinierten Schritte wie z.B. Formatanpassungen oder Relationen zu anderen Datenquellen werden durchgeführt und am Schluss werden die aktuellen Daten in der Excel-Tabelle angezeigt.

Wichtig: Ich habe in dieser Anleitung einige kosmetische nicht zwingend notwendige Schritte ausgelassen wie z.B. die sinnvolle Benennung von Abfragen und Tabellen.

Rechnungsdaten in Excel laden

Rohdaten laden

Als erstes müssen wir eine RESTful Datenverbindung von Excel zu Bexio aufbauen. Ich möchte das hier am Beispiel der Rechnungen (Invoices) zeigen:

Klicke im Ribbon “Daten” unter “Daten abrufen” auf “Aus dem Web”. Folgender Dialog sollte dann erscheinen:

Verbindung zur RESTful API von Bexio herstellen

Konfiguriere die Verbindung wie folgt:

  • Wähle “Weitere”.
  • Füge unter URL-Teile die URL https://api.bexio.com/2.0/kb_invoice ein.
  • Bei den Paremetern des HTTP-Anforderungsheaders musst du eine weitere Zeile hinzufügen. Anschliessend gibst du folgende Daten in die beiden Felder ein:
  • Erstes Feld: “Authorization”. Als Wert gibst du zuerst das Wort “Bearer” und anschliessend den kompletten API Key ein.
  • Zweites Feld: “Accept”. Als wert gibst du “application/json” ein.

Anschliessend klickst du auf “OK”. Die Verbindung wird hergestellt und Excel Power Query wird gestartet:

Die Verbindung wird überprüft

Klicke dann auf “Zu Tabelle”:

Die Verbindung wurde hergestellt und es wurden Daten gefunden

Beim anschliessenden Dialog klickst du einfach auf “OK”.

Dialog für die Umwandlung in eine Tabelle

Nun kannst du die Spalten der Tabelle anzeigen. Klicke dabei auf das graue Feld rechts des Titels der angezeigten Spalte “Column1”.

Dialog für die Anzeige aller Spalten

Hier kannst du nun entweder alle Spalten wählen oder auch eine Selektion treffen. Den Haken unter “Ursprünglichen Spaltennamen als Präfix verwenden” kannst du rausnehmen. Ansonsten hat jede Spalte das Präfix “Column1.”.

Nun hast du die Rohdaten in Excel Power Query geladen und kannst sie für die Weiterverarbeitung nutzen.

Datentypen einstellen

Für eine Weiterverarbeitung solltest du nun durch die Felder gehen, und die Datentypen einstellen. So interpretiert Power Query die Felder mit den Beträgen als Text und nicht als Zahl oder Währung. Auch viele Datenfelder werden oft nicht als solche erkannt. Klicke mit der rechten Maustaste auf den Titel der jeweiligen Spalte und navigiere auf “Typ ändern” und dann auf den jeweiligen Datentyp:

So können die Datentypen der Spalten angepasst werden

Über diesen Dialog kannst du auch Spalten entfernen, umbenenne, duplizieren etc.

Laden der Daten in eine Excel-Tabelle

Nun können wir diese Tabelle in Excel anzeigen lassen. Dafür kannst du auf “Datei -> Schliessen & Laden” klicken. Power Query erstellt nun einen neuen Tab in Excel mit einer Tabelle, in der die geladenen Daten angezeigt werden.

Das sieht anschliessen folgendermassen aus:

Ich empfehle, den Tabellennamen oben links im Ribbon “Entwurf” auf einen sprechenden Namen anzupassen.

Wenn sich nun daten geändert haben, können diese einfach über den Dialog “Daten -> Alle aktualisieren” aktualisiert werden.

Verschiedene Datenquellen kombinieren

Für die ersten Anwendungen reicht diese Tabelle wahrscheinlich auch. Sobald du aber Umsätze nach Kunden angezeigt haben möchtest, willst du die Namen der Kunden angezeigt haben. In der oben stehenden Tabelle stehen aber nur Zahlen. Diese verweisen auf eine weitere Tabelle in Bexio. Diese kannst du mit der gleichen Methode in MS Power Query anzeigen.

Kundennamen anzeigen

Damit du die Kundennamen in deiner Tabelle siehst, musst du die Tabelle mit den Kundendaten in Power Query laden. Das geschieht genau gleich wie vorher mit den Rechnungen. Du musst im ersten Schritt nur die URL auf “https://api.bexio.com/2.0/contact” ändern. Anschliessend solltest du in Power Query zwei Abfragen sehen:

Nun kannst du die beiden Abfragen kombinieren. Wähle die Abfrage “kb_invoice” aus und klicke auf “Abfragen zusammenführen”:

Folgender Dialog erscheint:

Dialog für das Zusammenführen von zwei Tabellen

Wähle als zweite Tabelle “contacts” aus. Klicke bei der Tabelle kb_invoice auf “contact_id” und bei der Tabelle “contacts” auf “id”. Nun zeigt dir Power Query unten an, für wie viele Zeilen es in der zweiten Tabelle einen Eintrag gefunden hat. Wenn für alle Zeilen der ersten Tabelle ein Eintrag vorhanden ist so wie im Beispiel, ist das ein gutes Zeichen. Klicke nun auf “OK”.

Jetzt erscheint in deiner Power-Query-Tabelle eine neue Spalte mit dem Namen “contact”. Diese kannst du wieder erweitern. Wähle hier nun die Felder aus, die du gerne angezeigt haben möchtest. Für den Namen der Kunden wählst du beispielsweise “name_1”:

Nun kannst du die Daten über “Schliessen & laden” ins Excel laden. In deiner Tabelle erscheint nun als letzte Spalte jeweils der Name deines Kunden.

Dieses Vorgehen kannst du für weitere Tabellen in Bexio verwenden. Besser noch: du kannst es auch für andere Datenquellen anwenden. Ich zeige das nun am Beispiel der Währungsumrechnung.

Währungsumrechnung

Bexio zeigt bei den Rechnungen standardmässig alles in der Originalwährung an. Das hat zur Folge, dass du die Beträge nicht wirklich vergleichen kannst. Damit dies möglich ist, solltest du die Beträge in deine Referenzwährung umrechnen. Wie das geht, siehst du hier.

Als erstes musst du rausfinden, in welcher Währung deine Rechnungen erstellt wurden. Leider zeigt Bexio dies nicht als Text an, sondern weist jeder Währung einfach eine Zahl zu. Dafür brauchen wir eine Hilfstabelle mit den Währungscodes.

Zweitens müssen wir die Umrechnungskurse laden. Eine Möglichkeit wäre es, diese wie die Währungscodes zu hinterlegen. Da Währungen aber gewissen Schwankungen unterliegen ist das nur bedingt clever. Zum Glück stellt die Eidgenössische Steuerverwaltung die Monatsmittelkurse ebenfalls per API kostenlos zur Verfügung. Diese Daten zapfen wir an.

Währungen anzeigen

Erstelle zuerst auf einen neuen Tab eine Tabelle in Excel, welche wie folgt aussieht:

Tabelle mit den Währungs-Codes

Klicke dann in die Tabelle rein und anschliessend im Ribbon “Daten” auf “Aus Tabelle/Bereich”:

Nun wird die vorher erstellte Tabelle mit den Währungskürzeln in Power Query geladen. Diese Tabelle kannst du nun wie vorher die “contacts”-Tabelle mit der Tabelle “kb_invoice” zusammenführen:

Nun zeigt uns Power Query für jeden Eintrag die entsprechende Währung als Kürzel an:

Währung umrechnen

Damit wir die Währung in unsere Referenzwährung Schweizerfranken umrechnen können, ziehen wir die Monatsmittelkurse der Eidgenössischen Steuerverwaltung ins Power Query rein. Definiere dafür eine neue Abfrage mit der URL http://www.pwebapps.ezv.admin.ch/apps/rates/estv/getavgxml ohne Authentisierung:

Dies lädt folgende Tabelle:

Die Spalte ganz links kannst du löschen. Die Spalten unter “devise” kannst du wie folgt erweitern:

Nun kannst du auch diese Tabelle mit der Tabelle “kb_invoice” zusammenführen:

Erweitere diese neue Spalte nun ausschliesslich mit der Spalte Kurs.

Den Datentyp musst du nun noch auf “Währung” anpassen.

Als letztes musst du noch den Kurs mit den Beträgen multiplizieren. Dazu erstellst du eine neue Spalte:

Multipliziere nun die gewünschte Spalte mit dem Umrechnungskurs:

Umrechnung in die Referenzwährung

Jetzt hast du eine neue Spalte mit dem Namen “Gross CHF” in der die Beträge umgerechnet in CHF erscheinen:

Dummerweise liefert die ESTV keinen Umrechnungskurs von CHF in CHF. Das ist zwar aus Sicht ESTV verständlich, führt aber dazu, dass uns bei Rechnungen in CHF ein Kurs von “null” angezeigt wird. Dies können wir aber beheben. Rechtsklick in den Titel der Spalte “kurs” und dann auf “Werte ersetzen”.

Nun ersetzen wir den Wert “null” mit 1.

Damit haben wir den Wechselkurs 1 für Rechnungen in CHF.

Fragen?

Hast du Fragen zu dieser Anleitung? Dann erreichst du mich am besten über meine Webseite unter https://antarius.ch.

--

--

Chris Burger
Digitalisierung für Startups und KMU

Entrepreneur, Visionär und Certified PINNACLE Business Guide. Ich helfe mittelständischen Unternehmer:innen, ihre Vision zu realisieren.