d払いの履歴をエクセルのデータにしてみよう
今回はｄ払いの支払い履歴をエクセルに取り込んでみます。果たしてうまくできるでしょうか。
利用履歴は確認できるか？
前回はLINE Payの履歴を作成しました。他にマクロ処理できそうな電子マネーは無いか探してみたところ、ｄ払いがPCサイト上で履歴確認できることを発見しました。
こちらからサイトを開くと「電話料金合算払い」「ドコモ口座払い」「その他お支払い方法」の三種類の履歴が確認できます。
この中の、履歴を覗くと、このような感じで月ごとに明細が表示されています。
でも、半年分しか記録されていないようですね・・・正直なところ最低でも１年分の履歴は確認できるようにしてほしいところですが、無いものは仕方ありません。
データ取得方法を考えよう
このデータを取り込む方法を考えます。とりあえずGoogle ChromeのDevelopperToolでElementsソースを解析してみましょう。「F12」キーで起動します。
Pythonでスクレイピングしてデータ化することもできそうですが、僕はSPANが多いデータの処理は苦手です。また、この手のサイトはweb上の構成が変更されることもしばしばあります。そして、ドコモの認証をクリアするためにはいちいち確認コードを入力させなければいけません。
スクレイピング自体が禁止されていることも多いので、今回はデータをコピペしてエクセルに貼付けたデータを集計することにしました。
原始的ですみません。
でも僕は、仕事の９割はコピー&ペーストでこなしていますし、コピー＆ペーストは僕の最も得意とするスキルです。（威張んな）
信頼できる技術に重きを置くのは当然。
だから、許して。
作業フローを作ろう
というわけで、今回は手作業による部分と、マクロによる部分に分かれますので、手作業でどこまで作業するのか、どういう形でデータを渡すのかの作業フローを図にしてみます。その作業が直接できるのもExcelの強みです。
作業フローは簡単で大丈夫です。必要に応じて追記すればよいだけです。
作業フローが完成したところで、構築順序を考えます。ループが深い場合、ループの深いところから作るのが基本です。また、ループカウンタに階層を意味する接尾語を付すると管理しやすいです。
3つ程度の入れ子は深くともなんともないですので、適当なカウンタ名にしています。
とはいえ、支払い方法の区別をどういう名前にするべきか悩みます。CategoryやWalletは既に他の定義で使用しています。名前空間は異なりますが混在は避けたいところ。ということでgenreにしてみました。
能力系バトル漫画で、使える語が減っていって後半に行くほど複雑な名称になっていくのに似ていますね。
思い切ってラテン語にしますか、オサレ だし。
結論から言うと
名前はとても大事
ということ(水辺軍)
今日のblogの結論はこれです。
初歩的なものを作るときは、わかりやすく理解しやすいものが大事。
複雑なものを作るときは、ルールや規則が大事。
初心者にルールや規則を押し付けると、混乱の元になるのです。
全データ行数取得
全データ行数は、全シートのB/E/H列をチェックするだけです。シート名は半角数値＋「月」としましょう。
'*----データ格納
For n_month = 1 To 12
s_name = n_month & "月"
Sheets(s_name).Activate
TotalRow = TotalRow + Cells(Rows.Count, 2).End(xlUp).Row _
+ Cells(Rows.Count, 5).End(xlUp).Row _
+ Cells(Rows.Count, 8).End(xlUp).Row
Next n_month
ReDim d_data(TotalRow, 6)
d_count = 1
このループではデータ格納のための全行を取得しています。実際にはかなり過剰です。動的配列を使用したテクニックもありますが、ここは愚直にいきましょう。
ｄカウントは配列のデータ行番号です。ループに入る前に念のため１にしておきましょう。Excelはシートのやり取りをする際に、1スタートのほうが扱いやすいことが多いです。
配列の定義と初期値
まずは配列の定義を行いましょう。また、初期値は定義しておいたほうが人間が理解するのに便利です。
For n_month = 1 To 12
s_name = n_month & "月"
Sheets(s_name).Activate
For n_genre = 1 To 3
TotalRow = Cells(Rows.Count, 3 * n_genre - 1).End(xlUp).Row
ReDim mainlog(TotalRow)
ReDim sub_log(TotalRow)
（適当な処理）
Next n_genre
Next n_month
次にデータ取得もさせてみましょう。
For n = 1 To TotalRow
sub_log(n) = Cells(n, n_genre * 3 - 2).Value
mainlog(n) = Cells(n, n_genre * 3 - 1).Value
Next n
たった4行ですが、これで大丈夫です。3次元配列にする手もありますが、ローカルウインドウで確認する際には2次元配列の方がわかりやすいので2次元配列にしています。
これで全シートを舐めるようにデータ取得できているかはVBAエディタのローカルウインドウでチェックできます。
この画像は既にデータを正規化したものなので、きちんと成形されたデータになっていますが、本来はぐちゃぐちゃになっています。
ここまでできたところで、For n_month = 1 To 1、For n_genre = 3 To 3 あたりに変更してチェックを繰り返しましょう。
毎回全データでチェックする必要はありませんからね。
日付の取得
日付データは9文字、または10文字で記述されており、[ ]で囲まれています。時刻は要らないので、この文字列をYYYY/MM/DD形式に直しましょう。
For n = 1 To TotalRow
d_data(d_count, 1) = Empty
For wn = 1 To 10
If IsNumeric(Mid(mainlog(n), 1 + wn, 1)) = True Or Mid(mainlog(n), 1 + wn, 1) = "/" Then
d_data(d_count, 1) = d_data(d_count, 1) & Mid(mainlog(n), 1 + wn, 1)
Else: GoTo continue_getdate:
End If
Next wn
continue_getdate:
1文字ずつ数値または"/"をチェックして、それ以外が出てきたらループを抜けます。
ちなみに、for文のループを抜けるのには「Exit For」という命令があり、こちらで処理しても同じ結果が得られます。
「GoTo」は多くなりすぎると余計に混乱を生みますが、明示的に処理を飛ばしつつ、ソースを見た時にどの処理が完了したのかコメントの代わりになり、ブレークポイントを設定するのに便利なので、僕は「GoTo」を使用しています。
「Exit For」だと、どの処理が完了してループを抜けたのかわかりにくいからです。あとGoToがSEO的に有利だからですね。
その他データの取得
この調子で他のデータも正規化していきましょう。
最初の行が日付かどうか判定し、正しければ一行ずつ取得しに行きます。
ループ外のカウンタ「d_count」に加算するのも忘れずに。
'*----日付チェックできたらデータ取得に向かう
If IsDate(d_data(d_count, 1)) = True Then
d_data(d_count, 0) = "ｄ払い"
'*----サブジェクト取得
d_data(d_count, 2) = mainlog(n + 1) 'サブジェクト
d_data(d_count, 3) = mainlog(n + 2) 'メソッド
d_data(d_count, 4) = Empty '金額
If n_genre = 1 Then
d_data(d_count, 6) = "電話料金合算払" 'ジャンル
ElseIf n_genre = 2 Then d_data(d_count, 6) = "ドコモ口座払" 'ジャンル
ElseIf n_genre = 3 Then d_data(d_count, 6) = "カード払" 'ジャンル
End If
'*----金額取得
For m = 1 To Len(mainlog(n + 3))
NumSet = Mid(mainlog(n + 3), Len(mainlog(n + 3)) - m + 1, 1)
If IsNumeric(NumSet) Or InStr(NumSet, ",") <> 0 = True Then
d_data(d_count, 4) = NumSet & d_data(d_count, 4)
Else: GoTo continue_PayOut:
End If
Next m
continue_PayOut:
'*----支払状況取得
For m = 1 To 5
If IsEmpty(sub_log(n + m)) = False Then
d_data(d_count, 5) = sub_log(n + m)
GoTo continue_PayCheck:
End If
Next m
continue_PayCheck:
d_count = d_count + 1
基本的にはデータ取得できていたので、間違いがあったら修正しながらデータを取得しましょう。
データの記録
再びローカルウインドウでデータが正確に取得できているのを確認できたら、あとは記録シートに吐き出すだけです。
'*----記録
Sheets("dbarai").Activate
For n2 = 1 To d_count - 1
Cells(n2, 1).Value = d_data(n2, 0)
Cells(n2, 2).Value = d_data(n2, 1)
Cells(n2, 3).Value = d_data(n2, 6) & " " & d_data(n2, 2)
Cells(n2, 4).Value = d_data(n2, 4)
If d_data(n2, 5) = "キャンセル済" Then Cells(n2, 5).Value = "入金" Else Cells(n2, 5).Value = "出金"
Cells(n2, 6).Value = d_data(n2, 3) & " " & d_data(n2, 5)
Next n2
さて、ここまでできたところでフルチェックしてみましょう。
成功ですね！頑張ったかいがありました！
正直なところblog書くほうが時間かかっていますが。
その他のPayをどうするか
前回LINE Pay、今回ｄ払いとマクロで処理してみましたが、どうやらその他のPayはPC上でログを取得することができません。
良い方法がないか探していますので、僕より深い知識をお持ちの方は、良い方法を教えてくださいね。コメント欄でも、ツイッターでもいいので教えてくださると助かります。
僕のツイッターはこちら！
