The option_transactions table shows options trades executed on the thinkorswim trading platform. Query the table to calculate the profit/loss made on each trade.
| order_id | option_quote |
|---|---|
| 1339553203 | TOSWeb BOT +1 RBLX 100 (Weeklys) 13 AUG 21 85 CALL @.82 |
| 1339551295 | TOSWeb BOT +1 RBLX 100 (Weeklys) 13 AUG 21 85 CALL @1.00 |
| 1339551281 | TOSWeb SOLD -2 RBLX 100 (Weeklys) 13 AUG 21 85 CALL @2.10 |
| 1339551278 | TOSWeb BOT +1 TSLA 100 (Weeklys) 13 AUG 21 750 CALL @5.50 |
| 1339551271 | TOSWeb SOLD -1 TSLA 100 (Weeklys) 13 AUG 21 750 CALL @7.50 |
| 1339551264 | TOSWeb BOT +5 QS 100 (Weeklys) 13 AUG 21 24 PUT @.50 |
| 1339551235 | TOSWeb SOLD -5 QS 100 (Weeklys) 13 AUG 21 24 PUT @.25 |
| 1339551224 | TOSWeb BOT +2 AAPL 100 (Weeklys) 13 AUG 21 150 CALL @2.00 |
| 1339551219 | TOSWeb SOLD -1 AAPL 100 (Weeklys) 13 AUG 21 150 CALL @2.40 |
| 1339139768 | TOSWeb SOLD -1 AAPL 100 (Weeklys) 13 AUG 21 150 CALL @2.60 |
The anatomy of an options quote can be found here while this video provides additional information on how to read an options quote.
Before getting into the query let’s explain TOSWeb BOT +1 RBLX 100 (Weeklys) 13 AUG 21 85 CALL @.82 to ensure that it’s understandable:
TOSWeb - The thinkorsiwm platform. This can be ignoredBOT - The type of transaction. Can be either “BOT” (contract was bought) or “SOLD” (contract was sold)+1 - The number of contracts bought or soldRBLX - The underlying stock symbol100 (Weeklys) - Can be ignored as it does not provide any needed information13 AUG 21 - The date that the contract expires85 - The strike price of the stockCALL - The type of option. Can be either “CALL” or “PUT”@.82 - The per contract cost of the optionThe first two rows in the option_transactions table shows that 2 RBLX calls with a strike price of $85 and an expiration of 08/13 were bought for a total price of $1.82. The third row shows that both contracts were sold for a price of $2.10 per contract. Thus, the total profit made on this trade was $2.38
with clean_options as (
select
stock,
transaction_type,
contract,
sum(num_contracts * price) as total_price,
from (
select
option_quote,
trim(regexp_extract(option_quote, r"(.*? ){2}")) as transaction_type,
cast(regexp_extract(option_quote, r"\d+") as numeric) as num_contracts,
regexp_extract(option_quote, r"(.*? ){4}") as stock,
regexp_extract(option_quote, r"\(Weeklys\)?(.*)@") as contract,
cast(regexp_extract(option_quote, r"\@(.*)$") as numeric) as price
from option_transactions)
group by
stock,
transaction_type,
contract
),
buy_to_open as (
select
stock,
contract,
total_price as buy_price
from clean_options
where transaction_type = 'BOT'
),
sell_to_close as (
select
stock,
contract,
total_price as sell_price
from clean_options
where transaction_type = 'SOLD'
)
select
buy_to_open.stock,
buy_to_open.contract,
buy_to_open.buy_price,
sell_to_close.sell_price,
(sell_to_close.sell_price - buy_to_open.buy_price) as profit_loss
from
buy_to_open
inner join sell_to_close on
buy_to_open.stock = sell_to_close.stock and
buy_to_open.contract = sell_to_close.contract
order by profit_loss desc| stock | contract | buy_price | sell_price | profit_loss |
|---|---|---|---|---|
| RBLX | 13 AUG 21 85 CALL | 1.82 | 4.20 | 2.38 |
| TSLA | 13 AUG 21 750 CALL | 5.50 | 7.50 | 2.00 |
| AAPL | 13 AUG 21 150 CALL | 4.00 | 5.00 | 1.00 |
| QS | 13 AUG 21 24 PUT | 2.50 | 1.25 | -1.25 |
clean_options <- option_transactions %>%
mutate(
transaction_type = word(option_quote, 2),
num_contracts = abs(parse_number((word(option_quote, 3)))),
stock = word(option_quote, 4),
contract = word(option_quote, 7, 11),
price = parse_number(word(option_quote, -1)),
total_price = num_contracts * price
) %>%
group_by(stock, transaction_type, contract) %>%
summarise(total_price = sum(total_price)) %>%
ungroup()
buy_to_open = clean_options %>%
filter(transaction_type == 'BOT') %>%
select(stock, contract, buy_price = total_price)
sell_to_close = clean_options %>%
filter(transaction_type == 'SOLD') %>%
select(stock, contract, sell_price = total_price)
profit_loss <- buy_to_open %>%
inner_join(sell_to_close, by = c("stock", "contract")) %>%
mutate(profit_loss = sell_price - buy_price) %>%
arrange(desc(profit_loss))| stock | contract | buy_price | sell_price | profit_loss |
|---|---|---|---|---|
| RBLX | 13 AUG 21 85 CALL | 1.82 | 4.20 | 2.38 |
| TSLA | 13 AUG 21 750 CALL | 5.50 | 7.50 | 2.00 |
| AAPL | 13 AUG 21 150 CALL | 4.00 | 5.00 | 1.00 |
| QS | 13 AUG 21 24 PUT | 2.50 | 1.25 | -1.25 |
option_transactions = r.option_transactions
clean_options = (
option_transactions
.assign(
option_quote = lambda x: x["option_quote"].str.split(" "),
transaction_type = lambda x: x["option_quote"].str[1],
num_contracts = lambda x: x["option_quote"].str[2].apply(int).abs(),
stock = lambda x: x["option_quote"].str[3],
contract = lambda x: x["option_quote"].str[6:10].str.join(" "),
price = lambda x: x["option_quote"].str[-1].str.replace("@", "").apply(float),
total_price = lambda x: x["num_contracts"] * x["price"]
)
.groupby(['stock','transaction_type','contract'])['total_price']
.sum()
.reset_index()
)
buy_to_open = (
clean_options[clean_options['transaction_type'] == 'BOT']
[['stock', 'contract', 'total_price']]
.rename({"total_price": "buy_price"}, axis='columns')
)
sell_to_close = (
clean_options[clean_options['transaction_type'] == 'SOLD']
[['stock', 'contract', 'total_price']]
.rename({"total_price": "sell_price"}, axis = 'columns')
)
profit_loss = (
buy_to_open
.merge(sell_to_close, how='inner', left_on=['stock', 'contract'], right_on=['stock', 'contract'])
.assign(profit_loss = lambda x: x["sell_price"] - x["buy_price"])
.sort_values(by='profit_loss', ascending=False)
)| stock | contract | buy_price | sell_price | profit_loss | |
|---|---|---|---|---|---|
| 2 | RBLX | 13 AUG 21 85 | 1.82 | 4.20 | 2.38 |
| 3 | TSLA | 13 AUG 21 750 | 5.50 | 7.50 | 2.00 |
| 0 | AAPL | 13 AUG 21 150 | 4.00 | 5.00 | 1.00 |
| 1 | QS | 13 AUG 21 24 | 2.50 | 1.25 | -1.25 |