Coinpaprika dashboard in Google Sheets (3)

In dit artikel ben ik een begonnen met het maken van een dashboard in Google Sheets met behulp van de Coinpaprika Api.

In deel 3 gaan we de volgende elementen toevoegen:

  • Ticker
  • Grafiek van Prijs over de laatste 30 dagen

Ticker

Eerst gaan we in het dashboard de kopjes plaatsen van de ticker, vul in de cellen G1 t/m O1 de volgende gegevens: Current price, Change 1h, Change 12h, Change 24h, Change 7d, Change 30d, Change 1y, All time high en From ATH. En in de cellen F2 t/m F4: BTC, USD, EUR. Je kan deze zelf aanpassen naar de gewenste valuta.

Zoals je inmiddels gewend bent maken we een nieuw tabblad/sheet (+ links onderin) voor de ticker import en geven het de naam “Coin ticker”. En vullen de standaardgegevens in:

A2: Help
B2: https://api.coinpaprika.com/#tag/Tickers/paths/~1tickers~1{coin_id}/get
A3: URL
B3: ="https://api.coinpaprika.com/v1/tickers/"&coin

In het dashboard staan de drie tickers die ik wil hebben (BTC, USD en EUR). Daarvoor moet drie keer een import worden gedaan, vul de volgende cellen:

A5: Quotes
B5: =ImportJSON($B$3&A6)
A6: =Dashboard!F2
A7: =Dashboard!F3
B7: =ImportJSON($B$3&A7,,"noHeaders")
A8: =Dashboard!F4
B8: =ImportJSON($B$3&A8,,"noHeaders")
Uitleg functies
ImportJSON geeft standaard de headers mee, deze heb ik maar één keer nodig dus daarom onderdruk ik de headers met “noHeaders” bij de import van de USD en EUR ticker. Tevens maak ik de URL naar de ticker dynamisch door &A7 aan standaard URL toe te voegen. Zo kun je zelf eventueel de ticker in een andere valuta weergeven (aanpassen in het dashboard!).

We gaan weer terug naar het Dashboard en gaan de gegevens plaatsen:

G2: =VALUE('Coin ticker'!K6)

Kopieer nu deze cel naar H2 t/m O2 én naar H3 t/m M3. Het ziet er nu als volgt uit:

Uitleg functies
Bij de import krijg je alles terug als tekst. Om er mee te kunnen rekenen moet de tekst wat eigenlijk een getal is omgezet worden naar een getal, dat doen we met de formule VALUE.

Grafiek

Natuurlijk mag er geen grafiek missen in deze dashboard. Ik wil een grafische weergave van de prijsschommelingen (in BTC) over de afgelopen 30 dagen. Deze gegevens halen we op met de historische data van OHLC (Open, High, Low & Close). Voeg een nieuw tabblad/sheet toe (+ links onderin) en geef het de naam “OHLC”. En vul de volgende cellen:

A2: Help
B2: https://api.coinpaprika.com/#tag/Tickers/paths/~1tickers~1{coin_id}/get
A3: URL
B3: ="https://api.coinpaprika.com/v1/coins/"&coin&"/ohlcv/historical?start="&B4&"&end="&D4&"&quote=btc"

Omdat de datums dynamisch zijn, namelijk begindatum is vandaag-31 en einddatum is vandaag, voegen we nog een aantal cellen toe om de import url compleet te maken. Vul de volgende cellen:

A4: Startdatum
B4: =TEXT(TODAY()-31,"yyyy-mm-dd")
C4: Einddatum
D4: =TEXT(TODAY(),"yyyy-mm-dd")
Uitleg functies
TODAY geeft de datum vandaag, deze waarde moeten we omzetten naar een tekst-string zodat we het aan de import-url kunnen plakken en dat doen we met de functie TEXT

Nu is het tijd om de import te doen, vul de cel:

A6: =ImportJSON(B3)

Zoals eerder gezegd, we krijgen de import gegevens als tekst, dus moeten we de gegevens wie we willen gebruiken omzetten naar een getal. Vul de volgende cellen:

I6: Value Close
I7: =ArrayFormula(IF(F7:F<>"", VALUE(F7:F),""))
J6: Sparkline data
J7: leeg laten!
J8: =I8-I7
K6: %
K7: leeg laten!
K8: =I8/I7-1

In kolom K staan percentages, dus moeten we het format van de cellen aanpassen: selecteer kolom K en ga in menu >> Format >> Number >> Percent. Kopieer nu de cellen J8 naar J9 t/m J38 en K8 naar K9 t/m K38.

Uitleg functies
De ARRAYFORMULA vult de kolom automatisch met dezelfde functie, handig! Ik voeg de IF functie toe om er voor te zorgen dat de formule alleen wordt gekopieerd zolang er een waarde in kolom A staat.

Ga weer naar het dashboard en voeg de cellen H7 t/m N11 samen. Hierin komt de grafiek. En vul de volgende cellen:

F7: high
F11: low
G7: =MAX(OHLC!I8:I37)
G11: =MIN(OHLC!I8:I37)
H12: =TODAY()-30
N12: =TODAY()
O7: =MAX(OHLC!K8:K37)
O11: =MIN(OHLC!K8:K37)
Uitleg functies
MAX haalt de hoogste waarde uit een range en MIN de laagste waarde.

Ik heb de cellen F6 t/m O6 ook samengevoegd ten behoeve van de titel van de grafiek en er Price last 30 days in gezet en de uitlijning gecentreerd.

En dan nu tijd voor de grafiek, vul de volgende cel:

H7: =SPARKLINE(OHLC!J8:J,{"charttype","column";"negcolor","red"})
Uitleg functies
Met SPARKLINE kun je een grafiek maken in één (samengevoegde) cel. Voor meer info klik hier.

Als alles goed gegaan is ziet je dashboard er ongeveer zo uit:

Hits: 1933

You may also like...