表示する銘柄は適当にBit coin, Ethereum, Ripple, Lite coin, Liskとします。
他のADAとかEOSなどは表示しないようにします。
①まずは、下記の通りにExcelのシートのB列のsymbleのセルに銘柄のシンボル名を入力します。
②下記のコードを実行します。
Sub Crypto_JSON_In_Excel()
Dim CryptoCurrencyJSON As Object, CryptoCurrencyNode
Dim WinHttpReq As Object, TickerJSON As String
Dim tickerSheet As Worksheet, arr(10) As Variant
Set tickerSheet = ThisWorkbook.Sheets(1)
arr(1) = "name"
arr(2) = "symbol"
arr(3) = "price_jpy"
arr(4) = "price_btc"
arr(5) = "24h_volume_jpy"
arr(6) = "market_cap_jpy"
arr(7) = "available_supply"
arr(8) = "percent_change_1h"
arr(9) = "percent_change_24h"
arr(10) = "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
'TickerJSON = DU("https://api.coinmarketcap.com/v1/ticker/")
Set CryptoCurrencyJSON = ParseJson(TickerJSON)
For i = 1 To 10
tickerSheet.Cells(1, i) = arr(i)
Next i
trow = 2
For Each CryptoCurrencyNode In CryptoCurrencyJSON
If UCase(Cells(trow, 2)) = CryptoCurrencyNode(arr(2)) Then
For i = 1 To 10
tickerSheet.Cells(trow, i) = CryptoCurrencyNode(arr(i))
Next
trow = trow + 1
Else: End If
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
●実行結果
入力したシンボルに合わせてデータが表示されました。
