I collect crypto prices and save them to a database every minute.
There are times every now and then when my system is down, which creates gaps in my prices data.
I found out how to get Historic Prices between two time stamps. e.g:
When the system comes back up I have a PHP script that checks for gaps and then constructs the above URL call to get the prices.
This works well for most of the crypto, but for a few I get no data being returned, e.g. ATOM-GBP.
After some investigation I found it is because for these I must specify ATOM-USD, or ATOM-BTC.
The problem here is first knowing which one to use, and secondly, how to convert the returned price to GBP at the timestamp concerned.
Here is my snippet code:
<?php
date_default_timezone_set('Europe/London'); // YOUR timezone, of the server
ini_set('memory_limit', '256M');
?>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="style.css?v=<?=time();?>">
</head>
<body>
<H1> Missing Prices <?= date("Y-m-d H:i:s") ?> </H1>
<p>
<?php
error_reporting(E_ALL);
include 'cfg.php';
require_once ('vendor/autoload.php');
include ('db_conn.php');
use Coinbase\Wallet\Client as Client;
use Coinbase\Wallet\Configuration as Configuration;
$configuration = Configuration::apiKey($cbase_API_Key, $cbase_API_Secret);
$client = Client::create($configuration);
// List prices from local database
$price_sql = "
SELECT p1.ccode,
CONVERT_TZ(p1.tstamp,'Europe/London','UTC') ts1,
UNIX_TIMESTAMP(CONVERT_TZ(p1.tstamp,'Europe/London','UTC')) ut
FROM prices p1
WHERE p1.tstamp BETWEEN (NOW() - INTERVAL 5 DAY ) AND (NOW() - INTERVAL 3 DAY )
ORDER BY 1, 2
";
$stmt = $dbh->prepare($price_sql);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);
$num_rows = $result -> num_rows;
$prev_ut = -1;
$prev_ts = "";
$prev_curr = "";
$c = 0;
foreach ($data as $row)
{
if ( $prev_ut == -1 )
{
$prev_ut = $row['ut'];
$prev_ts = $row['ts1'];
$prev_curr = $row['ccode'];
}
$ut_diff = $row['ut'] - $prev_ut;
if ( $prev_curr != $row['ccode'] )
{
$ut_diff = 0;
}
// If the gap between current and previous row is over 5 minutes then we have a gap
if ( $ut_diff > 300 )
{
echo '<H2> Missing data for ' . $row['ccode'] . " between " .
$row['ts1'] . " and " . $prev_ts . " diff " . $ut_diff . "</H2>";
// Build up URL
$nxt_ts = DateTime::createFromFormat('Y-m-d H:i:s', $prev_ts );
$pts = $nxt_ts->format('Y-m-d H:i:s');
$strt = str_replace(' ', 'T', $prev_ts);
$end = str_replace(' ', 'T', $row['ts1']);
$url = "https://api.pro.coinbase.com/products/" . $row['ccode'] . "-GBP/candles?start=" . $strt . ".0Z&end=" . $end . ".0Z&granularity=300";
echo '<h3> ' . $url . '</h3>';
echo '<table border="1">';
echo '<tr>';
echo '<th> No</th>';
echo '<th> Currency </th>';
echo '<th> Time </th>';
echo '<th> Low </th>';
echo '<th> High </th>';
echo '<th> Open </th>';
echo '<th> Close </th>';
echo '<th> Volume </th>';
echo '</tr>';
// Use curl to get the data
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
$prices_json = json_decode(curl_exec($ch), true);
foreach($prices_json as $price )
{
$c++;
$utime = gmdate( 'r', $price[0]);
$low = $price[1];
$high = $price[2];
$open = $price[3];
$close = $price[4];
$volume = $price[5];
echo '<tr>';
echo '<td>' . $c . '</td>';
echo '<td>' . $row['ccode'] . '</td>';
echo '<td>' . $utime . '</td>';
echo '<td>' . $low . '</td>';
echo '<td>' . $high . '</td>';
echo '<td>' . $open . '</td>';
echo '<td>' . $close . '</td>';
echo '<td>' . $volume . '</td>';
echo '</tr>';
}
}
echo '</table>';
$prev_ut = $row['ut'];
$prev_ts = $row['ts1'];
$prev_curr = $row['ccode'];
}
?>
Results snippet:
Missing data for ATOM between 2021-05-13 09:44:12 and 2021-05-13 02:20:06 diff 26646
https://api.pro.coinbase.com/products/ATOM-GBP/candles?start=2021-05-13T02:20:06.0Z&end=2021-05-13T09:44:12.0Z&granularity=300
Warning: gmdate() expects parameter 2 to be integer, string given in fillpricegaps.php on line 172
No Currency Time Low High Open Close Volume
409 ATOM o t F o u
Missing data for BAL between 2021-05-13 09:44:15 and 2021-05-13 02:20:10 diff 26645
https://api.pro.coinbase.com/products/BAL-GBP/candles?start=2021-05-13T02:20:10.0Z&end=2021-05-13T09:44:15.0Z&granularity=300
Warning: gmdate() expects parameter 2 to be integer, string given in fillpricegaps.php on line 172
No Currency Time Low High Open Close Volume
410 BAL o t F o u
Missing data for BAND between 2021-05-13 09:44:13 and 2021-05-13 02:20:08 diff 26645
https://api.pro.coinbase.com/products/BAND-GBP/candles?start=2021-05-13T02:20:08.0Z&end=2021-05-13T09:44:13.0Z&granularity=300
No Currency Time Low High Open Close Volume
411 BAND Thu, 13 May 2021 09:40:00 +0000 10.4942 10.5993 10.5939 10.5248 1080.08
412 BAND Thu, 13 May 2021 09:35:00 +0000 10.6262 10.7359 10.717 10.627 185.25
413 BAND Thu, 13 May 2021 09:30:00 +0000 10.6525 10.8151 10.6525 10.7991 338.32
414 BAND Thu, 13 May 2021 09:25:00 +0000 10.641 10.7716 10.7521 10.641 134.54
Can you not grab the BTCUSD and also BTCGBP to find the conversion rate between USD and GBP?