Help us understand the problem. What is going on with this article?

freeeの取引データをBIツール(PowerBI)やOfficeツール(Excel/Access)で活用してみよう

More than 1 year has passed since last update.

はじめに

本記事では、freee株式会社が提供するクラウド会計ソフトfreeeの取引データをCData ODBC Drivers for RESTを利用してMicrosoftのBIツール(PowerBI Desktop)、および、Officeツール(Excel/Access)から利用する方法について解説します。

実現イメージ

今回はBI & Officeツールからfreee APIに接続しますが、それらのツールからは単純に freeeと接続することができません。それはBI & Officeツールがどのようにfreee API側へリクエストを投げるべきかの判断材料やメタデータの情報が無いためです。

image.png

そこで、CData REST ODBC Driverを用いて、このボトルネックを解決します。まず、BI & OfficeツールからはODBCインタフェースでSQL(Select文)をCData REST ODBC Driverに向けて発行してもらいます。そのリクエストを受け取った、CData REST ODBC Driverは、freeeのWebAPIのエンドポイントに対して、SQL文を解釈し、HTTPリクエスト形式に変換して発行します。リクエスト後、Jsonフォーマットで返ってきたデータセットはCData REST ODBC DriverがODBCインタフェースのResultsetに変換してBI & Officeツールに返します。

image.png

これにより、BI & OfficeツールからアドホックにSQLによるリクエストがあったタイミングで最新のデータを freee から取得することが可能です。それでは実際に、PowerBIやExcel、Accessからfreee内の取引データを取得してみたいと思います。

前提

freeeのAPIを利用するための準備

freee スタートガイド の「はじめに」の以下手順を実施して、AppIDとSecretを取得します。

image.png

CData ODBC Drivers for REST 製品のインストール、および、初期設定

CData ODBC Drivers for RESTWindows版のビルドをダウンロードします。ダウンロードすると、インストールファイル(.exe)がダウンロードされます。インストーラーを起動してソフトウェア使用許諾契約書を確認してデフォルトのままインストールします。

image.png

(注意) オンラインアクティベーションのため、インターネットへの接続が必要となります

インストールが完了すると、ODBCのDSN設定画面が起動します。一度、閉じてしまった場合には、WindowsのODBCデータソースアドミニストレーターを起動してください。Data Source Name(DSN)をセット(例:CData REST Source freee)して、下記の項目をセットします。

項目 備考
OAuthVersion 2.0
OAuthAccessTokenURL https://api.freee.co.jp/oauth/token
OAuthAuthorizationURL https://secure.freee.co.jp/oauth/authorize
OAuthClientId *********************************** freee側のアプリケーション登録時のApp ID
OAuthClientSecret *********************************** freee側のアプリケーション登録時のSecret
Callbackurl http://localhost
Location C:\Program Files\CData\freee スキーマファイルの格納パス
Format JSON
logfile C:\Program Files\CData\freee\cdatalog\freee.log (任意)CDataのログファイルを出力する場合、出力パスを指定
verbosity 3 (任意)CDataのログファイルを出力する場合、出力レベル(3)を指定

image.png

「接続のテスト」ボタンをクリックします。

ブラウザが起動して、freeeのログイン画面が表示されるので、freeeへのユーザIDおよびパスワードを入力してログインします。すると下記のような認証が出来た旨の画面と接続に成功して旨のダイアログが表示されれば、freeeへの一覧のOAuthによる認証認可のプロセスが完了したことになります。

image.png

「OK」ボタンをクリックして、「CData ODBC Driver for REST - DSN設定」の画面も「OK」ボタンをクリックして閉じます。

スキーマ定義の配置

Githubから「.rsd」ファイル群をダウンロードしてローカルディレクトリに配置します。

配置パスの例
C:\Program Files\CData\freee

PowerBI Desktopからの接続

事業所の確認

PowerBI Desktopの「データを取得」から「その他 > ODBC」を選択して「接続」ボタンをクリックします。

image.png

上記手順で作成したDSN(例:CData REST Source freee)を選択して「OK」ボタンをクリックします。

image.png

「ナビゲーター」ダイアログに移動するので、左側のツリーを開いて表示された表の中から「_companies(事業所)」テーブルを選択します。右側に、事業所一覧が表示されることを確認します。id(事業所ID)は後の手順で使用するので手元に控えておきます。

image.png

取引データ、および、各種マスターデータの確認

同じ手順で、今度は「_deals(取引)」テーブルを選択してデータを取得してみましょう。すると、「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示されます。

image.png

これは「_deals(取引)」テーブルに該当するAPIエンドポイントには、リクエスト時のパラメータとして事業所IDが必要です。今回は本事業所IDを指定していないため本エラーが発生しました。

image.png

(ご参考)freee API (収入/支出)一覧の取得

そこで、上記で取得した事業所IDをODBCのDSNにセットします。ODBCデータソースアドミニストレーターを開いて、DSNに下記のパラメータを追加してDSNを保存します。

追加する項目 備考
CustomUrlParams company_id=****** 上記手順で取得した事業所ID

再度、PowerBI Desktopの「ナビゲーター」ダイアログにて「deals(取引)」テーブルを選択して、「最新の情報に更新」ボタンをクリックします。今度は、正常に「deals(取引)」データがプレビューで表示することが出来ました。

image.png

続いて、他のテーブルも全て選択して「読み込み」ボタンをクリックします。

image.png

取引データを中心に、各マスターデータを以下の通り、結合キーを指定します。

image.png

カーディナリティ テーブル(左) 結合キー(左) テーブル(右) 結合キー(右)
多対一(*:1) _deals(取引) detailes_account_item_id _account_items(勘定科目) id
多対一(*:1) _deals(取引) conpany_id _companies(事業所) id
多対一(*:1) _deals(取引) partner_id _partners(取引先) id
多対一(*:1) _deals(取引) details_tax_id _taxes(税区分) id
多対一(*:1) _deals(取引) payments_from_walletable_id _walletables(口座)  id

ビジュアライズ

Tableauワークブックを保存して、「シート1」に移動します。ドラッグ&ドロップ操作で積み上げ棒グラフを選択して以下の通り、ディメンションおよびメジャー内の項目を右側ペインに配置します。

image.png

ディメンション・メジャー 項目 配置 備考
ディメンション(_account_items) category フィルタ 資本、差引損益計算、営業損益、経費
ディメンション(_account_items) name
ディメンション(_walletables) name 凡例
ディメンション(メジャー) ammount

取引データを、経費の勘定科目と口座毎に集計した積み上げグラフが作成出来ました。作成したファイルを保存してPowerBIを閉じます。

Excelからの利用

以下の手順はExcel2016を利用した場合のイメージです。他のExcelのバージョンをご利用の場合には各バージョンのイメージに読み替えてお進みください。Excelを起動して空白のブックを開きます。

「その他のデータソース」内の「データの接続ウィザードから」を開きます。

image.png

データ接続ウィザードが起動するので、「ODBC DSN」、作成したDSN(例:CData REST Source freee)を選択して「次へ」をクリックします。

image.png
 
image.png

データを取得するテーブルを選択します。今回は「_deals(取引)」を選択して次へ進みます。

image.png

データをインポートするセルを指定します。

image.png

Excelシートに取引データをインポートする事が出来ました。

image.png

(注意) 「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示される場合には、「PowerBI Desktopからの接続 - 取引データ、および、各種マスターデータの確認」記載の通りODBCのDSNに「CustomUrlParams」を追加してください。

「データ」タブ内の「更新」ボタンをクリックするとfreee内の最新の取引データを取得することが出来ます。必要に応じてExcelブックを保存してExcelを閉じます。

Accessからの利用

以下の手順はAccess2016を利用した場合のイメージです。他のAccessのバージョンをご利用の場合には各バージョンのイメージに読み替えてお進みください。Accessを起動して空のデータベースを開きます。

「新しいデータソース > 他のソースから > ODBCデータソース」を開きます。

image.png

「外部データの取り込み - ODBCデータソース」ダイアログが開くので、「リンクテーブルを作成してソース データにリンクする」を選択して「OK」ボタンをクリックします。

image.png

「データソースの選択」ダイアログで「コンピュータデータソース」内の作成したDSN(例:CData REST Source freee)を選択して「OK」ボタンをクリックします。

image.png

「テーブルのリンク」ダイアログで全テーブルを選択して「OK」ボタンをクリックします。

image.png

各テーブルの「固有レコード識別子の選択」ダイアログが表示されるので「id」を選択します。

image.png

緑色のアイコンのリンクテーブルが作成されたら左側のテーブルリスクから「REST_deals(取引)」を選択してダブルクリックして開きます。右側のペインに取引一覧が表示されることを確認します。

image.png

(注意) 「アクセス権がありません。存在しないかすでに削除された事業所です。」といったエラーメッセージが表示される場合には、「PowerBI Desktopからの接続 - 取引データ、および、各種マスターデータの確認」記載の通りODBCのDSNに「CustomUrlParams」を追加してください。

次に選択クエリーを作成します。

image.png

以下の通り、テーブル間のリレーションを作成します。

テーブル(左) 結合キー(左) テーブル(右) 結合キー(右) レコードを含める
_deals(取引) detailes_account_item_id _account_items(勘定科目) id REST_dealsを全レコード、 REST_account_itemsの同じ結合フィールドのレコードのみ
_deals(取引) conpany_id _companies(事業所) id REST_dealsを全レコード、 REST_account_itemsの同じ結合フィールドのレコードのみ
_deals(取引) partner_id _partners(取引先) id REST_dealsを全レコード、 REST_partnersの同じ結合フィールドのレコードのみ
_deals(取引) details_tax_id _taxes(税区分) id REST_dealsを全レコード、 REST_taxesの同じ結合フィールドのレコードのみ
_deals(取引) payments_from_walletable_id _walletables(口座)  id REST_dealsを全レコード、 REST_walletablesの同じ結合フィールドのレコードのみ

以下の通り、表示する項目を作成します。

フィールド テーブル 集計 並び替え 表示
names REST_account_items グループ化 ON
names REST_walletables グループ化 ON
account REST_deals 合計 ON

クエリーを実行すると、勘定科目毎・口座毎の合計金額が表示されます。

image.png

(補足)freee用スキーマ定義の生成方法

上記手順「スキーマ定義の配置」で使用したスキーマファイル「.rsd」ファイルは下記のストアドプロシージャを実行して生成することも可能です。ストアドプロシージャを実行できるツール(VisualStudioのサーバーエクスプローラーなど)から実行ください。

EXEC CreateSchema @TableName='companies', @URI='https://api.freee.co.jp/api/1/companies', @XPath='/companies', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='companies.rsd';
EXEC CreateSchema @TableName='deals', @URI='https://api.freee.co.jp/api/1/deals', @XPath='/deals', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='deals.rsd';
EXEC CreateSchema @TableName='partners', @URI='https://api.freee.co.jp/api/1/partners', @XPath='/partners', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='partners.rsd';
EXEC CreateSchema @TableName='account_items', @URI='https://api.freee.co.jp/api/1/account_items', @XPath='/account_items', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='account_items.rsd';
EXEC CreateSchema @TableName='taxes', @URI='https://api.freee.co.jp/api/1/taxes', @XPath='/taxes', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='taxes.rsd';
EXEC CreateSchema @TableName='walletables', @URI='https://api.freee.co.jp/api/1/walletables', @XPath='/walletables', @Format='JSON', @FileLocation='/Applications/CData/freee/', @FileName='walletables.rsd';

まとめ

本記事では、freee内の最新の取引データをCData Drivers for RESTを利用してBIツール(PowerBI Desktop)、および、Officeツール(Excel/Access)から利用する方法について説明しました。前提として必要なサービスやソフトウェアは試用版があるので、是非、お試しください。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away