Version 1.0

Differences between versions are listed below:

ver 1.0: compare QUANDL Difficulty vs Blockchain Difficulty sources, get total error estimate

ver 2.0: compare QUANDL Hash Rate vs Blockchain Hash Rate sources, get total error estimate

ver 3.0: Total Blocks estimate using different methods

--------------------------------

This indicator estimates Bitcoin Block Height (Total Blocks) using Difficulty and Hash Rate in the most accurate way possible, since

QUANDL doesn't provide a direct source for Bitcoin Block Height (neither QUANDL:BCHAIN, nor QUANDL:BITCOINWATCH/MINING).

Bitcoin Block Height can be used in other calculations, for instance, to estimate the next date of Bitcoin Halving.

Using this indicator I demonstrate:

- that QUANDL data is not accurate and differ from Blockchain source data (industry standard), but still can be used in calculations

- how to plot a series of data points from an external csv source and compare it with another source

- how to accurately estimate Bitcoin Block Height

Features:

- compare QUANDL Difficulty source ( EOD , D1) with external Blockchain Difficulty csv source ( EOD , D1, embedded)

- show/hide Quandl/Blockchain Difficulty curves

- show/hide Blockchain Difficulty candles

- show/hide differences (aqua vertical lines)

- show/hide time gaps (green vertical lines)

- count source differences within data range only or for the whole history

- multiply both sources by alpha to match before comparing

- floor/round both matched sources when comparing

- Blockchain Difficulty offset to align sequences, bars > 0

- count time gaps and missing bars (as result of time gaps)

WARNING:

- This indicator hits the max 1000 vars limit, adding more plots/vars/data points is not possible

- Both QUANDL/Blockchain provide daily EOD data and must be plotted on a daily D1 chart otherwise results will be incorrect

- current chart must not have any time gaps inside the range (time gaps outside the range don't affect the calculation). Time gaps check is provided.

Otherwise hardcoded Blockchain series will be shifted forward on gaps and the whole sequence become truncated at the end => data comparison/total blocks estimate will be incorrect

Examples of valid charts that can run this indicator: COINBASE:BTCUSD,D1 (has 8 time gaps, 34 missing bars outside the range), QUANDL:BCHAIN/DIFF,D1 (has no gaps)

Usage:

- Description of output plot values from left to right:

- c_shifted - 4x blockchain plotcandles ohlc, green/black (default na)

- diff - QUANDL Difficulty

- c_shifted - Blockchain Difficulty with offset

- QUANDL Difficulty multiplied by alpha and rounded

- Blockchain Difficulty multiplied by alpha and rounded

- is_different, bool - cur bar's source values are different (1) or not (0)

- count, number of differences

- bars, total number of bars/data points in the range

- QUANDL daily blocks

- Blockchain daily blocks

- QUANDL total blocks

- Blockchain total blocks

- total_error - difference between total_blocks estimated using both sources as of cur bar, blocks

- number_of_gaps - number of time gaps on a chart

- missing_bars - number of missing bars as result of time gaps on a chart

- Color coding:

- Blue - QUANDL data

- Red - Blockchain data

- Black - Is Different

- Aqua - number of differences

- Green - number of time gaps

- by default the indicator will show lots of vertical aqua lines, 138 differences, 928 bars, total error -370 blocks

- to compare the best match of the 2 sources shift Blockchain source 1 bar into the future by setting Blockchain Difficulty offset = 1, leave alpha = 0. 01 =>

this results in no vertical aqua lines, 0 differences, total_error = 0 blocks

if you move the mouse inside the range some bars will show total_error = 1 blocks => total_error <= 1 blocks

- now uncheck Round Difficulty Values flag => some filled aqua areas, 218 differences.

- now set alpha = 1 (use raw source values) instead of 0. 01 => lots of filled aqua areas, 871 differences.

although there are many differences this still doesn't affect the total_blocks estimate provided Difficulty offset = 1

Methodology:

To estimate Bitcoin Block Height we need 3 steps, each step has its own version:

- Step 1: Compare QUANDL Difficulty vs Blockchain Difficulty sources and estimate error based on differences

- Step 2: Compare QUANDL Hash Rate vs Blockchain Hash Rate sources and estimate error based on differences

- Step 3: Estimate Bitcoin Block Height (Total Blocks) using different methods in the most accurate way possible

QUANDL doesn't provide block time data, but we can calculate it using the Hash Rate approximation formula:

estimated Hash rate/sec H = 2^32 * D / T, where D - Difficulty, T - block time, sec

1. block time (T) can be derived from the formula, since we already know Difficulty (D) and Hash Rate (H) from QUANDL

2. using block time (T) we can estimate daily blocks as daily time / block time

3. block height (total blocks) = cumulative sum of daily blocks of all bars on the chart (that's why having no gaps is important)

Notes:

- This code uses Pinescript v3 compatibility framework

- hash rate is in THash/s, although QUANDL falsely states in description GHash/s! THash = 1000 GHash

- you can't read files, can only embed/hardcode raw data in script

- both QUANDL and Blockchain sources have no gaps

- QUANDL and Blockchain series are different in the following ways:

- all QUANDL data is already shifted 1 bar into the future, i.e. prev day's value is shown as cur day's value => Blockchain data must be shifted 1 bar forward to match

- all QUANDL diff data > 1 bn (10^12) are truncated and have last 1-2 digits as zeros, unlike Blockchain data => must multiply both values by 0. 01 and floor/round the results

- QUANDL sometimes rounds, other times truncates those 1-2 last zero digits to get the 3rd last digit => must use both floor/round

- you can only shift sequences forward into the future (right), not back into the past (left) using positive offset => only Blockchain source can be shifted

- since total_blocks is already a cumulative sum of all prev values on each bar, total_error must be simple delta, can't be also int(cum()) or incremental

- all Blockchain values and total_error are na outside the range - move you mouse cursor on the last bar/inside the range to see them

TLDR, ver 1.0 Conclusion:

QUANDL/Blockchain Difficulty source differences don't affect total blocks estimate, total error <= 1 block with avg 150 blocks/day is negligible

Both QUANDL/Blockchain Difficulty sources are equally valid and can be used in calculations. QUANDL is a relatively good stand in for Blockchain industry standard data.

Links:

QUANDL difficulty source: https://www.quandl.com/data/BCHAIN/DIFF-...

QUANDL hash rate source: https://www.quandl.com/data/BCHAIN/HRATE...

Blockchain difficulty source (export data as csv ): https://www.blockchain.com/charts/diffic...

Changes in ver 2.0:

Features:

- removed plot Blockchain candles

- show/hide Quandl Hash Rate MA

- option to use Quandl Hash Rate MA instead of Quandl Hash Rate in source comparisons

Usage:

- Description of output plot values from left to right:

- THash_rate_src - QUANDL Hash Rate

- THash_rate_ma - QUANDL Hash Rate MA

- c_shifted - Blockchain Hash Rate with offset

- QUANDL Hash Rate multiplied by alpha and rounded

- Blockchain Hash Rate multiplied by alpha and rounded

- is_different, bool - cur bar's source values are different (1) or not (0)

- count, number of differences

- bars, total number of bars/data points in the range

- QUANDL daily blocks

- Blockchain daily blocks

- QUANDL total blocks

- Blockchain total blocks

- total_error - difference between total_blocks estimated using both sources as of cur bar, blocks

- number_of_gaps - number of time gaps on a chart

- missing_bars - number of missing bars as result of time gaps on a chart

- by default the indicator will show many filled aqua areas, 906 differences, 928 bars, total error 332 blocks

- to compare the best match of the 2 sources shift Blockchain source 1 bar into the future by setting Blockchain Hash Rate offset = 1 =>

this results in just a few vertical aqua lines, 15 differences, total_error = 8 blocks

if you move the mouse inside the range some bars will show total_error = 20 blocks => max total_error = 20 blocks

- now uncheck both Round and Floor Hash Rate Values flag => the whole range is filled with aqua areas, 928 differences, total_error = 8 blocks

That's because Blockchain Hash Rate data has higher precision with more digits after the decimal point

although there are many differences this still doesn't affect the total_blocks estimate provided Hash Rate offset = 1

- now set ma length = 7 (sma is used) => 928 differences, total_error = 1039 blocks

- now enable again Round Hash Rate value flag => 927 differences, total_error = 1039 blocks

In the whole range there's only 1 matching bar for sma source after rounding

- Rounding Hash Rate Values barely affects the results

- multiplying sources by alpha doesn't affect the results

Notes:

- Blockchain hash rate data has more precision

- when MA Length > 0 (Enabled) QUANDL Hash Rate SMA is used in source comparisons instead of QUANDL Hash Rate

TLDR, ver 2.0 Conclusion:

QUANDL/Blockchain Hash Rate source differences don't affect total blocks estimate, total error <= 20 blocks with avg 150 blocks/day is negligible

Both QUANDL/Blockchain Hash Rate sources are equally valid and can be used in calculations. QUANDL is a relatively good stand in for Blockchain industry standard data.