LINE Payの履歴をエクセルのデータにしてみよう

みなさん、各種ペイの支払い履歴はどのように管理していますか？

今回はLINE Payの支払い履歴をエクセルに取り込んでみたいと思います

大問題！各種ペイにはエクスポートの手段が存在しない！？

　各種ペイの支払い履歴にはCSV出力などのエクスポートの手段は存在しません。アプリの使用履歴を１行ずつチェックするしかないのが現状です。

　ペイペイはボーナス取得と決済履歴が混在しており、ｄ払いなどは半年分のログしかなく年間の支出記録を作ろうと思っても今更探すことはできません。各種ペイの確認方法やその仕様も様々でユーザーとしては使いづらいことこの上ないところです。

　一応僕は改善要望を送ったこともあるのですが、改善される様子はありません。

　LINE Payも基本的には同じなのですが、LINE Payの場合はLINEの機能を利用したライフハック手段があります。

　LINEウォレットをユーザー登録しておくと、履歴を教えてくれるサービスがありますよね（ご存じですか？）。LINEフレンドとのトークルームということは、ログをトーク履歴としてテキスト保存可能ということです。

　このトーク履歴を解析するVBAを作れば、簡単にデータ化できるのではないかというのが今回の目的です。

LINEからトーク履歴の保存

　LINEウォレットの画面から、トーク履歴を保存を選びます。

保存したファイルはiCloudドライブへの保存を選択しました。パソコンから簡単に開けるしね。

　このテキストファイルを開くと、ポイントやお金の入出に関して一日ごとにイベントが格納され

2018/11/05(月)
15:07 LINEウォレット LINEポイント 【クイズ】LINE MUSICで3ポイント獲得しました

というような表記になっていることがわかります。これは簡単にいうと

日付

時刻　ウォレット名　サービス名　額面　単位　処理

のような構造になっていることがわかります。

また、同じ日に複数のイベントがある場合には

2019/11/03(日)
19:47 LINEウォレット LINEポイント 今だけ！8000ポイントバックで1ポイント獲得しました
19:56 LINEウォレット LINEポイント LINE証券Wキャンペーン中で1ポイント獲得しました

というように、イベントごとに改行して処理されるようです。

日付

時刻　ウォレット名　サービス名　額面　単位　処理

時刻　ウォレット名　サービス名　額面　単位　処理

というような構造になっていることが予測できます。

代表のパターンに分類する

　処理の分岐をできるだけ簡素にするため、LINE Payの入出金、ポイントの獲得・利用、LINE　PAYでの店舗支払い、LINE内の各種サービスでの利用あたりでそれぞれどの程度のユニーク値を持っているかを検討しましょう。

　例えば、「日付」、「時刻」の項は「YYYY/MM/DD(AAA)」「hh:mm」という書式ですし、「ウォレット名」は「LINEウォレット」で固定になっており、「処理」は「獲得しました」「利用しました。」「取得しました。」「（無言：収入）」「（無言：支出）」の5つがあると考えられます。

　また、「額面」「単位」は数値と単位（円、ポイントあたり）におおよそ固定されると考えられます。

　一方で「サービス名」は様々なパターンが存在しており、類型化するのは困難といった感じでしょうか。

　おおよその構造はわかりましたが、分解には注意が必要です。特にサービス名はあとから解析することを想定せず作られており、自由記載できるものになっています。

　例えばスペース区切りではサービス名の部分でいくつものバグが生ずることが容易に想像できます。

というわけで、代表的な表記を集めてみました。

15:37 LINEウォレット LINE Pay : ツルハグループで2ポイント獲得しました
08:42 LINEウォレット "LINE Pay お支払い 428 円
08:41 LINEウォレット "LINE Pay チャージ 1,000 円
16:35 LINEウォレット LINEギフト：商品購入で1032ポイント利用しました
19:33 LINEウォレット "LINE Pay 獲得 333 P

このあたりでしょうか。

分類器を作ろう

まずはlogシートの各行を入力する動的配列linelog()と、データを格納しておく動的配列linedata() を宣言します。

Dim linelog() As Variant
Dim linedata() As Variant

ループする回数を決めるため、行数をカウントしておきましょう。linedata()は支出名　日付　件名　金額　入出の区別がわかればよさそうなので２次元の配列を作ります。

TotalRow = Cells(Rows.Count, 1).End(xlUp).Row
ReDim linelog(TotalRow)

ReDim linedata(TotalRow, 4)

linelogに各行の文字列データを入れていきます。

For n = 1 To TotalRow 'セルA1～データの終わりまで1行ずつループ
linelog(n) = Cells(n, 1).Value
Next n

これで、全データがlinelogに取り込まれました。

ん？これ別にTXTからLine Inputで一行ずつ抜いても同じだったね・・・。

まあいいや。

出力データ形式を定義する

記録先のシート名は”LINE Pay”とします。

記録するデータは以下のように定義します。

　列Ａ　支出元（今回の場合はＬＩＮＥ Ｐａｙ固定）：String

　列Ｂ　日付：String リテラル　日付形式 "YYYY/MM/DD"

　列Ｃ　件名：String

　列Ｄ　金額：long　カンマ区切り無し

　列Ｅ　入出の別：「出金」又は「入金」

先頭行は項目名にしたいので２行目から記録していきます。

ループカウンタと別に行数を管理しなければならないので新しい変数を定義しましょう。

Dim RowCount As Variant

全然関係ないですが変数の命名規則は昔はハンガリアン記法を採用していましたが、最近はどストレートな名前を使っています。

そんな面倒なプログラム書かないしね・・・

あとはlinedataの(a,b)がそれぞれCells(a,b)に対応する配列とします。

ループ処理

ループ処理は、１行ずつのループと、各行をとりあえず半角スペースで区切った場合のループの２種類が必要です。

また、各行のlinelogをSplitしたデータを記録する配列も必要なので再び定義します。ループ回数は配列の要素数によるので、それを判定して処理させましょう。

RowCount = 2
For n = 1 To TotalRow 'セルA1～データの終わりまで1行ずつループ
rowdata = Split(linelog(n), " ")
For i = LBound(rowdata) To UBound(rowdata) '分解した要素の終わりまで1ブロックずつループ

（なんかの処理（適当））

next i

next n

金額（数値）獲得の処理

行と単語に分割したデータを作ったところで、それぞれ評価していきます。

まずはサンプルデータである

15:37 LINEウォレット LINE Pay : ツルハグループで2ポイント獲得しました

を整理していきましょう。

この文章はいくつかのブロックに分かれています。最初に示したように典型的なフォーマットだと思われます。

[時刻][サービス名][件名][獲得ポイント数][獲得単位][処理]

のような並びで、半角スペースで区切られていると仮定します。Pythonなら形態素解析するところですが、ここでは簡単に[処理]＝「ポイント獲得」というフレーズをキーにして解析することにしましょう。

まず、区切った文節からポイント獲得という文字を検索します。

If InStr(rowdata(i), "ポイント獲得") > 0 Then

ポイント獲得という文字の前が数字だと思うので１文字ずつ加算していきます。

ループ回数は、行全体の文字数から、「ポイント獲得」までの文字数（位置）

そこから１文字ずつ前に戻り、数値だったら後ろに結合していきます。数字以外の文字を拾ったときにはループの処理を抜けることにします。

PointNum = Empty
For m = 1 To Len(rowdata(i)) - InStr(rowdata(i), "ポイント獲得")
　NumSet = Mid(rowdata(i), InStr(rowdata(i), "ポイント獲得") - m, 1)
　　If IsNumeric(NumSet) = True Then
　　　PointNum = Mid(rowdata(i), InStr(rowdata(i), "ポイント獲得") - m, 1) & PointNum
　　Else: GoTo continue_Point:
　　End If
Next m
continue_Point:

さて、ここで、数値判定を考えた時に、カンマによる３桁区切りで表記されているケースがあることに気づきましたので、数値判定を以下のように修正します。

If IsNumeric(NumSet) = True Then

　　↓

If IsNumeric(NumSet) Or InStr(NumSet, ",") <> 0 = True Then

これでカンマ表記にも対応しましたね。

日付取得の処理

日付は一つ前の列にありますが、連続している場合は日付ではないので、日付チェックをしながら過去に遡ります。とりあえずPastと定義しましょう。日付データを発見した段階で、日付データを取得します。GetDateだと汎用性が高く今後別に使いそうな雰囲気なので、RecDateにしましょう。

この辺の怪しい英語を使ったほうが、それっぽくていい感じですよね。

Past = 1
Do While IsDate(Left(Cells(n - Past, 1).Value, 10)) = False
　Past = Past + 1
Loop
　RecDate = Left(Cells(n - Past, 1).Value, 10) '日付GET

件名取得の処理

件名はSubjectとしたいですが、一応接頭辞としてLineを付加します。LineSubjectというやや長めの変数名にします。

ここでは、

　LINE Pay : ツルハグループ

という文字列を件名として取得したいので、不要なLINEウォレットまでを検索し、更に文字数分を勧めた位置からMID関数で切取ります。

　「件名＋数値」の文字数は、LINEウォレットの位置 + 16　から　ポイント獲得の位置　- 1なので、（ポイント獲得の位置　- 1）- （LINEウォレットの位置 + 16）となり、ポイント獲得の位置　-  LINEウォレットの位置 - 17に、なります。そこから更に数値の文字数分を遡ったところが件名の文字数となります。

LineSubject = Mid(linelog(n), InStr(linelog(n), "LINEウォレット") + 16, _

　　　　　　InStr(linelog(n), "ポイント獲得") - InStr(linelog(n), "LINEウォレット") _

　　　　　　　- 17 - Len(Format(PointNum, "###0")))

セルへの記録

さて、ここまでのデータが整ったところで記録していきます。このとき、シート"log"とシート"LINEPAY"間を何度か行き来するので、途中の処理に間違いのないようにSheets("□□□").Activateの処理を挟んでおきましょう。

Sheets("LINEPAY").Activate
Cells(RowCount, 1).Value = "LINEPAY"
Cells(RowCount, 2).Value = RecDate
Cells(RowCount, 3).Value = Replace(Replace(LineSubject, " ", " "), " ", " ")
Cells(RowCount, 4).Value = PointNum
Cells(RowCount, 5).Value = "入金"
RowCount = RowCount + 1

End If

どうでしょう、ここまでを処理すれば成形されたデータが手に入るのではないでしょうか。忘れずにループを閉じて実行してみましょう。

Next i

Next n

出力結果

LINEPAY 2018/11/5 LINE Pay :  ツルハグループ 2 入金

バッチリですね！

この調子で他の処理も自動化していけばLINE PAYについては手入力しなくてもよさそうです。

あれ？

そういえば、わざわざSplitして作ったrowdata(n)って何のために作ったんだっけ・・・？もしかして最初から一行ずつ処理で良かったんじゃ・・・

まあいいや

僕はプログラムに美しさなんて求めていない！

狙った通りの結果ができればそれでいいじゃないかと思っています。

妥協することで得られるものもある。

僕は強く思いました。まる。

 twitter.com