Bitcoin USD Exchange Rates Versus Major Market ETFs
During my early years as a developer, I encountered an
executive who would often shout “developers, developers, developers…” at the
beginning of his conference presentations until he turned red in the face or
ran out of breath. He retired as a very
rich person. This experience motivates
me to write “bitcoins, bitcoins, bitcoins…” because the post is meant to move data
analysts along the path to enjoying a rich, fulfilling, and early retirement
via bitcoin investing.
The BTC-USD
ticker at the Yahoo Finance site tracks
the price of a bitcoin in US dollars.
The bitcoin.org website describes
bitcoins as a new kind of money (store of value) on its home page; bitcoins
exist on the internet in a blockchain network. The network enables bitcoin miners
to track bitcoin ownership between those owning bitcoins and those accepting
bitcoins in exchange for goods and/or services. Bitcoin miners are rewarded with new bitcoins
for each new block of exchanged bitcoins they discover as well as a transaction
fee for bitcoins they verify as valid bitcoins in a block on the blockchain. Happily, you do not need to know the
mechanics of the bitcoin blockchain to benefit financially from bitcoins. Nevertheless, those of you who want to gain an
introductory understanding of the underlying blockchain technology for bitcoins
may find it beneficial to get started with a free
video series from the Khan Academy.
This post focuses on the bitcoin exchange rate in US dollars
and how this exchange rate compares in growth to a couple of ETFs for major
market indexes. By performing these
analyses, the post indicates if bitcoins can grow value faster than a
collection of stocks.
As you read this post, you may find it useful to refer to three
prior posts. Bitcoin-related
Ticker Performance in 2023 Year and in December 2023 presents an
introduction to price tracking for bitcoin-related securities. How
to Compare Close Prices for Three Securities Over Four Years illustrates an
approach to using compound annual growth rates for comparing different
securities. How
to Download and Display Historical Price and Volume Data demonstrates three
approaches for transferring historical securities data, such as for the BTC-USD
ticker, from Yahoo Finance to your computer. One of these approaches is utilized in this
post.
Comparing BTC-USD to major market tickers
This post begins by collecting BTC-USD ticker price data and
comparing them to prices for subsets of the S&P 500 index and the NASDAQ
market. The QQQ is a widely used ETF for
tracking the top 100 securities traded on
the NASDAQ market. The OEF is a
comparable ETF for tracking the top 100 securities in the S&P 500 index. By comparing the returns from the BTC-USD
versus the OEF and QQQ, this posts reflects the growth of bitcoin prices relative
to the prices for a couple of different baskets of leading stock prices.
This post section demonstrates how to collect data for comparing
BTC-USD prices to OEF and QQQ share prices.
The next section of this post shows how to compute growth rates for
BTC-USD prices versus OEF and QQQ share prices.
As referenced above, you can download historical securities
data from Yahoo Finance. When you do
this, the website sends a csv file to the download folder on your
computer. If you need assistance with
how to invoke this Yahoo Finance capability, view the post titled How
to Download and Display Historical Price and Volume Data. Depending on your preferences, you can process
the data from the download folder or you can copy the data to a new location on
your workstation for easy future reference.
For this post, the downloaded csv files were moved to the path named 'C:\SecurityTradingAnalytics\Bitcoin_Tickers_as_of_022924\’. One csv file each was downloaded from Yahoo
Finance for each of the three tickers tracked in this post.
The following T-SQL script illustrates one approach to inserting
BTC-USD, OEF, and QQQ historical prices into the [dbo].[symbol_date] table within
the SecurityTradingAnalytics database.
This database was created to serve as a data repository for
demonstrating T-SQL code to facilitate demonstrations of trading security
analytics.
- The script begins by creating two tables – PriceAndVolumeDataFromYahooFinance and symbol_date. Both tables reside in the dbo schema of the SecurityTradingAnalytics database.
- For each ticker’s csv file, the script successively copies with a bulk insert statement the downloaded file contents to an empty version of the PriceAndVolumeDataFromYahooFinance table. Next, the script adds a column designating the ticker and copies the updated results set to [dbo].[symbol_date].
- At the end of the script, the symbol_date table contains historical data rows for all three tickers.
·
The top screenshot shows five rows of data for
the beginning five dates for the BTC-USD ticker. Notice the initial date is 2014-09-17.
·
The bottom screenshot displays five rows of data
for the final five dates for the QQQ ticker.
The last date for which data are collected for this post is 2024-02-29.
·
Between the data for the BTC-USD and QQQ tickers
in the symbol_date table are comparable data for the OEF ticker.
The distinction between Yahoo Finance close and adjclose column values is not straightforward, but the text in this paragraph and other content in this post may highlight the most important differences between close and adjclose column values and the significance of the differences. According to multiple pages at the stackoverflow.com site (page1, page2, and page3), the close column values and adjclose column values are both adjusted for stock splits. On the other hand, the adjclose column values are adjusted for both stock splits and dividends. For assessing long-run returns, such as between 2014-09-17 through 2024-02-29, it is best to use adjclose column values.
- Close values are good for reflecting the actual close price at the end of a trading day.
- Adjclose values reflect dividends, which are usually distributed quarterly by a company; some securities issue dividends on a monthly, weekly, or ad hoc interval. Over an extended period, dividends can add up and materially affect the total return value of an investment in a security. Selected other corporate actions that can impact adjclose column values are mergers, spin-offs, and share buybacks.
There is another issue to consider when comparing growth
between BTC-USD versus either OEF or QQQ.
Namely, BTC-USD prices (from both close and adjclose column values) do not
reflect splits and dividends because there are none for bitcoin exchange rates. Bitcoin is not a company that takes corporate
actions to split shares or issue dividends.
On the other hand, individual members of the basket of securities
tracked by either OEF or QQQ tickers do sometimes split their shares or issue
dividends. Additionally, rapidly growing
companies sometimes may split the total volume of share, which decreases the
price per share.
Security price adjustments are important especially when
comparing long-run returns for different securities. Long-run security returns are sometimes assessed
via compound annual growth rates. Recall
that a
prior post includes coverage of how to compute compound annual growth rates
with Excel. The current post expands and extends the
prior post by performing the calculations in T-SQL for the tables in a SQL
Server database – namely, the SecurityTradingAnalytics database.
Computing Compound Annual Growth Rates Based on Close and Adjclose Values
The following T-SQL script shows code for computing compound
annual growth rates for each of three tickers for which data was collected in
the preceding section. The compound annual growth rate depends on the two different
types of close values for each ticker (close column values and adjclose column
values). As indicated in the preceding
section, the preferred approach is to adjclose values instead of the close
values when performing fundamental analysis and/or a security’s value over an
extended period of time.
The compound annual growth rate depends on two key terms:
- The end_adjclose value divided by the begin_adjclose and;
- The inverse of the fractional number of years from the begin_date through the end_date.
- The fractional number of trading years is computed slightly differently for the BTC-USD ticker versus the other two tickers (OEF and QQQ) because the number of trading days in a year is different for the two types of tickers.
- The fractional number of years for the BTC-USD is the fractional number of trading days from 2014-09-17 through 2024-02-29 divided by 365.25. Additionally, there is one trading day for each day in a calendar year. Bitcoins can be exchanged 24 hours per day and seven days per week for each calendar day in a year.
- For the OEF and QQQ tickers the fractional number of years is the fractional number of trading days from 2014-09-17 through 2024-02-29 divided 252, which Wikipedia specifies as the average number of trading days for exchange traded securities in a calendar year.
- The following script returns the cagr value in percentage points with up to two places after the decimal point.
- The script below operates on adjclose values for each ticker.
- The results set from the tip includes three rows – one for each ticker.
As a point of reference, the following script computes the compound annual growth rate based on close values, instead of adjclose values. When working with an extended range of dates, such as 2014-09-17 through 2024-02-29, it is usually preferable to compute cagr values based on adjclose values. Recall that this is because adjclose values adjust for dividends and other corporate actions that can impact adjclose values, but close values from Yahoo Finance are only adjusted for stock splits. Furthermore, the longer the duration from beginning through end dates, the greater the difference in compound annual growth rates will be between those computed on close values versus adjclose values.
-- from 2014-09-17 through 2024-02-29
-- begin_close and end_close values are from close column values
use SecurityTradingAnalytics
go
select
begin_closes.symbol symbol
,begin_closes.[date] begin_date
,end_closes.[date] end_date
,begin_closes.begin_close
,end_closes.end_close
,[for fractional trading years].[fractional trading years]
,end_closes.end_close/begin_closes.begin_close [end_close/begin_close]
,round((power((end_closes.end_close/begin_closes.begin_close),
1/[for fractional trading years].[fractional trading years]) - 1) *100,2) [cagr]
from
(
-- begining historical data by ticker
select symbol, date, [close] [begin_close], adjclose [begin_adjclose]
from [dbo].[symbol_date]
where [date] = '2014-09-17'
) begin_closes
join
(
-- ending historical data by ticker
select symbol, date, [close] [end_close], adjclose [end_adjclose]
from [dbo].[symbol_date]
where [date] = '2024-02-29'
) end_closes
on begin_closes.symbol = end_closes.symbol
join
(
-- compute fractional trading years
select
symbol
,[total trading days]
,
case
when symbol = 'BTC-USD' THEN cast([total trading days] as float)/365.25
else cast([total trading days] as float)/252
end [fractional trading years]
from
(
-- count total trading days per ticker
select
symbol
,count(*) [total trading days]
from [dbo].[symbol_date]
group by Symbol
) [for total trading days]
) [for fractional trading years]
on begin_closes.symbol = [for fractional trading years].symbol
Focusing on the top results set, it is clear that BTC-USD
exchange rates grow from slightly less than 4 times to slightly more than 5 times
corresponding OEF and QQQ ticker stock prices.
The compound growth rates computed with the close values also
show that compound annual growth rate for the BTC-USD ticker vastly exceeds
that for the other two tickers. However,
the differences are even larger for cagr value computed based on close values
than for cagr values computed on adjclose value. This is because adjclose values include
dividend values that are missing from close values.
The fractional trading years for the BTC-USD ticker are
marginally less than for the other two tickers.
All three tickers have the same
begin and end dates, but the BTC-USD ticker has more trading days and a different
divisor to convert trading days to trading years. This is because bitcoins can trade 24
hours per day on 7 days per week throughout
a calendar year. In contrast, shares for
ticker symbols traded in stock exchanges only trade during hours and days when
exchanges are open.
Closing Points
Thanks for reading this post through to the end. You likely noticed that the post confirms that
bitcoin prices grow dramatically faster on average than baskets of leading
securities from the S&P 500 index or the NASDAQ market. This post does not say anything about how to
invest in bitcoins to participate in the exceedingly rapid growth rates from
bitcoins relative to leading stock securities.
A
prior post indicates some of the ways that you can participate in bitcoin
investing. Future posts will dive more
deeply into how you can invest in bitcoins either directly or indirectly.
When considering investments in bitcoins, you should be
aware that compound average growth rates do not fully reflect the risks
associated with this asset class. Future
posts will also offer at least an introductory level of treatment of these
risks. However, despite the well-known
risks of bitcoin investing, this post confirms that bitcoin can appreciate in
price over an extended period much more rapidly than leading securities from
the S&P 500 index or the NASDAQ market.
By learning more about the risks and the rewards for different kinds of
investments, such as bitcoins, stocks, bonds, and commodities, you can assess
which asset classes in which proportions are best for your objectives.
If you would like the files referenced in this tip, please send a request to RickDobsonBlogs@gmail.com. I will reply with the files used to create this blog back to your email address.
If you would like additional specific coverage of bitcoin technical and investment topics, please send a request to RickDobsonBlogs@gmail.com. I will reply with future posts that cover those topics to the best of my ability.
Comments
Post a Comment