会計freeeからROICツリーを作ろう #PowerQuery

遅くなりました！今回のカレンダーのテーマで初めてfreeeを触りました。

これまでfreeeはSMBをセグメントに主戦場にされてきたのかなと思いますが、ユーザー企業の成長やfreee自体の成長に伴ってエンタープライズ一歩手前の会社がターゲットになってくると考え、「(税引前)ROICツリーを作ろう」をテーマにMicrosoft ExcelからfreeeのAPIを叩こうと思います。

[背景補足] 【保存版】外資系SaaSユニコーンの日本事業立ち上げから学ぶ、エンタープライズSaaSグロース戦略｜志村裕司｜note

まえおき

ROICツリー

会社規模拡大にともなって「経営と現場の意思疎通が難しいな」となってきたときに企業価値向上と各部門の活動の対応を数字で表現して目標を追っていく管理手法です。

ヤマシンフィルタの決算説明資料(2019)に詳しいです。

急成長企業だとこういった複雑な構成を取るかバーンレートとキャッシュをモニタしながらシンプルにトップラインを追うかはマネジメントの考え方次第ですが、会社規模が300人を超えてくると色々難しくなってくると聞きますし、ROICツリーみたいな考え方も大事なのかなと思ってます。

KPIは足し算と掛け算を併用しよう

ExcelでAPIを叩く

Excel 2016からはPower Query M 言語という強力な機能が使える様になりした。これはMicrosoft Power BIのデスクトップ版でも共通で使えるユニークな言語です。

今回は1ページのデータ取得程度にしか使いませんが、複数ページにまたがるjsonの取得などに非常に便利です。たとえばkintoneでは1回のAPIコールで500件のレコードまでしか取り出せませんが、これを複数ページつなげて取得するクエリも書くことができます。

Power BIでkintoneのレコードを取り出す(REST API) - 夜間飛行

ただ、今回書いてみて感じましたが永続的なアプリケーショントークンを発行するKintoneと比べて、きちんとしたOAuth2に従ってリフレッシュトークンの扱い等があるfreeeではプロトタイプにとどめておくのが良いのかもしれません。

ワークパッケージ

では前置きはこのくらいにしてワークパッケージです。

Excelでfreeeの貸借対照表と損益計算書の試算表を取得する
- 取得情報の設定シート作成
- freeeのAPIの呼び出し設定
読み出した情報からROICツリーKPIの計算
見た目を整える(今回はしない)

貸借対照表と損益計算書の試算表取得

取得情報設定シート

Configとでも名前をつけたシートに'ConfigTable'と名前をつけた表を作ります。
この中にはROIC計算をするターゲットの年次、事業所の他、アクセストークンやリフレッシュトークンを書き込みます。

そして、データタブからからのクエリを作成します。

そうするとPower Queryのエディタが開くのでAdvanced Editorを開いて

こちらのコードを入力します。テーブル名と参照行数でパラメータを取り出す関数です。

Code From How To Parameterize Your Power Query | How To Excel

let 
    Parameter=(TableName, RowNumber) =>
        let
            Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
            value = Source{RowNumber-1}[Value]
        in
            value
in Parameter

How To Parameterize Your Power Query | How To Excel

エディタで編集後は右端のクエリーの設定で関数名をfParameterと設定することを忘れないでください。

こうすると、他のクエリーの中で

FiscalYear = fParameter("ConfigTable", 1)

などとしてシートで定義した設定を読み出せるようになります。

Free APIの呼び出し

さて、いよいよfreee APIで試算表を取り出します。

貸借対照表の呼び出し

まずは賃貸貸借表からです。先程と同様に空のクエリーを作って、BalanceSheetとQueryを名付けます。取得したデータの表が含まれているシートは1.BSとでも名前をつけておきます。

let 
  // Author: Chachay https://blog.chachay.org/
  BaseUrl         = "https://api.freee.co.jp/api/1/reports/trial_bs?account_item_display_type=group&breakdown_display_type=account_item&company_id=",
  CompanyID       = fParameter("ConfigTable", 2),
  FiscalYear      = fParameter("ConfigTable", 1),
  Token           = fParameter("ConfigTable", 3),

  GetJson = (Url) =>
      let Options = [
            Headers=[
              Authorization="Bearer " & Token,
              Accept = "application/json",
              #"X-Api-Version" = "2020-06-15"
            ]
          ],
          RawData = Web.Contents(Url, Options),
          Source  = Json.Document(RawData),
          records = Source[trial_bs]
      in  records,

  Data        = GetJson(BaseUrl&Number.ToText(CompanyID)&"&fiscal_year="&Number.ToText(FiscalYear)),
  balances = Data[balances],
  #"Converted to Table" = Table.FromList(balances, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
   {"account_category_name", 
    "account_group_name", 
    "account_item_name",
    "hierarchy_level", 
    "closing_balance", 
    "account_item_id"
})
in
  #"Expanded Column"

クエリを実行すると、こんな雰囲気で取得できます。

損益計算書の呼び出し

もうわかってきましたね。空のクエリーから…です。2.PLとシートに名前をつけておきます。

let 
  // Author: Chachay https://blog.chachay.org/
  BaseUrl         = "https://api.freee.co.jp/api/1/reports/trial_pl?company_id=2749740&fiscal_year=",
  FiscalYear      = fParameter("ConfigTable", 1),
  Token           = fParameter("ConfigTable", 3),

  GetJson = (Url) =>
      let Options = [
            Headers=[
              Authorization="Bearer " & Token,
              Accept = "application/json"
            ]
          ],
          RawData = Web.Contents(Url, Options),
          Source  = Json.Document(RawData),
          records = Source[trial_pl]
      in  records,

  Data        = GetJson(BaseUrl&Number.ToText(FiscalYear)),
  balances = Data[balances],
  #"Converted to Table" = Table.FromList(balances, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
    {"account_category_id", 
     "account_item_name",
     "parent_account_category_id", 
     "parent_account_category_name",
     "account_category_name", 
     "total_line", 
     "hierarchy_level", 
     "closing_balance"})
in
  #"Expanded Column"

こんなんが取れます。

読み出した情報からROICツリーKPIの計算

ROICツリーについては様々な記事が出ていますので、そちらを参照していただくと良いと思います。
- ROICツリーとは？経営や投資に活かせるバリュードライバー分析のやり方 | １億人の投資術
- 企業価値評価：企業価値の向上策　３

ただ、Freeeがテスト用用意しているアカウントではダミーデータに人件費関連が入力されていません。本件ではダミーデータを作るところまで手を入れませんでしたが、「人件費」はその性格によって「原価」になったり、「研究開発費」になったり、「販売費」になったりetc.するので会社のビジネスによって適切に分けましょう。

最近ではAIブームで受託開発などを取る事業が多くありました。80万円/月などの報酬でスターエンジニアを雇い(原価)として月額単価500万円～/人月で販売する類のビジネスが多かったと思います。シリコンバレーだと研究開発に寄せるAI企業が多くてビジネスのやり方が違うなぁという個人的感想を持っています。

さて、そうこう言っているうちにツリーに入れる数字ができました。

ダミーデータ…なかなかやんちゃな経営指標が並んでいる企業だとわかりました。お給料も払ってないですよね。

見た目を整える

世界にはエクセルダッシュボード職人が多くいらっしゃって極めるとDashboard Excelくらいのものは作れるそうです。すごいですね。とても勝てる気がしないのですが、エクセルでもこういったダッシュボードに仕立てられるそうです。

Excelではじめる財務分析 (EXCELで作るROICダッシュボード作例)

私ならVue+Bootstrapにします。

あとがき

さて、トークンの更新（リフレッシュトークン）には言及しませんでしたが、M言語がなかなか癖があって工夫のしがいのある面白い言語なので、パズル感覚で取り組む余地を残しました！

こういった関数もあるので残期間を見ながらトークンの更新処理をしてみると良いと思います。

Solved: Converting UNIX time to Date in PowerBI for Deskto... - Microsoft Power BI Community

では！

参考情報

ExcelでPowerQueryを使ってデータ収集分析 - Qiita