coinMarketCapToPowerPointSlidesCode/Code at master · ryan-gontarek/coinMarketCapToPowerPointSlidesCode · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
Sub Button1_Click()

    Dim wb As Workbook
    Dim wsCMS As Worksheet
    Dim wsAPI As Worksheet

    Dim cellName As Range
    Dim cellMarketCap As Range
    Dim cellCMS As Range

    Set wb = ActiveWorkbook

    'Refresh web queries' http requests
    wb.RefreshAll

    Set wsCMS = wb.Sheets("CryptoMarketSize(API)")
    Set wsAPI = wb.Sheets("ProcessedAPI")

    wsAPI.Columns(2).ClearContents
    wsAPI.Columns(4).ClearContents

    'Refresh web queries' http requests
    wb.RefreshAll

    Set cellName = wsAPI.Range("B2")
    Set cellMarketCap = wsAPI.Range("D2")

    cellName.Value = "Name:"
    cellMarketCap.Value = "Market Capitalization:"

    lineRow = 2
    nameRow = 3
    mcRow = 3
    'start at cell B4 and D4 on ProcessedAPI
    ' and at cell D2 on CryptoMarketSize(API)
    Set cellName = wsAPI.Range("B" & nameRow)
    Set cellMarketCap = wsAPI.Range("D" & mcRow)
    Set cellCMS = wsCMS.Range("D" & lineRow)
    'while Dsub(k) is not null do: //every line in the CryptoMarketSize(API)
    While cellCMS.Value <> ""

        'checks if line contains "name":
        If hasName(cellCMS.Value) Then
            'insert name into ProcessedAPI
            cellName.Value = getName(cellCMS.Value)
            nameRow = nameRow + 1
        End If

        'check if line contains "market_cap_usd":
        If hasMarketCap(cellCMS.Value) Then
            'insert MarketCap into ProcessedAPI
            'increment Dsub(4) to Dsub(i+1)
            cellMarketCap.Value = getMarketCap(cellCMS.Value)
            mcRow = mcRow + 1
       End If

        'set cellCMS to next line from web query
        lineRow = lineRow + 1
        Set cellCMS = wsCMS.Range("D" & lineRow)
        Set cellName = wsAPI.Range("B" & nameRow)
        Set cellMarketCap = wsAPI.Range("D" & mcRow)

    Wend

    wb.RefreshAll

End Sub

Function hasName(line As String) As Boolean

    Dim strArray As Variant

    strArray = Split(line, " ")

    hasName = False
    For i = 0 To UBound(strArray)
        If strArray(i) = """name"":" Then
            hasName = True
        End If
    Next

End Function

Function hasMarketCap(line As String) As Boolean

    Dim strArray As Variant

    strArray = Split(line, " ")

    hasMarketCap = False
    For i = 0 To UBound(strArray)
        If strArray(i) = """market_cap_usd"":" Then
            hasMarketCap = True
        End If
    Next

End Function

Function getMarketCap(line As String) As Double

    Dim foundMarketCap As Boolean
    Dim marketCap As String
    Dim strArray As Variant

    strArray = Split(line, " ")

    'finds bid price in line array separated by blank spaces
    For i = 0 To UBound(strArray)
        If foundMarketCap Then
            marketCap = strArray(i)
            If foundMarketCap Then Exit For
        End If

        If strArray(i) = """market_cap_usd"":" Then
            foundMarketCap = True
        End If
    Next

    'removes quotes from name string
    marketCap = Replace(marketCap, """", "")
    marketCap = Replace(marketCap, ",", "")

    getMarketCap = CDbl(marketCap)

End Function

Function getName(line As String) As String

    Dim foundName As Boolean
    Dim name As String
    Dim strArray As Variant

    strArray = Split(line, ":")
    strArray(0) = Trim(strArray(0))
    strArray(1) = Trim(strArray(1))

    If strArray(i) = """name""" Then
            name = strArray(1)
    End If

    'removes quotes from name string
    name = Replace(name, """", "")
    name = Replace(name, ",", "")

    getName = name

End Function