AmazonPayの経理をExcelマクロで効率化する方法

2019年6月6日

URLをコピーしました！

クレジットカード決済サービスとして、AmazonPayを使っている場合、経理が少々手間です。
それを効率化するExcelマクロをご紹介します。

IMG_20190606_074903

※自宅にて　Pixel 3

1 AmazonPayのメリット
2 AmazonPayのデメリット
3 ExcelマクロでのAmazonPayの経理

AmazonPayのメリット

AmazonPayとは、Amazonが提供する決済サービスです。

Amazonアカウントで決済可能

自分のサービスを提供するときにAmazonのアカウントで決済できます。

支払い時にボタンをクリックすると、

Amazonのアカウントでログインして、Amazonに登録してあるカードで決済できるしくみです。

私のサイトでも導入しています。

タイムコンサルティングオンラインショップ

動画、音声教材の他、個別コンサルティング、セミナーなど提供するサービスは、すべてAmazonPayとしました。
（これまで一部使っていたPaymoがサービス停止となったため）

すべてのクレジットカードを使える

AmazonPayなら、Visa、MasterCard、American Express、JCB、Diners Clubとすべてのクレジットカードを使えます。
JCBが使えない、または手数料が高くなるサービスが多い中うれしいポイントです。
（まあ、その分手数料が高いのですが）

振込が自動で無料

カード決済サービスで地味にめんどくさいのが、出金処理。
カード決済会社に決済金額がたまっていき、それを振り込んでもらう処理をしなければいけません。
そのときに手数料がかかると、「もうちょっとたまってからでいいか」と考えてしまいがちです。
AmaonPayは、約2週間ごとに自動で、かつ無料で振り込んでくれます。

（まあ、その分手数料が高いのですが）

なお、初期費用もかかりませんし、固定費もなしです。

AmazonPayのデメリット

一方、デメリットもあります。

手数料が高い

1つは、手数料。
デジタルコンテンツ4.5%　それ以外4%です。
ただ、その区分けは、AmazonPay側でやっており、ほぼ4.5%と考えておきましょう。
この手数料は利便性のために必要なものなので、ご決済していただく際にはお気兼ねなく。

登録・設定の手間

もう1つは、登録・設定。
登録には申請が必要で、1週間から2週間かかるといわれています。
私の場合は、7日間でした。

また、AmazonPayは決済方法だけの提供なので、サイトに組み込まなければいけません。
（Paypal、squareなどは決済するページも利用可能）
私は、WordPressのサイトに組み込み、そのためにプラグインWooCommerceを使っています。
WordPress、プラグインは無料です。

WordPressでネットショップ。WooCommerce＋AmazonPayの設定方法。 | EX-IT

ネットショップサービスを使うと、AmazonPayを組み込めますが、そこにまた手数料はかかってしまいます。

経理の手間

さらにもう1つのデメリットは、経理。
記事執筆現在、AmazonPayは、クラウド会計ソフトに連動していません。
専用サイトからデータを取ることはできますが、加工は必要です。

その加工をExcelマクロでやっています。

ExcelマクロでのAmazonPayの経理

AmazonPayは、専用サイト（販売事業者向けサイト）があります。
ログインするとこんな感じです。

AmazonPayの経理のタイミング

経理には、次の2つのタイミングでデータが必要となります。

〇AmazonPayで決済
AmazonPayで決済していただいたタイミングです。
27,000円の売上で、AmazonPayという科目を使うなら、

AmazonPay　27,000　/　売上　27,000

となります。

ただし、売上からは手数料が差し引かれるので、

AmazonPay　25,785　/　売上　27,000

支払手数料　1,215

または、

AmazonPay　27,000　/　売上　27,000

支払手数料　1,215　/ AmazonPay　1,215

という処理です。

また、コンテンツ（動画や音声）の場合は、ご購入いただいた日が売上日となりますが、個別コンサルティングがセミナーの場合は、事前に決済いただくので、実際の売上日は、決済日よりも後です。

たとえば、6/27のセミナーの決済が6/6、売上の日＝セミナー開催日が6/27なら、決済していただいた時点で、
6/6　AmazonPay　27,000　/　前受金　27,000

6/6 支払手数料　1,215　/ AmazonPay　1,215

とし、売上の日に

6/27　前受金　27,000　/　売上　27,000

としています。

実際には、お申込みがあった日（6/5）の翌朝経理をするときに

6/27　前受金　27,000　/　売上　27,000

と入れ、月1回のAmazonPayや預金の経理をするときに、

6/6　AmazonPay　27,000　/　前受金　27,000

6/6 支払手数料　1,215　/ AmazonPay　1,215

とする流れです。

〇振り込まれた場合

決済金額が振り込まれたときには、

普通預金　25,785/AmazonPay 25,785

という処理になります。

ーーーーーーー
※私は、普通預金の処理とダブるので、

諸口　25,785/AmazonPay　25,785

と処理しています。

普通預金側では、たとえば、ゆうちょ　/諸口　25,785とすれば、「資金移動」は0になって消えますので。

ーーーーーーー

まとめると、こういった取引の流れになり、AmazonPayのサイトからは、赤字の部分のデータがとれます。

AmazonPayのペイメント→期間別レポート

AmazonPayのサイトで、［レポート］→［ペイメント］をクリックし、

［期間別レポート］で、［レポートの作成］をクリックします。

期間を選択して、［レポートを作成］をクリックしましょう。

進行中と表示されますが、［最新の情報に更新］をおせば、そのうちできあがります。

［ダウンロード］という表示になればOKです。

このときにレポートができない場合もあります。
たとえば、6月1日にレポートをつくろうとして、5/24から6/6の期間で計算しているとき、その期間に売上があった場合、その売上はレポートに表示されません。
その場合は、期間の選択で日付を5/23までと区切りましょう。
毎月は、その日までで合わせておいて、決算のときだけ考えるようにしています。

ダウンロードできるのは、こういったデータです。
A列（日付）、K列（金額）、L列（手数料）を使います。

会計ソフトに取り込むため、それぞれ同じ行に会計ソフトの取込形式でデータをつくりましょう。
ここではクラウド会計に取り込むようにしています。

取引日は、A列から年、月、日を取り出し、DATE関数でくっつけました。

A列はこうなっています。
このままだと日付として使えませんので、左から4文字を年、左から6文字目から2文字を月、左から9文字目から2文字を日として取り出すのです。

借方金額は、K列が0より大きい＝プラスか、そうでないかでIFを使って処理を分けています。

金額がプラスだと、決済ということなので、AmazonPay/前受金となり、金額がマイナスだと、出金なので、普通預金/AmazonPayという処理です。

貸方科目も同様に、IFで処理しています。

金額は、K列がプラスであってもマイナスであってもプラスの数字を表示するように、ABSを使いました。
ABSは絶対値を示すもので、ABS（100）もABC（-100）も100と表示します。

しかし、このデータからは、取引の内容がわかりません。
そこで、取引の内容は、別のデータから連動させます。

AmazonPayの注文→取引管理

AmazonPayのサイトの［注文］→［取引管理］をクリックします。

次のような、購入者氏名が入ったデータが表示されるので、当該月のデータをコピーしてExcelに貼り付けましょう。

（ダウンロードはできません）

貼り付けたデータをシート「customer」とします。

「購入者データ」は、先ほどの「売上データ」と「AmazonリファレンスID」が一致するので、その一致する部分を使って購入者を表示しましょう。

「売上データ」のF列、「購入者データ」のG列に「AmazonリファレンスID」があるので、INDEXとMATCH関数を使うと、購入者が表示されます。

VLOOKUP関数を使いたいところですが、購入者データの形式がVLOOKUP関数に適していない（一番左に、リファレンスIDがない）ので、INDEXを使いました。

エラーが出ているのは購入者データがないもの、つまり出金データです。

INDEXは、（〇、△、□）で、〇の範囲から△行、〇列目にあるデータを表示します。
その行を指定するのに、MATCHでリファレンスIDを探すというしくみです。

出金部分は、実際には、エラー処理をIFERRORでやって、空欄にしておきます。

手数料のデータ

さらにカード手数料のデータが必要です。

QからU列のデータを使いつつ、同じ行にカード手数料のデータをつくります。

取引の科目は固定で入れ、金額は、L列がマイナスなので、ABSを使ってプラスにしておきましょう。

全体像はこんな感じです。

AからO列がAmazonPayのレポート、Q列からU列が、売上データ（インポート用）、W列からAA列がカード手数料データ（インポート用）になっています。

インポートデータはそれぞれ2行目を売上データの数だけコピーしなければいけません。

Excelマクロで、データ結合

このままだと、データをインポートできないので、このように1つにしなければいけません。
2行目の数式をデータの数だけコピーして、

データを1つにするところをExcelマクロでつくっています。

Sub amazonpay_import()

    
'インポートするシート「import」をいったん削除(見出し除く）
    Worksheets("import").Rows(2 & ":" & Rows.Count).Delete
    
    
'AmazonPayのデータをカウント
    Dim Max_row
    Max_row = Worksheets("sale").Range("a" & Worksheets("sale").Rows.Count).End(xlUp).Row
    
'インポート用データ作成（2行目をコピー）
    Worksheets("sale").Range("q2", "aa2").Copy Worksheets("sale").Range("q3", "aa" & Max_row)
    
    
'貼り付け先のデータをカウント
    Dim im_Max_row
    im_Max_row = Worksheets("import").Range("a" & Worksheets("import").Rows.Count).End(xlUp).Row
    
    
'売上データをコピーして貼り付け
    Worksheets("sale").Range("q2", "u" & Max_row).Copy
    
    Worksheets("import").Range("a" & im_Max_row + 1).PasteSpecial Paste:=xlPasteValues
    
    
'カード手数料データをコピーして貼り付け
    im_Max_row = Worksheets("import").Range("a" & Worksheets("import").Rows.Count).End(xlUp).Row
    
    Worksheets("sale").Range("w2", "aa" & Max_row).Copy
    
    Worksheets("import").Range("a" & im_Max_row + 1).PasteSpecial Paste:=xlPasteValues
    


    
'シート「import」をCSVファイルで保存
    Application.DisplayAlerts = False

        Worksheets("import").Copy
          
        ActiveWorkbook.SaveAs Filename:="import.csv", FileFormat:=xlCSV, Local:=True
        
        Workbooks("import.csv").Close
    
    Application.DisplayAlerts = True

End Sub

あとは、freeeでインポート（仕訳日記帳→他社会計ソフトデータのインポート）すれば完成です。