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

## はじめに

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

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

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

この記事を書くにあたって、以下の記事を参考にさせていただきました。
[freee x GASでBS明細の作成を自動化したお話](https://qiita.com/srknra/items/8be61a2e6a01c80652a9)
[会計freee×GASで取引先別の月次貸借対照表データを取得してみる](https://qiita.com/Kirin8210/items/4ae08a4975580aab2297)


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

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

財務分析の詳細は freee社のこちらの記事に載っています。
[貸借対照表を使った財務分析のやり方](https://www.freee.co.jp/kb/kb-accounting/business-analysis-with-bs/)



## スプレッドシートの連携

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

[【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）](https://moripro.net/freee-gas-api/)

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

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

### 作業の流れ

まずは貸借対照表（BS）を取得していきます。
方法はAPIドキュメント [freee developer 会計APIリファレンス](https://developer.freee.co.jp/docs/accounting/reference#/) を参照していきます。

BS はエンドポイント [GET /api/1/reports/trial_bs_two_years
](https://developer.freee.co.jp/docs/accounting/reference#/Trial%2520balance) を利用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25725/170886b4-fb88-df8e-68d2-7600e56c6437.png)


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

（取得結果のイメージ）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25725/e0d81921-e337-dd67-90c7-f7a09a98fd54.png)

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

[公式のサンプルコード](https://support.freee.co.jp/hc/ja/articles/115005287286--freee-API-GAS%E3%82%92%E7%94%A8%E3%81%84%E3%81%A6Google%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88%E3%81%A8%E9%80%A3%E6%90%BA%E3%81%99%E3%82%8B#4)や、冒頭にリンクを貼った記事たちを参考にしています。


```js
// これが実行するメソッド
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
](https://developer.freee.co.jp/docs/accounting/reference#/Trial%2520balance) を利用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25725/074f2aec-b829-9c18-4b88-65a6945899b7.png)


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

（取得結果のイメージ）
※サンプルデータが昨年分なく1年分の表示になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25725/8b42d64a-69dc-0baa-9129-0c7f3d7af117.png)

### 損益計算書（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の結果も取得できます。

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

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

（結果イメージ）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/25725/e77de142-8a1f-fcd1-4e58-961490332f47.png)

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