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

  • URLをコピーしました!

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

IMG_20190606_074903

※自宅にて Pixel 3

 

AmazonPayのメリット

 

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

 

Amazonアカウントで決済可能

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

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

image

 

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

image

 

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

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

動画、音声教材の他、個別コンサルティング、セミナーなど提供するサービスは、すべて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は、専用サイト(販売事業者向けサイト)があります。
ログインするとこんな感じです。

 

image

 

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のサイトからは、赤字の部分のデータがとれます。

image

 

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

 

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

image

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

image

 

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

 

image

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

image

 

[ダウンロード]という表示になればOKです。
image

 

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

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

image

 

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

image

 

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

 

image

 

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

 

image

 

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

image

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

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

 

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

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

 

 

AmazonPayの注文→取引管理

 

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

image

 

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

(ダウンロードはできません)

 

image

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

image

 

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

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

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

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

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

 

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

image

 

手数料のデータ

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

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

image

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

image

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

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でインポート(仕訳日記帳→他社会計ソフトデータのインポート)すれば完成です。

image

 

マクロの部分を入れずに、データを貼り付けるだけでも十分かと思います。
AmazonPayの経理で使ってみていただければ。

サンプル
EX-ITサンプル AmazonPay経理.xlsm

そのうち、AmazonPayで使いやすいデータが出てくるか、クラウド会計ソフトが対応するかもですが。
さらに、私はデータの作成にRPA(UiPath)を使っています。

 

 



■編集後記

『キャプテンマーベル』のデジタル先行配信がはじまりましたが、レンタルはなく、購入(2000円or2500円)のみ。
ここで2000円出すなら、もうちょっとまって4KUHDかなぁと。
商売上手な……。

 

 

■昨日の「1日1新」

とあること

 

■昨日の娘日記

最近は、保育園へ行くときの道具が減りました。
以前は、帽子、上着、ポシェット、バッグのセットが必須でしたが、最近はそれらがすべてありません。
帽子はかぶってほしいところですが。

  • URLをコピーしました!