Basic demo of TSFrames using financial data
Create a TSFrame object for IBM historical data
To load the IBM historical data, we will use the MarketData.yahoo function from MarketData.jl, which returns the data in the form of a TimeArray. We just simply pass this on to the TSFrame constructor.
julia> using TSFrames, MarketData, Plots, Statistics, Impute, GLMjulia> ibm_ts = TSFrame(MarketData.yahoo(:IBM))13456×6 TSFrame with Date Index Index Open High Low Close AdjClose Volume Date Float64 Float64 Float64 Float64 Float64 Float64 ────────────────────────────────────────────────────────────────────────────── 1971-02-08 16.0851 16.336 15.8939 16.2882 3.55486 719648.0 1971-02-09 16.2882 16.3121 16.1687 16.1807 3.53139 673624.0 1971-02-10 16.1568 16.1568 15.9775 16.1209 3.51835 648520.0 1971-02-11 16.1209 16.2285 16.097 16.1926 3.53399 579484.0 1971-02-12 16.1926 16.2285 16.1329 16.2285 3.54182 382836.0 1971-02-16 16.2285 16.4197 16.1926 16.3301 3.56399 684084.0 1971-02-17 16.2703 16.2703 16.0492 16.0851 3.51052 652704.0 1971-02-18 16.0851 16.1209 15.7385 15.7385 3.43488 822156.0 ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ 2024-06-07 168.18 171.31 168.06 170.01 170.01 3.4755e6 2024-06-10 169.55 170.76 168.88 170.38 170.38 3.4447e6 2024-06-11 169.98 170.0 166.81 169.32 169.32 2.9513e6 2024-06-12 171.35 172.47 168.1 169.0 169.0 3.5227e6 2024-06-13 169.01 169.59 168.34 169.12 169.12 3.5257e6 2024-06-14 168.29 169.47 167.23 169.21 169.21 2.7777e6 2024-06-17 168.76 169.72 167.5 169.5 169.5 3.2388e6 13441 rows omitted
Create TSFrame object for AAPL
Similarly, we can create a TSFrame object for the AAPL data.
julia> aapl_ts = TSFrame(MarketData.yahoo(:AAPL))10969×6 TSFrame with Date Index Index Open High Low Close AdjClose Volum ⋯ Date Float64 Float64 Float64 Float64 Float64 Float ⋯ ──────────────────────────────────────────────────────────────────────────────── 1980-12-12 0.128348 0.128906 0.128348 0.128348 0.099192 4.690 ⋯ 1980-12-15 0.12221 0.12221 0.121652 0.121652 0.094017 1.758 1980-12-16 0.113281 0.113281 0.112723 0.112723 0.087117 1.057 1980-12-17 0.115513 0.116071 0.115513 0.115513 0.089273 8.644 1980-12-18 0.118862 0.11942 0.118862 0.118862 0.091861 7.344 ⋯ 1980-12-19 0.126116 0.126674 0.126116 0.126116 0.097467 4.863 1980-12-22 0.132254 0.132813 0.132254 0.132254 0.102211 3.736 1980-12-23 0.137835 0.138393 0.137835 0.137835 0.106524 4.695 ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱ 2024-06-07 194.65 196.94 194.14 196.89 196.89 5.310 ⋯ 2024-06-10 196.9 197.3 192.15 193.12 193.12 9.726 2024-06-11 193.65 207.16 193.63 207.15 207.15 1.723 2024-06-12 207.37 220.2 206.9 213.07 213.07 1.981 2024-06-13 214.74 216.75 211.6 214.24 214.24 9.786 ⋯ 2024-06-14 213.85 215.17 211.3 212.49 212.49 7.012 2024-06-17 213.37 218.95 212.72 216.67 216.67 9.360 1 column and 10954 rows omitted
Create a 6-month subset of stock data
We would like to compare the stock returns for both the stocks for 6 months starting from June 1, 2021 till December 31, 2021. We use TSFrames.subset method to create new objects which contain the specified duration of data.
julia> date_from = Date(2021, 06, 01);julia> date_to = Date(2021, 12, 31);julia> ibm = TSFrames.subset(ibm_ts, date_from, date_to)150×6 TSFrame with Date Index Index Open High Low Close AdjClose Volume Date Float64 Float64 Float64 Float64 Float64 Float64 ───────────────────────────────────────────────────────────────────── 2021-06-01 138.623 139.417 137.428 137.849 119.776 2.5287e6 2021-06-02 138.26 139.34 137.772 139.312 121.047 2.9151e6 2021-06-03 138.537 139.465 137.706 139.149 120.906 4.32061e6 2021-06-04 139.579 141.061 139.35 140.937 122.46 3.26132e6 2021-06-07 141.061 142.199 140.698 141.511 122.958 3.62198e6 2021-06-08 141.606 143.595 141.606 142.514 123.83 5.31378e6 2021-06-09 142.476 144.426 142.275 144.044 125.159 5.54725e6 2021-06-10 144.809 146.119 143.174 143.92 125.051 4.97739e6 ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ 2021-12-22 129.06 129.79 127.6 129.75 115.559 3.9232e6 2021-12-23 130.0 130.96 129.52 130.63 116.343 3.649e6 2021-12-27 130.63 131.65 129.95 131.62 117.224 4.2939e6 2021-12-28 131.6 133.22 131.59 132.63 118.124 3.4452e6 2021-12-29 132.4 134.21 132.3 133.35 118.765 4.2399e6 2021-12-30 133.75 134.37 133.33 133.91 119.264 3.1581e6 2021-12-31 134.0 134.99 133.61 133.66 119.041 3.3621e6 135 rows omitted
julia> aapl = TSFrames.subset(aapl_ts, date_from, date_to)150×6 TSFrame with Date Index Index Open High Low Close AdjClose Volume Date Float64 Float64 Float64 Float64 Float64 Float64 ───────────────────────────────────────────────────────────────────── 2021-06-01 125.08 125.35 123.94 124.28 122.357 6.76371e7 2021-06-02 124.28 125.24 124.05 125.06 123.125 5.92789e7 2021-06-03 124.68 124.85 123.13 123.54 121.629 7.62292e7 2021-06-04 124.07 126.16 123.85 125.89 123.942 7.51693e7 2021-06-07 126.17 126.32 124.83 125.9 123.952 7.10576e7 2021-06-08 126.6 128.46 126.21 126.74 124.779 7.44038e7 2021-06-09 127.21 127.75 126.52 127.13 125.163 5.68779e7 2021-06-10 127.02 128.19 125.94 126.11 124.159 7.11864e7 ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ 2021-12-22 173.04 175.86 172.15 175.64 173.435 9.21353e7 2021-12-23 175.85 176.85 175.27 176.28 174.067 6.83566e7 2021-12-27 177.09 180.42 177.07 180.33 178.066 7.49196e7 2021-12-28 180.16 181.33 178.53 179.29 177.039 7.91443e7 2021-12-29 179.33 180.63 178.14 179.38 177.128 6.23489e7 2021-12-30 179.47 180.57 178.09 178.2 175.962 5.9773e7 2021-12-31 178.09 179.23 177.26 177.57 175.34 6.40623e7 135 rows omitted
Combine adjusted closing prices of both stocks into one object
We now join (cbind) both the stocks' data into a single object for further analysis. We use TSFrames.join to create two columns containing adjusted closing prices of both the stocks. The join happens by comparing the Index values (dates) of the two objects. The resulting object contains two columns with exactly the same dates for which both the objects have data, all the other rows are omitted from the result.
julia> ibm_aapl = TSFrames.join(ibm[:, ["AdjClose"]], aapl[:, ["AdjClose"]]; jointype=:JoinBoth)150×2 TSFrame with Date Index Index AdjClose AdjClose_1 Date Float64 Float64 ────────────────────────────────── 2021-06-01 119.776 122.357 2021-06-02 121.047 123.125 2021-06-03 120.906 121.629 2021-06-04 122.46 123.942 2021-06-07 122.958 123.952 2021-06-08 123.83 124.779 2021-06-09 125.159 125.163 2021-06-10 125.051 124.159 ⋮ ⋮ ⋮ 2021-12-22 115.559 173.435 2021-12-23 116.343 174.067 2021-12-27 117.224 178.066 2021-12-28 118.124 177.039 2021-12-29 118.765 177.128 2021-12-30 119.264 175.962 2021-12-31 119.041 175.34 135 rows omittedjulia> TSFrames.rename!(ibm_aapl, [:IBM, :AAPL])150×2 TSFrame with Date Index Index IBM AAPL Date Float64 Float64 ────────────────────────────── 2021-06-01 119.776 122.357 2021-06-02 121.047 123.125 2021-06-03 120.906 121.629 2021-06-04 122.46 123.942 2021-06-07 122.958 123.952 2021-06-08 123.83 124.779 2021-06-09 125.159 125.163 2021-06-10 125.051 124.159 ⋮ ⋮ ⋮ 2021-12-22 115.559 173.435 2021-12-23 116.343 174.067 2021-12-27 117.224 178.066 2021-12-28 118.124 177.039 2021-12-29 118.765 177.128 2021-12-30 119.264 175.962 2021-12-31 119.041 175.34 135 rows omitted
After the join operation the column names are modified because we merged two same-named columns (AdjClose) so we use TSFrames.rename!() method to rename the columns to easily remembered stock names.
Fill missing values
julia> ibm_aapl = ibm_aapl |> Impute.locf()150×2 TSFrame with Date Index Index IBM AAPL Date Float64 Float64 ────────────────────────────── 2021-06-01 119.776 122.357 2021-06-02 121.047 123.125 2021-06-03 120.906 121.629 2021-06-04 122.46 123.942 2021-06-07 122.958 123.952 2021-06-08 123.83 124.779 2021-06-09 125.159 125.163 2021-06-10 125.051 124.159 ⋮ ⋮ ⋮ 2021-12-22 115.559 173.435 2021-12-23 116.343 174.067 2021-12-27 117.224 178.066 2021-12-28 118.124 177.039 2021-12-29 118.765 177.128 2021-12-30 119.264 175.962 2021-12-31 119.041 175.34 135 rows omitted
Convert data into weekly frequency using last values
Here, we convert daily stock data into weekly frequency by taking the value with which the trading closed on the last day of the week as the week's price.
julia> ibm_aapl_weekly = to_weekly(ibm_aapl)31×2 TSFrame with Date Index Index IBM AAPL Date Float64 Float64 ────────────────────────────── 2021-06-04 122.46 123.942 2021-06-11 125.666 125.38 2021-06-18 118.888 128.442 2021-06-25 121.978 131.051 2021-07-02 116.312 137.795 2021-07-09 117.558 142.865 2021-07-16 115.382 144.125 2021-07-23 117.409 146.262 ⋮ ⋮ ⋮ 2021-11-19 103.357 158.534 2021-11-26 103.144 154.841 2021-12-03 105.842 159.808 2021-12-10 110.518 177.197 2021-12-17 113.466 168.991 2021-12-23 116.343 174.067 2021-12-31 119.041 175.34 16 rows omitted
Compute weekly returns using the familiar log and diff functions
julia> ibm_aapl_weekly_returns = diff(log.(ibm_aapl_weekly))31×2 TSFrame with Date Index Index IBM_log AAPL_log Date Float64? Float64? ──────────────────────────────────────────────── 2021-06-04 missing missing 2021-06-11 0.0258468 0.0115308 2021-06-18 -0.0554489 0.0241276 2021-06-25 0.0256601 0.0201089 2021-07-02 -0.0475583 0.0501811 2021-07-09 0.0106558 0.0361353 2021-07-16 -0.018687 0.00878208 2021-07-23 0.0174142 0.0147147 ⋮ ⋮ ⋮ 2021-11-19 -0.0247661 0.0680367 2021-11-26 -0.00207038 -0.0235704 2021-12-03 0.0258271 0.0315731 2021-12-10 0.0432291 0.103288 2021-12-17 0.0263248 -0.0474148 2021-12-23 0.0250372 0.0295919 2021-12-31 0.0229304 0.00729127 16 rows omittedjulia> TSFrames.rename!(ibm_aapl_weekly_returns, [:IBM, :AAPL])31×2 TSFrame with Date Index Index IBM AAPL Date Float64? Float64? ──────────────────────────────────────────────── 2021-06-04 missing missing 2021-06-11 0.0258468 0.0115308 2021-06-18 -0.0554489 0.0241276 2021-06-25 0.0256601 0.0201089 2021-07-02 -0.0475583 0.0501811 2021-07-09 0.0106558 0.0361353 2021-07-16 -0.018687 0.00878208 2021-07-23 0.0174142 0.0147147 ⋮ ⋮ ⋮ 2021-11-19 -0.0247661 0.0680367 2021-11-26 -0.00207038 -0.0235704 2021-12-03 0.0258271 0.0315731 2021-12-10 0.0432291 0.103288 2021-12-17 0.0263248 -0.0474148 2021-12-23 0.0250372 0.0295919 2021-12-31 0.0229304 0.00729127 16 rows omitted
Compute standard deviation of weekly returns
Computing standard deviation is done using the std function from Statistics package. The skipmissing is used to skip missing values which may have been generated while computing log returns or were already present in the data.
julia> ibm_std = std(skipmissing(ibm_aapl_weekly_returns[:, :IBM]))0.03407930812765529
julia> aapl_std = std(skipmissing(ibm_aapl_weekly_returns[:, :AAPL]))0.03058228629948157
Scatter plot of AAPL and IBM
Here, we use the Plots package to create a scatter plot with IBM weekly returns on the x-axis and Apple weekly returns on the y-axis.
plot(ibm_aapl_weekly_returns[:, :AAPL],
ibm_aapl_weekly_returns[:, :IBM],
seriestype = :scatter;
xlabel = "AAPL",
ylabel = "IBM",
legend = false)
Aggregation and rolling window operations
Here, we compute realised volatility of returns of both IBM and Apple stock weekly and bi-monthly. Then, we compute daily returns volatility on a rolling basis with a window size of 10.
julia> daily_returns = diff(log.(ibm_aapl))150×2 TSFrame with Date Index Index IBM_log AAPL_log Date Float64? Float64? ────────────────────────────────────────────────── 2021-06-01 missing missing 2021-06-02 0.0105552 0.00625663 2021-06-03 -0.00116727 -0.0122286 2021-06-04 0.0127658 0.0188434 2021-06-07 0.00406189 7.95903e-5 2021-06-08 0.00706863 0.00664967 2021-06-09 0.0106759 0.00307249 2021-06-10 -0.000863345 -0.00805557 ⋮ ⋮ ⋮ 2021-12-22 0.00602981 0.0152028 2021-12-23 0.00675944 0.00363707 2021-12-27 0.00754996 0.0227148 2021-12-28 0.00764446 -0.00578384 2021-12-29 0.00541388 0.000501838 2021-12-30 0.00419066 -0.00659991 2021-12-31 -0.0018686 -0.00354159 135 rows omittedjulia> rvol = apply(daily_returns, Week(1), std) # Compute the realised volatility31×2 TSFrame with Date Index Index IBM_log_std AAPL_log_std Date Float64? Float64? ──────────────────────────────────────────────── 2021-06-01 missing missing 2021-06-07 0.00423144 0.00685027 2021-06-14 0.00515147 0.0140529 2021-06-21 0.0135566 0.00849497 2021-06-28 0.022242 0.00681136 2021-07-06 0.00774681 0.0123476 2021-07-12 0.00554708 0.0145984 2021-07-19 0.00913883 0.020118 ⋮ ⋮ ⋮ 2021-11-15 0.00421513 0.0107342 2021-11-22 0.0052403 0.0175357 2021-11-29 0.0142163 0.0188585 2021-12-06 0.00430294 0.0142467 2021-12-13 0.0139501 0.0249443 2021-12-20 0.00719011 0.0122394 2021-12-27 0.00389367 0.0121977 16 rows omittedjulia> rvol = apply(daily_returns, Month(2), std) # Every two months4×2 TSFrame with Date Index Index IBM_log_std AAPL_log_std Date Float64? Float64? ─────────────────────────────────────────────── 2021-06-01 missing missing 2021-08-02 0.00863797 0.0124996 2021-10-01 0.0187056 0.0131257 2021-12-01 0.00792972 0.0184034julia> rollapply(daily_returns, std, 10) # Compute rolling vols141×2 TSFrame with Date Index Index rolling_IBM_log_std rolling_AAPL_log_std Date Float64? Float64? ─────────────────────────────────────────────────────── 2021-06-14 missing missing 2021-06-15 0.0070376 0.0115999 2021-06-16 0.00777311 0.0115779 2021-06-17 0.00939336 0.0103008 2021-06-18 0.00965328 0.0105066 2021-06-21 0.0128877 0.0109084 2021-06-22 0.0125776 0.0111714 2021-06-23 0.0121089 0.0114156 ⋮ ⋮ ⋮ 2021-12-22 0.0102034 0.0220221 2021-12-23 0.0102076 0.0220115 2021-12-27 0.0102131 0.0213968 2021-12-28 0.00798222 0.0202205 2021-12-29 0.00799233 0.0199266 2021-12-30 0.00678749 0.0179653 2021-12-31 0.00534795 0.0116642 126 rows omitted
Rolling regression with a window of 10
One of the common finance problems is to run a rolling window regression of firm returns over market returns. For doing this, we will use the lm() function from the GLM package. We will create a separate function regress() which would take in the data as an argument and use pre-defined strings to identify the returns columns, pass them to lm(), and return the results.
We start by downloading the S&P500 daily data from Yahoo Finance, then performing the same steps as above to come to a joined TSFrame object containing daily returns of S&P500 and IBM stock prices. Then, use rollapply() with bycolumn=false to tell rollapply() to pass in the entire TSFrame to the function in one go for each iteration within the window.
julia> sp500 = TSFrame(MarketData.yahoo("^GSPC"));julia> sp500_adjclose = TSFrames.subset(sp500, date_from, date_to)[:, ["AdjClose"]]150×1 TSFrame with Date Index Index AdjClose Date Float64 ────────────────────── 2021-06-01 4202.04 2021-06-02 4208.12 2021-06-03 4192.85 2021-06-04 4229.89 2021-06-07 4226.52 2021-06-08 4227.26 2021-06-09 4219.55 2021-06-10 4239.18 ⋮ ⋮ 2021-12-22 4696.56 2021-12-23 4725.79 2021-12-27 4791.19 2021-12-28 4786.35 2021-12-29 4793.06 2021-12-30 4778.73 2021-12-31 4766.18 135 rows omittedjulia> ibm_adjclose = ibm[:, [:Index, :AdjClose]]150×1 TSFrame with Date Index Index AdjClose Date Float64 ────────────────────── 2021-06-01 119.776 2021-06-02 121.047 2021-06-03 120.906 2021-06-04 122.46 2021-06-07 122.958 2021-06-08 123.83 2021-06-09 125.159 2021-06-10 125.051 ⋮ ⋮ 2021-12-22 115.559 2021-12-23 116.343 2021-12-27 117.224 2021-12-28 118.124 2021-12-29 118.765 2021-12-30 119.264 2021-12-31 119.041 135 rows omittedjulia> sp500_ibm = join(sp500_adjclose, ibm_adjclose, jointype=:JoinBoth)150×2 TSFrame with Date Index Index AdjClose AdjClose_1 Date Float64 Float64 ────────────────────────────────── 2021-06-01 4202.04 119.776 2021-06-02 4208.12 121.047 2021-06-03 4192.85 120.906 2021-06-04 4229.89 122.46 2021-06-07 4226.52 122.958 2021-06-08 4227.26 123.83 2021-06-09 4219.55 125.159 2021-06-10 4239.18 125.051 ⋮ ⋮ ⋮ 2021-12-22 4696.56 115.559 2021-12-23 4725.79 116.343 2021-12-27 4791.19 117.224 2021-12-28 4786.35 118.124 2021-12-29 4793.06 118.765 2021-12-30 4778.73 119.264 2021-12-31 4766.18 119.041 135 rows omittedjulia> sp500_ibm_returns = diff(log.(sp500_ibm))150×2 TSFrame with Date Index Index AdjClose_log AdjClose_1_log Date Float64? Float64? ────────────────────────────────────────────────── 2021-06-01 missing missing 2021-06-02 0.00144589 0.0105552 2021-06-03 -0.0036353 -0.00116727 2021-06-04 0.0087953 0.0127658 2021-06-07 -0.000797056 0.00406189 2021-06-08 0.00017501 0.00706863 2021-06-09 -0.00182553 0.0106759 2021-06-10 0.00464145 -0.000863345 ⋮ ⋮ ⋮ 2021-12-22 0.0101287 0.00602981 2021-12-23 0.00620441 0.00675944 2021-12-27 0.0137441 0.00754996 2021-12-28 -0.00101067 0.00764446 2021-12-29 0.00140091 0.00541388 2021-12-30 -0.00299423 0.00419066 2021-12-31 -0.00262963 -0.0018686 135 rows omittedjulia> TSFrames.rename!(sp500_ibm_returns, ["SP500", "IBM"]);julia> function regress(data) ll = lm(@formula(SP500 ~ IBM), data) co::Real = coef(ll)[coefnames(ll) .== "IBM"][1] sd::Real = Statistics.std(residuals(ll)) return (co, sd) endregress (generic function with 1 method)julia> rollapply(sp500_ibm_returns, regress, 10, bycolumn=false)141×1 TSFrame with Date Index Index rolling_regress Date Tuple… ───────────────────────────────────── 2021-06-14 (0.121115, 0.00357525) 2021-06-15 (0.162976, 0.00343796) 2021-06-16 (0.266415, 0.00359778) 2021-06-17 (0.191824, 0.0034874) 2021-06-18 (0.265324, 0.00419973) 2021-06-21 (0.407348, 0.00453721) 2021-06-22 (0.425805, 0.00470506) 2021-06-23 (0.494154, 0.00383348) ⋮ ⋮ 2021-12-22 (0.00412805, 0.0118974) 2021-12-23 (0.00391056, 0.0117623) 2021-12-27 (0.0408858, 0.0121557) 2021-12-28 (-0.312514, 0.0113447) 2021-12-29 (-0.271109, 0.0108816) 2021-12-30 (0.112463, 0.0102273) 2021-12-31 (0.968821, 0.00825401) 126 rows omitted