データインポートガイド
現在、電子決済が普及しており、銀行や証券会社、決済アプリなどの取引プラットフォームでは、通常、過去の取引記録を出力することが可能です。そのため、各取引を手動で記録する必要はほとんどありません。しかし、プラットフォームごとに形式が異なる取引記録をTataruBookに一括インポートする方法は、記帳効率に大きく影響を及ぼす重要な課題となっています。
本ページでは、取引記録データの変換およびインポートする方法を提供し、参考にしていただければ幸いです。
取引記録をExcelに変換する
取引プラットフォームから出力された取引明細書は、テキスト、CSVファイル、Excelファイル、PDFファイルなど、さまざまな形式で提供される場合があります。取引明細書がCSVファイルまたはExcelファイルの場合、このステップは省略できることが多いです。それ以外の場合は、まず取引明細書の内容をExcelテーブルに変換する必要があります。
Excelテーブルの内容は、以下の要件を満たす必要があります。
- 各行は１件の取引のみを表していること。
- 取引記録は時間順に昇順で並んでいること（特に同じ日に複数の取引がある場合、単に日付で並べ替えるだけでは正しい順序にならないことがあります）。
残念ながら、銀行の取引明細書や証券取引報告書は、PDF形式やHTML 形式で提供されることがよくあります。この場合、アカウント明細から取引記録を抽出し、Excel形式に変換する必要があります。このプロセスに固定された方法はありませんが、さまざまな解決策があります。正規表現やファイル変換ツール、オンラインサービスなどを利用することができます。Googleで「取引明細書をExcelに変換する」と検索すれば、この作業を行うための多くの手順が見つかります。
例としてのテーブルを以下に示します。この例は、クレジットカード取引明細書の一部を模したものです。実際のアカウント明細書は、この例とは異なる場合があります。列の数が増減したり、列の順序が異なることも考えられます。ただし、テーブルが上記の２つの要件を満たしていれば、後続の手順を適宜調整するだけで、インポートは最終的に完了させることができます。
| 決済日 | 支払日 | 利用内容 | 利用金額 |
|---|---|---|---|
| 21/07/23 | 20/07/23 | ラストスタンド | 32.55 |
| 28/07/23 | 27/07/23 | ラストスタンド | 45.45 |
| 05/08/23 | 04/08/23 | マンダヴィル・ゴールドソーサー | 690.00 |
| 15/08/23 | 14/08/23 | モーグリ銀行からの支払い | -150.00 |
| 15/08/23 | 14/08/23 | シャーレアン銀行からの支払い | -150.00 |
| 15/08/23 | 14/08/23 | シャーレアン銀行からの支払い | -150.00 |
この手順では、Excelのセルには余分なスペース、タブ、改行、その他の不要な文字が含まれていないか注意深く確認する必要があります。これらの文字が含まれていると、後続の処理手順が失敗する原因となる可能性があります。また、これらの文字は Excel上で表示されないため、問題の特定がより困難です。アカウント明細書を変換した後は、これらの余分な文字をできるだけ早く削除する必要があります。
再利用可能な変換テンプレートを作成する
新しいExcelファイルを作成し、最初のワークシートを生データと命名します。そのワークシートに、前の手順で取得したデータを貼り付けてください。
このExcelファイルは、このデータ形式を処理するための変換テンプレートになります。以後、この形式のデータについては生データのワークシートに貼り付けるだけで、変換が自動的に完了します。つまり、本ページで説明しているほとんどの手順は、特定の取引プラットフォームから出力されたデータを初めて処理する際にのみ必要です。変換テンプレートを一度作成してしまえば、今後同じようなデータを処理する際には、大部分の手順が自動的に実行されるようになります。
取引プラットフォームによってデータ形式が異なる場合があるため、通常それぞれのプラットフォームに対応した専用の変換テンプレートを作成する必要があります。
パラメータを設定する
変換テンプレート内で、生データの次にパラメータという名前の新しいワークシートを作成します。
次に、生データを観察し、各取引について、取引相手のアカウントを決定するための情報を特定します。前文の例では以下のようになります。
利用内容という列の内容がラストスタンドの場合、取引相手のアカウントは飲食費用という外部アカウントになります。利用内容という列の内容がマンダヴィル・ゴールドソーサーの場合、取引相手のアカウントは娯楽費用という外部アカウントになります。利用内容という列の内容がモーグリ銀行からの支払いの場合、取引相手のアカウントはモーグリ銀行普通預金という内部アカウントになります。利用内容という列の内容がシャーレアン銀行からの支払いの場合、取引相手のアカウントはシャーレアン銀行という内部アカウントになります。
これらのルールに基づいて、利用内容と取引相手のアカウントとの対応関係を次のようにパラメータというワークシートに入力します。
| ラストスタンド | 飲食費用 |
| マンダヴィル・ゴールドソーサー | 娯楽費用 |
| モーグル銀行からの支払い | モーグリ銀行普通預金 |
| シャーレアン銀行からの支払い | シャーレアン銀行普通預金 |
生データに日々の消費や振り込みが多数含まれている場合、このステップの処理は、かなり個別化されたものになることがあります。たとえば、ある取引を飲食費用として判断するにはどうすればよいでしょうか。特定のレストランでほとんどの食事をしている場合、これらの取引記録は特定の列に同じ値が記録されている可能性があり、パラメーターワークシートにその列の特徴と飲食費用との対応関係を追加することができます。しかし、この方法だけではすべてのデータを処理するには不十分であることが多いです。一部の特定の支出や収入については、手動での対応が必要になる場合があります。
必要な情報を取得し変換する
生データには、例の決済日など、データベースファイルにインポートする必要のない列が含まれている場合があります。当然ながら、必要だと判断した情報についてはpostingsテーブルのcommentフィールドに記入することも可能です。ただし、最も重要なのは、生データから取引に関する重要な情報を取得することです。
変換テンプレートのパラメータ部分の後に、新しいワークシートを作成して中間結果と命名します。そのワークシートの最初の行に、以下の列名を記載します。
| trade_date | this_account | amount | other_account | comment | other_change |
次に、生データの構造に基づいて、データを取得し変換するために、列ごとに関数を設計する必要があります。ここでは、ワークシートの2行目 (つまり、データ内容の最初の行) に記載する各セルの関数を例として説明します。
たとえば、trade_dateは取引日を表します。生データにおける取引日は支払日列 (列B) に記録されています。ただし、生データの支払日は YYYYMMDD形式で記載されているため、これをExcelの日付形式に変換する必要があります。このために設計された関数は以下のようになります: = DATE( CONCAT("20",RIGHT('生データ'!B2,2)),MID('生データ'!B2,4,2),LEFT('生データ'!B2,2))。この関数では、文字列操作関数と日付関数を使用しています。なお、生データの日付形式が例と異なる場合は、状況に応じて関数を適切に調整する必要があります。
this_accountは、生データですべての取引が発生したアカウントを指します。つまり、取引プラットフォームに対応するアカウントです。本例では、そのアカウント名をシャーレアン銀行クレジットカードと仮定し、その名称をthis_account列に入力します。this_accountの値はすべての取引が同一であるため、関数を使用する必要はありません。
異なる家族が同一の証券会社で口座を開設している場合など、同じ取引プラットフォームに複数のアカウントが存在する場合は、これらのアカウントが生成する取引記録は同一のデータ形式となります。そのため、一つの変換テンプレートを使用して処理することが可能です。このような場合、アカウント名をパラメータワークシートとしてParametersワークシートに記入し、this_account列で参照する方法をお勧めします。
amountは、取引におけるthis_accountアカウントの残高変化額を表し、生データの列Dに記載された利用金額が参照されます。クレジットカードの場合、利用金額は借入額を示すため、資産の変化額はアカウント明細書に記載された利用金額の反数になることに注意する必要があります。そのため、この列の関数は次のようになります: =-'生データ'!D2。
other_accountは、取引における取引相手のアカウント名を示します。パラメータを設定するの手順で設定されたパラメータに基づいて、この列の関数は次のようになります: =VLOOKUP('生データ'!C2,Parameters!A:B,2,FALSE)。
commentは、取引に関するコメントや説明を記載する列です。この列に入力されるデータの内容はユーザーの設定によって異なります。例では、利用内容を取得し、そのままcommentに直接入力します: ='生データ'!C2。
other_changeは、取引における取引相手のアカウントの残高変化額を示します。本例では、取引に関与する２つのアカウントが同じ資産を保持しているため、この列はすべて空欄になります。この場合、TataruBookは取引相手のアカウントの残高変化額をamountの反数として扱います。ただし、この条件を満たさない取引（たとえば株式取引など）がある場合は、other_changeに情報を入力する必要があります。
| 列 | 関数 |
|---|---|
| trade_date | = DATE( CONCAT("20",RIGHT('生データ'!B2,2)),MID('生データ'!B2,4,2),LEFT('生データ'!B2,2)) |
| this_account | シャーレアン銀行クレジットカード |
| amount | =-'生データ'!D2 |
| other_account | = VLOOKUP( '生データ'!C2,パラメータ!A:B,2,FALSE) |
| comment | ='生データ'!C2 |
| other_change | (空欄) |
データ内容の最初の行にすべての関数を入力した後、Excelのオートフィル機能を使用して他の行にも対応する関数を適用することで、次のように計算結果を取得できます。
| trade_date | this_account | amount | other_account | comment | other_change |
|---|---|---|---|---|---|
| 2023/7/20 | シャーレアン銀行クレジットカード | -32.55 | 飲食費用 | ラストスタンド | |
| 2023/7/27 | シャーレアン銀行クレジットカード | -45.45 | 飲食費用 | ラストスタンド | |
| 2023/8/4 | シャーレアン銀行クレジットカード | -690 | エンターテインメント | マンダヴィル ゴールドソーサー | |
| 2023/8/14 | シャーレアン銀行クレジットカード | 150 | モーグリ銀行現在 | モーグル銀行からの支払い | |
| 2023/8/14 | シャーレアン銀行クレジットカード | 150 | シャーレアン銀行普通預金 | シャーレアン銀行からの支払い | |
| 2023/8/14 | シャーレアン銀行クレジットカード | 150 | シャーレアン銀行普通預金 | シャーレアン銀行からの支払い |
このステップにおける関数の設計は、実際のデータ形式に依存するため、Excel関数ファイルを頻繁に参照する必要がある場合があります。Excelの関数に詳しくない場合は、検索エンジンを利用するか、AIに助けを求めることを検討してください。
中間結果の正確性を確認する
ここでは、中間結果ワークシートのデータを手動で確認する必要があります。生データに含まれる各取引は常に厳密に一貫したルールに従っているとは限らず、データに異常が発生する場合があるため、手動で確認し、適切に処理する必要があります。ただし、データを修正する際は、生データのワークシートのみを変更し、中間結果のワークシートには手を加えないでください。そうしないと、既存の関数が破損し、変換テンプレートを再利用できなくなります。
元のアカウントと目的アカウントを選択する
変換テンプレートの中間結果の後ろに新しいワークシートを作成し、最終結果と命名します。そのワークシートの最初の行に、postingsテーブルのインポート要件に従い、以下の列名を記載します。
| posting_index | trade_date | src_account | src_change | dst_account | comment | dst_change |
dst_changeはposting_extrasテーブルのフィールドではなく、postingsテーブルのフィールドです。ただし、関連テーブルに自動挿入する機能を利用することで、TataruBookは両方のテーブルに関連するデータを同時に挿入できるため、取引記録をインポートする際にこの列が必要です。
最終結果において、posting_index列は空のままにしてください(理由については、自動生成されたインデックス フィールドを参照してください)。また、trade_date列とcomment列のデータは、中間結果に対応する列のデータをそのまま参照できます。src_accountやsrc_change、dst_account、 dst_changeの4列の内容は、各取引のキャッシュフローの方向に基づいて確定する必要があります。
中間結果を観察すると、amountの値がマイナスの場合、「src_account」(すなわち資金が流出するアカウント) はthis_account、一方dst_account (すなわち資金が流入するアカウント) はother_accountであることがわかります。逆に、amount列の値がプラスの場合、src_accountは other_account、そしてdst_accountは this_accountであることがわかります。つまり、取引の資金変動額の符号に基づき、２つのアカウントを交換する必要があるかどうかを判断する必要があります。
しかし、amountが\(0\)の場合はどうなるでしょうか。本例では、amountが\(0\)の取引はありませんが、実際に株式分割または株式併合が発生する場合、対応する取引で株式数のみが変動し、取引金額が\(0\)になる可能性があります。これらのケースに対応するためには、変換テンプレートを次のように調整するのが最適です。すなわち、amountが\(0\)の場合、other_change値がプラスであるかマイナスであるかに基づいてキャッシュフローの方向を決定します。
中間結果にother_change値が存在する場合、src_change列とdst_change列の計算方法は、src_account列とdst_account列の計算方法と似ており、src_account列の計算結果を基に、これらの２列の数値を交換するかどうかを判断できます。ただし、もし中間結果にother_changeが空欄の場合、最終結果のdst_change列も空欄にしなければならなりません。そして、src_changeについては、amount的の値をそのまま使用するか、あるいはamountの反数を使用するかを決定する必要があります（src_changeは常に\(0\)以下であるため）。
このように、最終結果ワークシートの2行目 (すなわち、データ内容の最初の行)にある各列の関数は、以下次の通りです。
| 列 | 関数 |
|---|---|
| posting_index | |
| trade_date | =中間結果!A2 |
| src_account | =IF(中間結果!C2<0,中間結果!B2,IF(中間結果!C2>0,中間結果!D2,IF(中間結果!F2<0,中間結果!D2,中間結果!B2))) |
| src_change | =IF(中間結果!F2="",IF(中間結果!B2=C2,中間結果!C2,-中間結果!C2),IF(中間結果!B2=C2,中間結果!C2,中間結果!F2)) |
| dst_account | =IF(中間結果!B2=C2,中間結果!D2,中間結果!B2) |
| comment | =中間結果!E2 |
| dst_change | =IF(中間結果!F2="","",IF(中間結果!B2=C2,中間結果!F2,中間結果!C2)) |
これらの関数は少し複雑に見えるかもしれませんが最終結果ワークシートの関数は、ほぼすべての変換テンプレートで再利用可能です。中間結果ワークシートが、前の手順に厳密に従った固定形式で生成されている限り、最終結果ワークシートのすべての関数は、既存の変換テンプレートから直接コピーするだけで利用できます。
これらの関数をすべての行に入力すると、以下の計算結果が得られます。
| posting_index | trade_date | src_account | src_change | dst_account | comment | dst_change |
|---|---|---|---|---|---|---|
| 2023/7/20 | シャーレアン銀行クレジットカード | -32.55 | 飲食費用 | ラストスタンド | ||
| 2023/7/27 | シャーレアン銀行クレジットカード | -45.45 | 飲食費用 | ラストスタンド | ||
| 2023/8/4 | シャーレアン銀行クレジットカード | -690 | エンターテインメント | マンダヴィル ゴールドソーサー | ||
| 2023/8/14 | モーグリ銀行現在 | -150 | シャーレアン銀行クレジットカード | モーグル銀行からの支払い | ||
| 2023/8/14 | シャーレアン銀行普通預金 | -150 | シャーレアン銀行クレジットカード | シャーレアン銀行からの支払い | ||
| 2023/8/14 | シャーレアン銀行普通預金 | -150 | シャーレアン銀行クレジットカード | シャーレアン銀行からの支払い |
インポート済みの取引記録を識別する
上記の例では、最後の３件の取引は、それぞれ別のアカウントからシャーレアン銀行クレジットカードアカウントへの振替です。通常、これらの取引記録は、取引関わるアカウントの両方の生データに存在します。たとえば、シャーレアン銀行普通預金の取引記録を先にインポートしていた場合、最後の２件の記録は、シャーレアン銀行クレジットカードの取引記録をインポートする前に、すでにデータベースファイル内に存在することになります。そのため、シャーレアン銀行クレジットカードの取引記録をインポートする際に、これらすでにインポートされた取引記録を識別し、データベースファイル内で重複が発生しないようにする必要があります。
ただし、この手順は必須ではありません。インポートする必要のある取引記録がデータベースファイル内に存在しないことが確実であれば、この手順を省略しても問題ありません。
最終結果ワークシートの後に、新しいワークシートを作成し、statementsと命名します。
次に、exportコマンドを使用してデータベースファイルのstatementsビューの内容をエクスポートし、Excelで開きます。src_name列をフィルタリングして、シャーレアン銀行普通預金アカウントに関連する記録のみを表示します。その後、現在インポートしようとしている取引記録と重複する期間を確認し、この期間内の取引記録をstatementsワークシートにコピー＆ペーストします。
次に、中間結果ワークシートとstatementsワークシートの取引記録を1 対 1 で照合する必要があります。正しい記帳データは、以下のような照合結果を満たしている必要があります。
- ２件の記録が一致する場合、それらの取引日と取引金額は同じです。(注*)
statementsワークシートの各記録に対して、中間結果ワークシートにはそれぞれ対応する唯一の記録があります。- あるアカウントにおいて、この期間中の取引記録がすでにインポートされている場合、
中間結果ワークシートでそのアカウントの各取引に対応する記録が存在し、statementsワークシートにもそれに対応する唯一の記録が含まれています。
注*: 現実の取引では、振込や送金が当日中に入金されなかったり、手数料などの影響で送金額と入金額が一致しない場合があります。このような場合、振込取引を手動で処理する必要があります。たとえば、1件の振込を手数料の支払いと実際の入金に分割して2つの取引として記録する方法があります。または、新たに「振込中」というアカウントを作成し、送金された金額をまず「振込中」アカウントに移動させ、その後、振込先のアカウントへ移動させる方法も考えられます。
例のstatementsワークシートを使用して、この一致手順を説明します。statementsワークシートの内容は以下の通りです。
| posting_index | trade_date | account_index | amount | target | comment | src_name | asset_index | is_external | target_name | balance |
|---|---|---|---|---|---|---|---|---|---|---|
| 102 | 2023/8/14 | 1 | 150 | 3 | 支払い | シャーレアン銀行普通預金 | 1 | 0 | シャーレアン銀行クレジットカード | 18450 |
| 103 | 2023/8/14 | 1 | 150 | 3 | 支払い | シャーレアン銀行普通預金 | 1 | 0 | シャーレアン銀行クレジットカード | 18300 |
この例では、statementsに記録されている2件のデータ(2 行目と 3 行目) は、それぞれ中間結果の最後の2件と一致する必要があります。ただし、問題は中間結果にある最後の3件の記録の取引日と取引金額が同じである点です。そのため、通常のVLOOKUP関数を使用して一致させると、結果が不正確になる可能性があります。この問題を解決するために、以下のような特殊な関数を使用します。
まず、中間結果の現在最後の列であるother_change(列 F)の右側に新しい列、key_info (列 G) を追加し、2 行目 (つまり、データの最初の行) に関数=CONCAT(A2," ",C2)を入力します。次に、statementsに現在最後の列であるbalance (列 K) の右側にも、新しい列key_info (列 L) を追加し、2行目 (つまり、データの最初の行) に関数=CONCAT(B2," ",D2)を入力します。これらの二つの関数の目的は、後続の処理を容易にすることです。連結された日付が整数として表示される場合がありますが、Excel が日付を内部的に整数で表現しているためであり、後続の処理を与えません。
そして、中間結果のkey_info列 (列 G)の右側に、新しい列force_align(列 H) を追加します。この列には関数が設定されておらず、初期状態では空欄です。ただし、必要に応じて、この列を使用してstatements内の記録に手動で一致させることができます。
次に、statementsのkey_info列 (列 L)の右側に新しい列 (列 M) を追加し、列 Mの最初の行(ヘッダー行)に数字 1を入力します。2行目に、次の関数を入力します: =IF(COUNTIF(中間結果!H:H,ROW())>0,MATCH(ROW(),中間結果!H:H,0),MATCH(L2,OFFSET(中間結果!G$1,M1,0,9999),0)+M1)。
この関数は、まず中間結果のforce_align列 (列 H)に手動で指定された一致する記録があるかどうかを確認します。一致する記録がある場合、その指定された記録を直接使用します。一致する記録がない場合、中間結果において、前回すでに一致した記録から下へ向かって、statementsの該当行に一致する記録を検索します。この処理により、中間結果の複数の記録が同じstatementsの記録に一致することを防ぐことが保証されます。
これらの関数をExcelのオートフィル機能を使用して、すべての行に入力します。すると、statementsのM列で、2 行目と3 行目の記録にそれぞれ5と6を対応します。これらは、中間結果の第5条および第6条の記録です。
しかし、この対応関係は正しくありません。なぜなら、中間結果の5番目の記録はマッチングに含まれるべきではないからです（この取引はシャーレアン銀行の普通預金口座からのものではないためです）。ただし、この記録の取引日と取引額が、対応する必要のある記録と偶然一致していたため、先にマッチングされてしまいました。このような場合には手動での修正が必要です。中間結果ワークシートのセル H6 に数値2を入力することで、この記録がstatementsの2行目の記録に対応していることを指定します。
セルH6の内容を変更すると、statementsの列 M の内容が更新されます。現在、2 件の記録がそれぞれ6と7に対応しており、マッチングは正確です。
最後に、最終結果のワークシートにおいて、現在の最後の列dst_change(列 G)の右側に新しい列skip(列 H) を追加し、2 行目に関数=COUNTIF(statements!M:M,ROW())を入力します。この関数は、その行の記録がstatements内に対応するかどうかを示します。対応する場合は結果が1、しない場合は結果が0になります。この関数をすべての行に適用した後、フィルターを使用して結果が0の記録を抽出します。これらの記録は データベースファイル内に存在せず、インポートが必要な取引記録です。
上記の手順に従って、関連するワークシートに追加された列と各列の 2 行目に記載された関数をまとめると、次のようになります。
| ワークシート | 列ヘッダー | 列番号 | 関数 |
|---|---|---|---|
| 中間結果 | key_info | G | =CONCAT(A2," ",C2) |
| 中間結果 | force_align | H | 関数なし、手動で一致を指定するために使用 |
| statements | key_info | L | =CONCAT(B2," ",D2) |
| statements | 1 | M | =IF(COUNTIF(中間結果!H:H,ROW())>0,MATCH(ROW(),中間結果!H:H,0),MATCH(L2,OFFSET(中間結果!G$1,M1,0,9999),0)+M1) |
| 最终结果 | skip | H | =COUNTIF(statements!M:M,ROW()) |
csvファイルとして保存し、データをインポートする
最終結果ワークシートのデータは、すでにTataruBookの取引記録のインポート要件を満たしています。このデータをクリップボードにコピーし、pasteコマンドを使って挿入すれば、すべての作業は完了です。
ただし、注意が必要です。一部の取引には、TataruBook上でまだ作成されていないアカウントが関与している可能性があります。たとえば、これまで取引されたことのない株式が取引に関わる場合、accountsテーブルやasset_typesテーブルにその株式の情報が含まれていない可能性があり、これが原因でインポートが失敗する場合があります。ただし、TataruBookでは、記録のインポートに失敗した場合、自動的にロールバックが実行されるため、既存のデータに影響を与えることはありません。そのため、postings.csvファイル全体を正常にインポートできるまで、複数回インポートを試みることができます。その際、失敗時のエラーメッセージに基づき、accountsテーブルやasset_typesテーブルに必要な情報を追加することで対応可能です。さらに、変換テンプレートの中間結果ワークシートにいくつかの新しい列を追加し、accountsテーブルやasset_typesテーブルに必要なフィールドを抽出して、インポート失敗時に不足している記録を補うことができます。