今日は1日中NHK WORLD-JAPAN presents SONGS OF TOKYO Festival 2020を視聴していました。まさか全通することになるとは。昨日までは朝・昼の2回見れたらいいかと思っていたんですが。
いや、水樹奈々さん・バンドリ・アイマスは素晴らしいですね。
この日記書いたらいい時間になるので今日は2時の4回目の配信を見て寝ます。
さて、Excelで家計簿を作るという見出しですが全然家計簿にはなっていないので、収支の推移だけでもやってみるか！ということでせっせかグラフを作っていました。ピボットまでは前回以前でやっていたのであれをグラフ化するだけです。特に手順も何も書かず結果だけ貼ります。
最近のExcel、というかOfficeは簡単にオシャンティーなデザインを自動でやってくれるのでいいですね！
ということで家計簿っぽいものとして収支の推移を用意しました。
前月赤字だしたから今月は黒字がんばるぞ～～～～！見たいな使いかをしていきたいんですが、明細のデータの反映の都合で月初に前月のデータが確定しないのでそういう使い方できないんですよね。ここ数ヶ月の様子が見れればいいかな程度です。
日付を絞り込んでいないので今は全部だしています。
年間のデータが2年分以上になったら月単位のみだけでいいかもしれないですね。
このグラフ作る時に困ったのが、ピボットテーブル側で自動的に日付をグループ化されていたことです。
多分、今のExcelって書式が[日付]の列があるデータをピボットテーブルにしたら自動でこのグループ化をしてくれるんだと思います。
で、グループ化というのは日付を年とか四半期って単位で表現する仕組みって印象を覚えました。
これが自動で 月・四半期・年 の3つでグループ化されているんですが、このままだと集約されているのでグラフ上で2019年～2020年を左から右に表現できないです（年だけならできるけどそれは年単位の収支になるので目的が変わる）
なので、Excelのピボットテーブルからグラフ作る時は日付項目は注意しないといけないかもしれません。
で、ここからが本題で、
そういえばまだSQL serverにデータを追加した後、Excel側で追加した分だけを取得できるか。
ってのを試していなかったと思い今日はそれをやってみたいと思います。
MoneyFowardアプリにある10月のcsvはまだ入れずに、ダミーデータで試します。
ダミーデータは19/07のcsvの日付をそのまま20/10に変更したものを用意しました。
ではまずINSERT
正常にISNERTできました。
TO_CHARがなくてCONVERTとかCAST関数で型変換させるのですね。
関数の中で型を指定するってのが斬新でした。Oracleはこの辺変換したい型ごとに関数が別れているので、ここは大きく違いますね。
因みに先日型変換も行わずにSUBSTRINGできてたのは、あれはVARCHAR型のカラムでやっていたからだということも知れました。
ではCOMMITもしたので次はExcel側
クエリタブの更新がそれっぽいので押してみます。
入りました。うわすごい！！！！！！！
んで、INSERTの次はDELETEとUPDATEも試して見たいと思います。
以下の変化をテーブルに加えます。
・10/31のレコードを全削除
・10/30の金額を一律1,000に変更
これはスクショのわかりやすさでこの2点に
SELECT文を流用しているとエラーを吐いたのでちょこっと修正しました。
具体的には列の別名を消しています。
DELETE UPDATEではASが必要なのかと思ったらそういうわけでもなく
テーブルの別名自体が許されない？のかな。
これはちょっと気になるので気になるリストに入れておきます。
それは置いといてExcelで再度更新をクリック
今日付の降順でソートしていますが31日消えていますね。
それと30日のレコードの金額は全て1000が入っていることが確認できました。
連携は特別問題無さそうですね。素晴らしい。
ただ、作業時に気になったのですがSQL server側のインポート
これ今INSERTしかしてないからいいんですが過去データとかのUPDATEをcsv毎やりたい時、つまりは洗い替えをしたい時がもしかしたらあるかもしれません。
その時はIDのカラムをプライマリーキーにしているので、
テーブルにINSERT時、IDが同一かつ他項目が完全一致ならINSERTしない。
他項目に不一致があればUPDATEという形でcsvをインポートしたいですね。
更に、月単位でしかcsvをDLできないことを踏まえると、
インポートのcsvに存在しないIDのレコードはテーブルから削除もやったほうがよいですね。
書いてて理由がちょっと解釈違いしてきたんですがMoneyFowar側に存在しなくなっているレコードがテーブルに残っているなら削除したい。という考えです。
これはそういうクエリというか関数みたいなの組んで見たいと思います。
もはやExcelで家計簿を作っていないのでタイトルはその時は変えよう。
でもその仕組みも含めて1つの家計簿・・・
Excel内でcsvの取り込みも全てやればタイトル変えずにできるな。。。
VBAでやり直すか悩ましいですね。
そもそもDBを使うことのメリットは今のところないんですよね。
ただ使いたいだけで使ってみたっていう感じ。
うーーーーんSQL serverでも作って見たいしタイトル通りにするならSQL serverは破棄して全部Excel内部で済ませたい・・・
悩ましい。
今日はここまで
あでゅー
Comments