pythonで株価をスクレイピング→bigqueryに取込んでLookerで可視化してみた


Lookerで株価を可視化してみました

データはpython使ってスクレイピングで取ってくる感じにしてます。
python使うならmatplotlib使えよ。的な話もあると思いますが、Lookerに可視化するのに必要な作業手順を一通り体験してみたかったので、やや興味本位（趣味）でやってますので、その辺はご理解よろしくお願いいたします。

利用ツール
・**google colab(python3)**
・**bigquery**
・**Looker**

## 株価データの取得(スクレイピング）
・まずは可視化対象のデータ取得ですが、個人的にFFとかロマサガが好きなので、スクエニの株価を取得。
・スクレイピングのコードはこちらの記事を参考にさせていただきました。
http://www.stockdog.work/entry/2016/08/28/180911


```python
import pandas
import datetime

def scraping_yahoo(code, start, end, term):
	base = "http://info.finance.yahoo.co.jp/history/?code={0}.T&{1}&{2}&tm={3}&p={4}"

	start = str(start)
	start = start.split("-")
	start = "sy={0}&sm={1}&sd={2}".format(start[0], start[1], start[2])
	end = str(end)
	end = end.split("-")
	end = "ey={0}&em={1}&ed={2}".format(end[0], end[1], end[2])
	page = 1

	result = []
	while True:
		url = base.format(code, start, end, term, page)
		df = pandas.read_html(url, header=0)
		if len(df[1]) == 0:
			break

		result.append(df[1])
		page += 1
	result = pandas.concat(result)
	result.columns = ['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close']

	return result

if __name__ == "__main__":
### 取得したい株価のコードを指定する（ここを変更）:9684はスクエニの株価コードです
	company = 9684

	EndDate = datetime.date.today()
	StartDate = EndDate - datetime.timedelta(days=180)  ## ここで何日分のデータ取得するかを指定

	data = scraping_yahoo(company, StartDate, EndDate, "d")
	print(data)
```
####実行結果

スクエニの直近180日分の株価を取得してきました。


![株価スクレイピング結果1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/ea7bd7a7-e66d-4180-64c9-1ac83436246d.png)


## DBに取込む為のレイアウトの変換と、7日移動平均の項目追加
・スクレイピングしたデータをそのままbigqueryに突っ込んでみたら、日付がうまくソートできない形式だったり、インデックスの並びが微妙だった為、いい感じに取込めるように変換してます。せっかくなので7日移動平均も追加。

```python
## スクレイピングしたデータを一時退避
df = data

## 日付項目をソートできるようにフォーマット変換
df['Date'] = pd.to_datetime(df['Date'], format='%Y年%m月%d日')

## adj CloseをDBに項目取り込みする為、スペース情報を_に変更
df = df.rename(columns={'Adj Close': 'Adj_Close'})

## 銘柄コードを追加
df['bond_cd'] = company

## 移動平均算出の為、Date順にソートする
df = df.sort_values(by=["Date"], ascending=True)

## 7日移動平均を算出して追加
df['avg_7day_Close'] = df["Close"].rolling(window=7).mean()

## indexをDateに変更
df.set_index("Date",inplace=True)

## 内容確認
df
```
####実行結果
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/b91af5e0-246c-d781-80a2-5bac656e2cc4.png" width=60% alt="株価スクレイピング結果2">


## データ定義を確認

```python
df.dtypes
```
####実行結果
・**Date** はインデックスにしたので、ここでは表示されませんがCSVにするとひょっこり出てくるので大丈夫。
![株価スクレイピング結果3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/62236f22-c689-84ac-342c-b70557fe6fa1.png)

## いちおう、matplotlibで株価データを時系列で可視化

```python
df['Close'].plot(legend=True, grid=True)
```
####実行結果
・直近で、結構株価上がってました。
ググったら2019年4-6月期の連結営業利益は前年同期比85.8％増と好調だったみたいで調子よいみたいです。
![株価スクレイピング結果4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/aa864507-708f-d7ca-7e45-083e4a680953.png)

## CSVにダウンロード(google colabからローカルへ）

```python
## CSV出力
from google.colab import files
filename = "bond.csv"
df.to_csv(filename) 
files.download(filename) 
```

## CSVをBigqueryへ
ここら辺の手順は**[こちら](https://qiita.com/ymto/items/d818899850801bb591f6)**を参考にし、**bond**というテーブルを作成しました。

## bigqueryのテーブルをLookerへ反映(LookMLを自動生成する)
・Lookerを開いて、開発用の**Development Mode**に切り替える
![株価スクレイピングlooker作成1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/904ea94a-6f1b-e62d-3e27-b67d415424f8.png)


・Projectの**Views**にbigqueryのテーブルを追加してLookMLを生成
![株価スクレイピングlooker作成2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/17672446-a4fc-b9bf-6f04-c08471807fde.png)
![株価スクレイピングlooker作成3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/874d4433-e073-30ea-b4fa-848a0170aa03.png)
![株価スクレイピングlooker作成4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/bcf22297-76fa-2a18-e039-d507e3c87171.png)


####自動生成されたLookML

```yaml
view: bond {
  sql_table_name: sample_analysis_looker.bond ;;

  dimension: adj_close {
    type: number
    sql: ${TABLE}.Adj_Close ;;
  }

  dimension: avg_7day_close {
    type: number
    sql: ${TABLE}.avg_7day_Close ;;
  }

  dimension: bond_cd {
    type: number
    sql: ${TABLE}.bond_cd ;;
  }

  dimension: close {
    type: number
    sql: ${TABLE}.Close ;;
  }

  dimension_group: date {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.[Date] ;;
  }

  dimension: high {
    type: number
    sql: ${TABLE}.High ;;
  }

  dimension: low {
    type: number
    sql: ${TABLE}.Low ;;
  }

  dimension: open {
    type: number
    sql: ${TABLE}.Open ;;
  }

  dimension: volume {
    type: number
    sql: ${TABLE}.Volume ;;
  }

  measure: count {
    type: count
    approximate_threshold: 100000
    drill_fields: []
  }
}
```

## LookMLの修正その１：描画用にmeasure項目を追加
・Lookerのお作法的なものですが、LookMLの自動生成では、数値項目でも**measure**ではなく**dimension**として認識されます。そのため、グラフなどに描画したい情報の場合、以下のように**measure項目として追加**する必要があります。詳細は**[こちら](https://qiita.com/ymto/items/1edad4ffdd058300e3b8)**

```yaml
### 追加コード
  measure: avg_7day_close_sum {
    type: sum
    sql: ${TABLE}.avg_7day_Close ;; ### 7日移動平均
  }
  
  measure: close_sum {
    type: sum
    sql: ${TABLE}.Close ;; ### 当日終値
  }
  
  measure: volume_sum {
    type: sum
    sql: ${TABLE}.Volume ;; ### 当日出来高
  }
```

## LookMLの修正その２：日付け項目の修正対応
・こちらもLookML使うときのお作法です。テーブルの日付項目名に一定の文字列があると、自動で[]がつけられますが、**bigqueryでは項目名に[]をつけると現時点ではエラーとなる為、コードの手直しが必要**。詳細は**[こちら](https://qiita.com/ymto/items/aaeba180f096f2a851af)**

```yaml
  dimension_group: Date {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.[Date] ;;  ### ここの[Date]というところはbigqueryでエラーになる
  }

### ↓　上記を以下のように変更
  dimension_group: Date{
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.Date ;;  ### 修正箇所：[Date] → Date に変更する
  }

```

## Lookerで株価を可視化
・**dimension**：**日付/Date**を選択
・**measure**：**7日移動平均/avg_7day_close_sum**、**当日終値/close_sum**、**当日出来高/volume_sum**の３つを選択
・株価データは土日祝のデータがない為、**Date**は**NULLを除外→**やり方は**[こちら](https://qiita.com/ymto/items/5f2e13189bd9770dd23e)**
・**当日出来高/volume_sum**は2軸にする→やり方は**[こちら](https://qiita.com/ymto/items/02f2781ad5df0d96de42)**

![株価可視化looker2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/0330c79b-4c8d-a265-62a2-8d477fb1f875.png)

## まとめ
・サクッと可視化したいなら、colab上でmatplotlibなどを使う方が楽。
・LookMLでカスタマイズがしやすそうだが、**お作法的な対処が必要**なので慣れが必要。
・LookMLで分析軸を増やして、Lookerのフィルタに追加可能なので、一度作ってしまえば、分析担当者でなくてもいろいろ分析出来る可能性があるので、**分析の民主化**的な体制を作りたい企業には良さそう。