やれExcel、やれGoogle スプレッドシート: Excelで仮想通貨の資産管理3 -Excel VBA

2018/02/07

Excelで仮想通貨の資産管理3 -Excel VBA

前回は全銘柄を合計した円建ての資産総額を表示しました。

Excelで仮想通貨の資産管理2 -Excel VBA

今回は各銘柄の円建て資産額が全体の円建て資産額に占める割合（パーセンテージ）を表示したいと思います。F列の1番上のセルにpercentageを追加しました。
※D列に保有分の通貨数量を入力します。B列のシンボルも間違えないで正確に入力します。下記のように入力すればOKです。

・コード

Sub Crypto_JSON_In_Excel()
Dim CryptoCurrencyJSON As Object, CryptoCurrencyNode
Dim WinHttpReq As Object, TickerJSON As String
Dim tickerSheet As Worksheet, arr(18) As Variant
Dim val_jpy As Long
Dim val_sum As Long
Dim i As Integer
Dim ii As Integer

Set tickerSheet = ThisWorkbook.Sheets(1)

arr(1) = "name"
arr(2) = "symbol"
arr(3) = "price_jpy"
arr(4) = "holding"
arr(5) = "value_jpy"
arr(6) = "percentage"
arr(7) = "percent_change_1h"
arr(8) = "percent_change_24h"
arr(9) = "percent_change_7d"

Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
WinHttpReq.Open "GET", "https://api.coinmarketcap.com/v1/ticker/?convert=JPY&limit=30", False
WinHttpReq.send
TickerJSON = WinHttpReq.responseText
Set WinHttpReq = Nothing

Set CryptoCurrencyJSON = ParseJson(TickerJSON)

For i = 1 To 9

tickerSheet.Cells(1, i) = arr(i)

Next i

trow = 2
val_sum = 0
For Each CryptoCurrencyNode In CryptoCurrencyJSON
If UCase(Cells(trow, 2)) = CryptoCurrencyNode(arr(2)) Then

For i = 1 To 9

If i = 4 Then
GoTo skip1
Else: End If

tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))
skip1:
Next
val_jpy = Cells(trow, 3) * Cells(trow, 4)
tickerSheet.Cells(trow, 5) = Application.WorksheetFunction.RoundDown(val_jpy, 0)

val_sum = Cells(trow, 5) + val_sum
trow = trow + 1

Else: End If

Next
tickerSheet.Cells(trow, 5) = val_sum

For ii = 2 To trow - 1
tickerSheet.Cells(ii, 6) = Cells(ii, 5) / val_sum
tickerSheet.Cells(ii, 6).NumberFormatLocal = "0.0%"
Next

With Range(Cells(2, i - 3), Cells(trow - 1, i - 1))

.FormatConditions.AddColorScale ColorScaleType:=3
.FormatConditions(Range(Cells(2, i - 3), Cells(trow - 1, i - 1)).FormatConditions.Count).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(1).Value = -100
.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = 7039480

.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(2).Value = 0
.FormatConditions(1).ColorScaleCriteria(2).FormatColor.ThemeColor = xlThemeColorDark1

.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(3).Value = 100
.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = 8109667
End With

End Sub

●実行結果
F列にそれぞれの銘柄の保有割合が表示されました。