freee × GASで財務分析をしてみる

はじめに

はじめまして。
自分はアプリエンジニア兼、中小事業者のコンサルをやっています。

今回はこれからfreee apiを使って日常業務の自動化、効率化を図っていこうと
考えている方のチュートリアルも兼ねて、記事を書いています。

そのため、
「freee apiに初めて触る状態」からスタートし
「スプレッドシートで試しに一つ財務分析をしてみる」をゴールとします。

この記事を書くにあたって、以下の記事を参考にさせていただきました。
freee x GASでBS明細の作成を自動化したお話
会計freee×GASで取引先別の月次貸借対照表データを取得してみる

対象の分析

財務分析の基本中の基本、
貸借対照表（BS）と損益計算書（PL）を使った
財務の 安全性分析・効率性分析・収益性分析を行っていきます。

この分析は顧客の財務状況把握に、絶対に行うので自動化しておきたく。
期末に自社の状況を共有する資料を作成する方なども
作業を自動化して便利に活用いただけるかと思います。

財務分析の詳細は freee社のこちらの記事に載っています。
貸借対照表を使った財務分析のやり方

スプレッドシートの連携

まずは、スプレッドシート(GAS）とfreeeを連携させてあげる必要があります。
その作業は、こちらのブログがめちゃめちゃわかりやすかったので
説明の移譲先とさせていただきます。

【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）

これで、スプレッドシートでfreee apiの結果を利用する準備が整いました。

貸借対照表（BS）を取得する

財務分析に必要な貸借対照表（BS）を取得していきます。
せっかくなので、せっかくなので2期分取得して、前年比較も確認してみます。

作業の流れ

まずは貸借対照表（BS）を取得していきます。
方法はAPIドキュメント freee developer 会計APIリファレンス を参照していきます。

BS はエンドポイント GET /api/1/reports/trial_bs_two_years
を利用します。

定義はAPIドキュメントに書いてあるとおりで、今回は
total_line = true である合計勘定の
account_item_name : 勘定科目名と closing_balance: 期末残高
last_year_closing_balance：前年度期末残高、year_on_year：前年比 を取得します。

（取得結果のイメージ）

貸借対照表（BS）取得コード

公式のサンプルコードや、冒頭にリンクを貼った記事たちを参考にしています。

// これが実行するメソッド
function createBSSheet() {
  //シートに項目を作成
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var bsSheet = ss.getSheetByName('BS'); // BSというシートを作成しておいてください。
  var titleList = [['勘定科目名', '期末残高', '前年度期末残高', '前年比']];

  bsSheet.getRange(1, 2, titleList.length, titleList[0].length).setValues(titleList);
  
  setBSToSheet()
}


function setBSToSheet() {
    // 合計勘定のリストを取得
    bsList = getTotalLineBSItems();
 
    //シートに転記
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var bsSheet = ss.getSheetByName('BS');
    bsSheet.getRange(2, 2, bsList.length, bsList[0].length).setValues(bsList);
       
}

function getTotalLineBSItems() {  
  //合計勘定(total_line = true)ならカテゴリデータを返す
  // それ以外のデータは無視する
  var data = getBs().map(function(account){
  
    //合計勘定の場合  
      if (account.total_line) {
        return [[
          // 勘定科目名
          account.account_category_name, 
          // 期末残高
          account.closing_balance,
          // 前年度期末残高
          account.last_year_closing_balance,
          // 前年比
          account.year_on_year,          
      ]];
      }
  });

  //配列の次元削減して返す
  var reducedArray = data.reduce(function (prev, post) {
    prev.push.apply(prev, post);
    return prev;
  });
  return reducedArray;
}


function getBs() {
      //トークン取得
    var freeApp = getService();
    var accessToken = freeApp.getAccessToken();

    //取得情報のパラメータ指定。
    var url = 'https://api.freee.co.jp/api/1/';
    var targetItem = 'reports/trial_bs_two_years?';
    var companyId = 'company_id=' + "あなたの事業所ID";
    var fiscalYear = '&fiscal_year=' + "対象の年（例：2020）";
    var startMonth = '&start_month=' + "対象の開始月（例：1）";
    var endMonth = '&end_month=' + "対象の終了月（例：12）";
    var accountItemDisplayType = '&account_item_display_type=group'; // 勘定科目の表示を決算書表示に指定
    var breakdown = '&breakdown_display_type=' + 'account_item'; // 内訳を勘定科目に指定

    //url結合
    url += targetItem + companyId + fiscalYear + startMonth + endMonth + accountItemDisplayType + breakdown;

    //option, headers
    var options = {
        'method': 'get',
        'headers': {
            'Authorization': 'Bearer ' + accessToken
        }
    };
    //取得したデータをJsonParseしてバランス(balances)の部分だけ取得
    var res = UrlFetchApp.fetch(url, options).getContentText();
    var resBs = JSON.parse(res).trial_bs_two_years.balances;
    return resBs;
}

これで createBSSheet() を実行すると
スプレッドシートに事前に用意した BS シートに勘定科目名と、その期末残高が表示されているはずです。

損益計算書（PL）を取得する

損益計算書（PL）も同様に、二期分を取得して
前年度比較も出していきます。

作業の流れ

PL はエンドポイント GET /api/1/reports/trial_pl_two_years
を利用します。

PLの方でも同様に
account_category_name：上位勘定科目カテゴリー名、
account_item_name : 勘定科目名 、 closing_balance : 期末残高
last_year_closing_balance：前年度期末残高、year_on_year：前年比 を表示していきます。

（取得結果のイメージ）
※サンプルデータが昨年分なく1年分の表示になっています。

損益計算書（PL）を取得コード

.js

function createPLSheet() {
  
  //シートに項目を作成
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var bsSheet = ss.getSheetByName('PL'); // PLというシートを作成しておいてください。
  var titleList = [['勘定科目カテゴリー名', '勘定科目名', '期末残高', '前年度期末残高', '前年比']];

  bsSheet.getRange(1, 2, titleList.length, titleList[0].length).setValues(titleList);
  
  setPLToSheet()
}

function setPLToSheet() {
    //シートに転記
  
   var plList = getPLItems();
    Logger.log(plList);
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var plSheet = ss.getSheetByName('PL');
    plSheet.getRange(2, 2, plList.length, plList[0].length).setValues(plList);
       
}

function getPLItems() {  
  //hierarchy_levelが3以上は細かいデータなので1~2を利用する
  var data = getPL().map(function(account){

    //合計勘定の場合  
        return [[
          // 上位勘定科目カテゴリー名
          account.account_category_name,
          // 勘定科目名
          account.account_item_name,
          // 期末残高
          account.closing_balance,
          // 前年度期末残高
          account.last_year_closing_balance,
          // 前年比
          account.year_on_year
      ]];
  });

  //次元削減して返す
  var reducedArray = data.reduce(function (prev, post) {
    prev.push.apply(prev, post);
    return prev;
  });

  return reducedArray;
}

function getPL() {
      //トークン取得
    var freeApp = getService();
    var accessToken = freeApp.getAccessToken();

    //取得情報のパラメータ指定。
    var url = 'https://api.freee.co.jp/api/1/';
    var targetItem = 'reports/trial_pl_two_years?'; // BSとほとんど一緒だけど、当然エンドポイントが異なる
    var companyId = 'company_id=' + "あなたの事業所ID";
    var fiscalYear = '&fiscal_year=' + "対象の年（例：2020）";
    var startMonth = '&start_month=' + "対象の開始月（例：1）";
    var endMonth = '&end_month=' + "対象の終了月（例：12）";
    var accountItemDisplayType = '&account_item_display_type=group'; // 勘定科目の表示を決算書表示に指定
    var breakdown = '&breakdown_display_type=' + 'account_item'; // 内訳を勘定科目に指定

    //url結合
    url += targetItem + companyId + fiscalYear + startMonth + endMonth + accountItemDisplayType + breakdown;

    //option, headers
    var options = {
        'method': 'get',
        'headers': {
            'Authorization': 'Bearer ' + accessToken
        }
    };
    //取得したデータをJsonParseしてバランス(balances)の部分だけ取得
    var res = UrlFetchApp.fetch(url, options).getContentText();
    var resPL = JSON.parse(res).trial_pl_two_years.balances;
    return resPL;
}

これでcreatePLSheet()を実行するとPLの結果も取得できます。

財務分析

ここまでくれば、あとは単純な計算です。
手順簡略化のために、一応スクリプトを作成します。

あとは前年比較するなり、業界平均と比較するなりして分析にご活用ください。

（結果イメージ）

.js

function setAnalysisToSheet() {
  //シートに項目を作成
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var plSheet = ss.getSheetByName('PL');
  var bsSheet = ss.getSheetByName('BS');
  
  // 売上高
  var amountOfSales = plSheet.getRange(findRow2(plSheet, '売上高', 3), 4, 1, 1).getValue();
  // 売上総利益
  var grossProfit = plSheet.getRange(findRow2(plSheet, '売上総損益金額', 2), 4, 1, 1).getValue();
  // 営業損益
  var operatingProfit = plSheet.getRange(findRow2(plSheet, '営業損益金額', 2), 4, 1, 1).getValue();
  // 経常利益
  var salesOrdinary = plSheet.getRange(findRow2(plSheet, '経常損益金額', 2), 4, 1, 1).getValue();
  // 売上原価
  var costOfSales = plSheet.getRange(findRow2(plSheet, '売上原価', 2), 4, 1, 1).getValue();
  // 経費
  var expense = plSheet.getRange(findRow2(plSheet, '販売管理費', 2), 4, 1, 1).getValue();
  // 固定資産
  var fixedAsset = bsSheet.getRange(findRow2(bsSheet, '固定資産', 2), 3, 1, 1).getValue();
  // 流動資産    
  var currentAssets = bsSheet.getRange(findRow2(bsSheet, '流動資産', 2), 3, 1, 1).getValue();
  // 流動負債
  var currentLiabilities = bsSheet.getRange(findRow2(bsSheet, '流動負債', 2), 3, 1, 1).getValue();
  // 当座資産(必要なら受取手形や有価証券も加味)
  var cashAndCashEquivalents = bsSheet.getRange(findRow2(bsSheet, '現金及び預金', 2), 3, 1, 1).getValue() + bsSheet.getRange(findRow2(bsSheet, '売掛金', 2), 3, 1, 1).getValue();
  // 自己資本
  var netWorth = bsSheet.getRange(findRow2(bsSheet, '資本金', 2), 3, 1, 1).getValue() + bsSheet.getRange(findRow2(bsSheet, '資本準備金', 2), 3, 1, 1).getValue() + bsSheet.getRange(findRow2(bsSheet, '資本剰余金', 2), 3, 1, 1).getValue();
  // 総資産
  var totalCapital = bsSheet.getRange(findRow2(bsSheet, '負債及び純資産', 2), 3, 1, 1).getValue();
  // 負債
  var debt = bsSheet.getRange(findRow2(bsSheet, '負債', 2), 3, 1, 1).getValue();
  
  // 流動比率
  var currentRatio = saftyDivision(currentAssets, currentLiabilities);
  // 当座比率
  var cashEquivalentsRatio = saftyDivision(cashAndCashEquivalents, currentLiabilities);
  // 固定比率
  var fixedRatio = saftyDivision(fixedAsset, netWorth);
  // 自己資本比率
  var capitalAdequacyRatio = saftyDivision(netWorth, totalCapital);
  // 負債比率
  var debtRatio = saftyDivision(debt, netWorth);
  // 売上高総利益率
  var grossProfitMargin = saftyDivision(grossProfit, amountOfSales);
  // 売上高営業利益率
  var operatingProfitMargin = saftyDivision(operatingProfit, amountOfSales);
  // 売上高経常利益率
  var ordinaryIncomeMargin = saftyDivision(salesOrdinary, amountOfSales);
  // 売上原価率
  var costOfSalesRatio = saftyDivision(costOfSales, amountOfSales);
  // 売上高販管費率
  var sgaRatio = saftyDivision(expense, amountOfSales);
  // 固定資産回転率
  var fixedAssetTurnover = saftyDivision(amountOfSales, fixedAsset);
  // 総資本回転率
  var TotalCapitalTurnover = saftyDivision(amountOfSales, totalCapital);
  
  // シートに結果を転記
  var resultSheet = ss.getSheetByName('分析'); // 分析というシートを作成しておいてください。
  
  
  var resultColomns = [
    ['流動比率（％）',currentRatio], ['当座比率（％）',cashEquivalentsRatio], ['固定比率（％）',fixedRatio], ['自己資本比率（％）',capitalAdequacyRatio], ['負債比率（％）',debtRatio], 
    ['売上高総利益率（％）',grossProfitMargin], ['売上高営業利益率（％）',operatingProfitMargin], ['売上高経常利益率（％）',ordinaryIncomeMargin], ['売上原価率（％）',costOfSalesRatio], ['売上高販管費率（％）',sgaRatio],
    ['固定資産回転率（回）',fixedAssetTurnover], ['総資本回転率（回）',TotalCapitalTurnover],
  ];
  resultSheet.getRange(2, 2, resultColomns.length, resultColomns[0].length).setValues(resultColomns);
  
}

// 値を検索して行を返す
function findRow2(sheet,val,col){
  var lastRow=sheet.getDataRange().getLastRow(); //対象となるシートの最終行を取得
  for(var i=1;i<=lastRow;i++){
    if(sheet.getRange(i,col).getValue() === val){
      return i;
    }
  }
  return 0;
}

// 0で除算する場合は0を返却する割り算
function saftyDivision(x, y) {
  if (y == 0) {
    return 0;
  } else {
    return x / y;
  } 
}

結果

• 貸借対照表（BS）の前年度比較ができた
• 損益計算書（PL）の前年度比較ができた
• 財務分析が自動化できた