{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Load Packages" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "printyellow (generic function with 1 method)" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "using Printf, Dates, Statistics, CSV, DataFrames\n", "include(\"jlFiles/printmat.jl\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Loading Some Data with CSV.jl" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The first 4 lines of Data/Options_prices_US_Canada.csv:\n", "\n", "symbol,exchange,date,adjusted close,option symbol,expiration,strike,call/put,style,ask,bid,volume,open interest,unadjusted\n", "SPX,CBOE,03/30/17,2368.06,SPXW 170331C00300000,03/31/17,300,C,E,2073.9,2062.9,0,0,2368.927\n", "SPX,CBOE,03/30/17,2368.06,SPXW 170331P00300000,03/31/17,300,P,E,0.1,0,0,0,2368.927\n", "SPX,CBOE,03/30/17,2368.06,SPXW 170331C00400000,03/31/17,400,C,E,1974.1,1962.7,0,0,2368.927\n", "\n" ] } ], "source": [ "DataFile = \"Data/Options_prices_US_Canada.csv\"\n", "\n", "println(\"The first 4 lines of $(DataFile):\\n\")\n", "txt = readlines(DataFile)\n", "printmat(txt[1:4])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use `normalizenames` to get names that can be used in Julia as variables names and specify the `dateformat` used in the csv file (to convert to proper Julia dates). The dates in the file are given as `03/30/17` which CSV/DataFrames interpret as 30 March year 17 (AD). We add `Dates.Year(2000)` to get year 2017." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[1m13952×10 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m symbol \u001b[0m\u001b[1m date \u001b[0m\u001b[1m close \u001b[0m\u001b[1m expiration \u001b[0m\u001b[1m strike \u001b[0m\u001b[1m call_put \u001b[0m\u001b[1m ask \u001b[0m\u001b[1m\u001b[0m ⋯\n", "\u001b[1m \u001b[0m│\u001b[90m String3 \u001b[0m\u001b[90m Date \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Date \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String1 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m\u001b[0m ⋯\n", "───────┼────────────────────────────────────────────────────────────────────────\n", " 1 │ SPX 2017-03-30 2368.06 2017-03-31 300.0 C 2073.9 ⋯\n", " 2 │ SPX 2017-03-30 2368.06 2017-03-31 300.0 P 0.1\n", " 3 │ SPX 2017-03-30 2368.06 2017-03-31 400.0 C 1974.1\n", " 4 │ SPX 2017-03-30 2368.06 2017-03-31 400.0 P 0.05\n", " 5 │ SPX 2017-03-30 2368.06 2017-03-31 500.0 C 1874.1 ⋯\n", " 6 │ SPX 2017-03-30 2368.06 2017-03-31 500.0 P 0.05\n", " 7 │ SPX 2017-03-30 2368.06 2017-03-31 600.0 C 1774.1\n", " 8 │ SPX 2017-03-30 2368.06 2017-03-31 600.0 P 0.05\n", " 9 │ SPX 2017-03-30 2368.06 2017-03-31 700.0 C 1673.9 ⋯\n", " 10 │ SPX 2017-03-30 2368.06 2017-03-31 700.0 P 0.05\n", " 11 │ SPX 2017-03-30 2368.06 2017-03-31 750.0 C 1624.1\n", " ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱\n", " 13943 │ XIU 2017-03-30 23.06 2019-03-15 21.0 C 3.21\n", " 13944 │ XIU 2017-03-30 23.06 2019-03-15 21.0 P 1.71 ⋯\n", " 13945 │ XIU 2017-03-30 23.06 2019-03-15 22.0 C 2.53\n", " 13946 │ XIU 2017-03-30 23.06 2019-03-15 22.0 P 2.03\n", " 13947 │ XIU 2017-03-30 23.06 2019-03-15 23.0 C 1.97\n", " 13948 │ XIU 2017-03-30 23.06 2019-03-15 23.0 P 2.51 ⋯\n", " 13949 │ XIU 2017-03-30 23.06 2019-03-15 24.0 C 1.5\n", " 13950 │ XIU 2017-03-30 23.06 2019-03-15 24.0 P 3.12\n", " 13951 │ XIU 2017-03-30 23.06 2019-03-15 25.0 C 1.13\n", " 13952 │ XIU 2017-03-30 23.06 2019-03-15 25.0 P 3.64 ⋯\n", "\u001b[36m 3 columns and 13931 rows omitted\u001b[0m" ] } ], "source": [ "df1 = CSV.read(DataFile,DataFrame,normalizenames=true,dateformat=\"mm/dd/yy\")\n", "\n", "df1.date .+= Dates.Year(2000) #03/30/17 to 03/30/2017\n", "df1.expiration .+= Dates.Year(2000)\n", "\n", "select!(df1,Not([:exchange,:option_symbol,:style,:unadjusted])) #deleting some columns\n", "rename!(df1,:adjusted_close => :close) #renaming a column\n", "\n", "show(df1)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Task 1\n", "\n", "Create a new DataFrame that contains only the data for SPX and those option contracts that were traded (volume > 0). Hint: `df1[vv, :]` picks out the rows of the data frame for which `vv` is `true`. " ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\u001b[1m2359×10 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m symbol \u001b[0m\u001b[1m date \u001b[0m\u001b[1m close \u001b[0m\u001b[1m expiration \u001b[0m\u001b[1m strike \u001b[0m\u001b[1m call_put \u001b[0m\u001b[1m ask \u001b[0m\u001b[1m \u001b[0m ⋯\n", "\u001b[1m \u001b[0m│\u001b[90m String3 \u001b[0m\u001b[90m Date \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m Date \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m String1 \u001b[0m\u001b[90m Float64 \u001b[0m\u001b[90m \u001b[0m ⋯\n", "──────┼─────────────────────────────────────────────────────────────────────────\n", " 1 │ SPX 2017-03-30 2368.06 2017-03-31 1600.0 C 774.0 ⋯\n", " 2 │ SPX 2017-03-30 2368.06 2017-03-31 1600.0 P 0.05\n", " 3 │ SPX 2017-03-30 2368.06 2017-03-31 2040.0 P 0.05\n", " 4 │ SPX 2017-03-30 2368.06 2017-03-31 2050.0 P 0.05\n", " 5 │ SPX 2017-03-30 2368.06 2017-03-31 2100.0 P 0.05 ⋯\n", " 6 │ SPX 2017-03-30 2368.06 2017-03-31 2110.0 C 264.5\n", " 7 │ SPX 2017-03-30 2368.06 2017-03-31 2120.0 P 0.05\n", " 8 │ SPX 2017-03-30 2368.06 2017-03-31 2150.0 P 0.05\n", " 9 │ SPX 2017-03-30 2368.06 2017-03-31 2175.0 C 199.5 ⋯\n", " 10 │ SPX 2017-03-30 2368.06 2017-03-31 2175.0 P 0.05\n", " 11 │ SPX 2017-03-30 2368.06 2017-03-31 2180.0 C 194.5\n", " ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋮ ⋱\n", " 2350 │ SPX 2017-03-30 2368.06 2019-12-20 1900.0 P 111.4\n", " 2351 │ SPX 2017-03-30 2368.06 2019-12-20 2000.0 P 136.0 ⋯\n", " 2352 │ SPX 2017-03-30 2368.06 2019-12-20 2200.0 C 345.7\n", " 2353 │ SPX 2017-03-30 2368.06 2019-12-20 2300.0 C 287.0\n", " 2354 │ SPX 2017-03-30 2368.06 2019-12-20 2350.0 C 259.7\n", " 2355 │ SPX 2017-03-30 2368.06 2019-12-20 2375.0 P 264.3 ⋯\n", " 2356 │ SPX 2017-03-30 2368.06 2019-12-20 2400.0 C 230.7\n", " 2357 │ SPX 2017-03-30 2368.06 2019-12-20 2400.0 P 275.3\n", " 2358 │ SPX 2017-03-30 2368.06 2019-12-20 2800.0 C 74.9\n", " 2359 │ SPX 2017-03-30 2368.06 2019-12-20 3000.0 C 37.5 ⋯\n", "\u001b[36m 3 columns and 2338 rows omitted\u001b[0m" ] } ], "source": [ "vv = (df1.symbol .== \"SPX\") .& (df1.volume .> 0) #rows with SPX, and trade\n", "\n", "df2 = df1[vv, :] #create new df, only some rows\n", "\n", "show(df2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Task 2\n", "\n", "Create a *group* for each expiration date. These groups can be referred to as `dataG2[key]`.\n", "\n", "Hints: `groupby()`" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "dataG2 = groupby(df2,:expiration); #grouped by expiration date" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Task 3\n", "\n", "Print the number of contracts (`nrow`) and the sum of the open interest `:open_interest=>sum` for each of the expiration dates.\n", "\n", "Hint: `combine()`" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "

34 rows × 3 columns

expirationnrowopen_interest_sum
DateInt64Int64
12017-03-311001010183
22017-04-0394109345
32017-04-0584102022
42017-04-07140467105
52017-04-107351755
62017-04-126756228
72017-04-13109303944
82017-04-177040855
92017-04-195725690
102017-04-211661775684
112017-04-246915085
122017-04-26566560
132017-04-28124467161
142017-05-05129169530
152017-05-12121225600
162017-05-19158937452
172017-05-269512914
182017-05-31105120944
192017-06-02340
202017-06-161261956193
212017-06-3072136661
222017-07-213951058
232017-07-312756511
242017-08-311415578
252017-09-1545524556
262017-09-291745842
272017-12-1546759858
282017-12-292235398
292018-01-191878967
302018-03-161615051
⋮⋮⋮⋮
" ], "text/latex": [ "\\begin{tabular}{r|ccc}\n", "\t& expiration & nrow & open\\_interest\\_sum\\\\\n", "\t\\hline\n", "\t& Date & Int64 & Int64\\\\\n", "\t\\hline\n", "\t1 & 2017-03-31 & 100 & 1010183 \\\\\n", "\t2 & 2017-04-03 & 94 & 109345 \\\\\n", "\t3 & 2017-04-05 & 84 & 102022 \\\\\n", "\t4 & 2017-04-07 & 140 & 467105 \\\\\n", "\t5 & 2017-04-10 & 73 & 51755 \\\\\n", "\t6 & 2017-04-12 & 67 & 56228 \\\\\n", "\t7 & 2017-04-13 & 109 & 303944 \\\\\n", "\t8 & 2017-04-17 & 70 & 40855 \\\\\n", "\t9 & 2017-04-19 & 57 & 25690 \\\\\n", "\t10 & 2017-04-21 & 166 & 1775684 \\\\\n", "\t11 & 2017-04-24 & 69 & 15085 \\\\\n", "\t12 & 2017-04-26 & 56 & 6560 \\\\\n", "\t13 & 2017-04-28 & 124 & 467161 \\\\\n", "\t14 & 2017-05-05 & 129 & 169530 \\\\\n", "\t15 & 2017-05-12 & 121 & 225600 \\\\\n", "\t16 & 2017-05-19 & 158 & 937452 \\\\\n", "\t17 & 2017-05-26 & 95 & 12914 \\\\\n", "\t18 & 2017-05-31 & 105 & 120944 \\\\\n", "\t19 & 2017-06-02 & 34 & 0 \\\\\n", "\t20 & 2017-06-16 & 126 & 1956193 \\\\\n", "\t21 & 2017-06-30 & 72 & 136661 \\\\\n", "\t22 & 2017-07-21 & 39 & 51058 \\\\\n", "\t23 & 2017-07-31 & 27 & 56511 \\\\\n", "\t24 & 2017-08-31 & 14 & 15578 \\\\\n", "\t25 & 2017-09-15 & 45 & 524556 \\\\\n", "\t26 & 2017-09-29 & 17 & 45842 \\\\\n", "\t27 & 2017-12-15 & 46 & 759858 \\\\\n", "\t28 & 2017-12-29 & 22 & 35398 \\\\\n", "\t29 & 2018-01-19 & 18 & 78967 \\\\\n", "\t30 & 2018-03-16 & 16 & 15051 \\\\\n", "\t$\\dots$ & $\\dots$ & $\\dots$ & $\\dots$ \\\\\n", "\\end{tabular}\n" ], "text/plain": [ "\u001b[1m34×3 DataFrame\u001b[0m\n", "\u001b[1m Row \u001b[0m│\u001b[1m expiration \u001b[0m\u001b[1m nrow \u001b[0m\u001b[1m open_interest_sum \u001b[0m\n", "\u001b[1m \u001b[0m│\u001b[90m Date \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n", "─────┼──────────────────────────────────────\n", " 1 │ 2017-03-31 100 1010183\n", " 2 │ 2017-04-03 94 109345\n", " 3 │ 2017-04-05 84 102022\n", " 4 │ 2017-04-07 140 467105\n", " 5 │ 2017-04-10 73 51755\n", " 6 │ 2017-04-12 67 56228\n", " 7 │ 2017-04-13 109 303944\n", " 8 │ 2017-04-17 70 40855\n", " 9 │ 2017-04-19 57 25690\n", " 10 │ 2017-04-21 166 1775684\n", " 11 │ 2017-04-24 69 15085\n", " ⋮ │ ⋮ ⋮ ⋮\n", " 25 │ 2017-09-15 45 524556\n", " 26 │ 2017-09-29 17 45842\n", " 27 │ 2017-12-15 46 759858\n", " 28 │ 2017-12-29 22 35398\n", " 29 │ 2018-01-19 18 78967\n", " 30 │ 2018-03-16 16 15051\n", " 31 │ 2018-03-29 9 153\n", " 32 │ 2018-06-15 17 69132\n", " 33 │ 2018-12-21 27 179774\n", " 34 │ 2019-12-20 13 9213\n", "\u001b[36m 13 rows omitted\u001b[0m" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "combine(dataG2,nrow,:open_interest=>sum) #same information as above, uncomment to se" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Task 4 \n", "Creating two new DataFrames: for expiration date 2017-04-21 and another for 2017-06-16.\n", "\n", "Hint: `dataG2[(expiration = Date(\"2017-04-21\"),)]`" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n" ] } ], "source": [ "#creating two new DataFrames: for two different expiration days\n", "\n", "df_20170421 = dataG2[(expiration = Date(\"2017-04-21\"),)]\n", "df_20170616 = dataG2[(expiration = Date(\"2017-06-16\"),)]\n", "\n", "println()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Task 5\n", "\n", "For the expiration date 2017-04-21, calculate the mid price as the average of the `.ask` and `.bid`. \n", "\n", "Plot the mid price as a function of the strike price `.strike` for put options. Add a curve another curve for the call options." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "using Plots #this loads the Plots package\n", "\n", "#pyplot(size=(600,400)) #choice of plotting backend\n", "gr(size=(480,320))\n", "default(fmt = :svg) " ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "image/svg+xml": [ "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "vv_P = df_20170421.call_put .== \"P\" #put options\n", "vv_C = df_20170421.call_put .== \"C\" #call options\n", "\n", "midPrice = (df_20170421.ask + df_20170421.bid)/2 #creates a traditional vector\n", "\n", "p1 = plot( df_20170421.strike[vv_P],midPrice[vv_P],label=\"puts\",\n", " linecolor = :red,\n", " linestyle = :solid,\n", " linewidth = 1,\n", " title = \"Option prices $(df_20170421.date[1])\",\n", " xlabel = \"strike prices\" )\n", "plot!( df_20170421.strike[vv_C],midPrice[vv_C],label=\"calls\",\n", " linecolor = :blue,\n", " linestyle = :dot,\n", " linewidth = 2 )\n", "vline!([df_20170421.close[1]],linecolor=:black,line=(:dash,2),label=\"SPX level\")\n", "display(p1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "@webio": { "lastCommId": null, "lastKernelId": null }, "anaconda-cloud": {}, "kernelspec": { "display_name": "Julia 1.7.0", "language": "julia", "name": "julia-1.7" }, "language_info": { "file_extension": ".jl", "mimetype": "application/julia", "name": "julia", "version": "1.7.0" }, "nteract": { "version": "0.23.1" } }, "nbformat": 4, "nbformat_minor": 4 }