LoginSignup
40
40

More than 3 years have passed since last update.

@s_higeruinエバーセンス

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

Last updated at Posted at 2020-07-07

はじめに

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

今回はこれから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
 を利用します。

image.png

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

（取得結果のイメージ）
image.png

貸借対照表（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
 を利用します。

image.png

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

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

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

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の結果も取得できます。

財務分析

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

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

（結果イメージ）
image.png

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）の前年度比較ができた
  • 財務分析が自動化できた
40
40
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
40
40