webサイトからExcelにJSON形式データを読み込むための下準備

Excel VBAを使ってExcelのシート上にwebサイトのAPIを利用してJSON形式データを取得するためのエクセルの設定の仕方をお伝えします。

・マクロの設定を有効にする

まず、マクロの設定を有効にしておきます。

・VBA-JSONファイルをダウンロードする

https://github.com/VBA-tools/VBA-JSON/releases

上のリンクからVBA-JSON v2.2.3のZipファイルをダウンロードします。最新バージョンがあれば最新バージョンをダウンロードします。

エクセルのVBEを開く。ファイル→ファイルのインポートを選択してダウンロードしたZipファイルの中にある、JsonConverter.basというファイルを選択する。※

※ファイルのインポートからZipファイルを選択してエラーが出たら、Zipファイルから「VBA-JSON-2.2.3」のフォルダをデスクトップなどどこでもいいので外に出して、ファイルのインポートから「VBA-JSON-2.2.3」のフォルダを選択してその中のJsonConverter.basというファイルを選択する。要は、JsonConverter.basというファイルを選択すればOKです。

・Microsoft Scripting Runtimeにチェックを入れる

VBEのツールタブ→参照設定を選択し、Microsoft Scripting Runtimeにチェックを入れます。

OKを押します。
以上です。

私は実際にAPIからJSON形式データを取得するExcelツールを作成して公開しています。ファイルはこちらからダウンロードできます。
バイナンスの24時間ticker価格を取得するエクセルマクロツール　Excel VBA

エクセルの株価チャートの横軸の日付が出ない時の日付の出し方 －Excel

エクセルの株価チャートの横軸の日付が出ない、出ないと悩んでいたのですが、出し方がわかりましたので公開します。
株価チャート(出来高ー始値ー高値ー安値ー終値)の作り方もふまえてご説明します。

下のデータの株価グラフを作成します。
A列からF列まで選択します。

出来高ー始値ー高値ー安値ー終値の株価チャートを選びます。この時点でグラフがすでにおかしいですね。このグラフが出てしまって困っていました。

横軸を右クリック→軸の書式設定をクリック

右側、軸のオプションの軸の種類のテキスト軸にチェックを入れると直ります。

エクセルで13桁のUNIXTIMEを日時に変換する方法

まず、日時にするセルの書式設定を変更します。
セルの書式設定を開き、yyyy/mm/dd hh:mm:ss　を選びます。無ければ種類(T): の下に直接入力します。

1520388000000という13桁のunixtimeがA１セルに入っていたら

=(A1 /1000+ 32400) / 86400 + 25569

というunixtimeを1000で割る数式をセルに入力すれば、2018/03/08/11:00:00と返ってきます。

ちなみに10桁の場合は、

=(A1+ 32400) / 86400 + 25569

でOKです。

リップル(XRP)の週次収益率と標準偏差をエクセルで求める

前回はビットバンクのリップル(XRP)の2017年の1週間ごとの価格データをAPIを利用してエクセルに読み込みました。今回はそのデータに現在取得できる分の2018年のデータを足して、週次収益率とSD(標準偏差)を求めたいと思います。


bitbankのデータの読み込み方はこちら
ビットバンクのXRP/JPY価格をVBA-JSONでエクセルに読み込む　-Excel VBA
bitbankのリップルの過去価格データをVBA-JSONでエクセルに読み込む　-Excel VBA

収益率とはリターンの割合のことで、週次収益率とは週ごとに得られるリターンの割合です。
SD(標準偏差)とはデータのばらつきの度合いを表す指標です。

収益率と標準偏差はExcelを使えば簡単に求めることができます。

・リップル(XRP)の週次収益率と標準偏差

週次収益率は
=当週の価格 / 先週の価格－１
で求められます。

週次平均収益率は週次収益率の平均です。
AVERAGE関数で求められます。

標準偏差はSTDEV関数で求められます。

上のデータは全部で43週分です。2017年分は5月15日からのデータとなり、そこから2018年3月5日までのデータです。＊

ブログからgoogleドライブ経由でエクセルをダウンロードできるようにする方法 -blogger

ブログからgoogleドライブを使用してExcelをダウンロードできるようにする方法をお伝えします。

１．googleドライブにエクセルファイルをアップロードします。ドラッグ＆ドロップでできます。

２．アップロードしたエクセルのアイコンを右クリック→共有可能なリンクを取得をクリック

３．リンクの共有がONになっているのを確認して、リンクのアドレスをコピーします。

４．アドレスをブログにペーストします。

これでアドレスのリンクからgoogleドライブ上のエクセルファイルにアクセスしてダウンロードできます。

エクセルファイルのプロパティの作成者、前回保存者を消す方法 -Excel

Excelのプロパティの作成者、前回保存者を消す方法についてお伝えします。

・方法１
ファイル→ブックの検査→ドキュメントのプロパティと個人情報にチェック入れる→検査

これをやるとプロパティの情報を削除できるのですが、ファイル保存時に『ドキュメント検査機能では削除できない個人情報がドキュメントに含まれていることがありますので、ご注意ください。』とメッセージが表示されてしまいます。

私はこの表示を出したくなかったので他の方法を探しました。

・方法２
デスクトップのエクセルのアイコン右クリック→プロパティ→詳細タブ→下のほうにあるプロパティや個人情報を削除をクリック→このファイルから次のプロパティを削除を選択、前回保存者にチェックを入れる→OK

この方法ならメッセージも出ることなく前回保存者を削除できました。