1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
import logging
from dataclasses import dataclass
from typing import Optional, List
from market import Market
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
ARBITRAGE_CANDIDATE_LIMIT = 30_000
ARBITRAGE_TYPE_LIMIT = 250
ARBITRAGE_STATION_PAIR_LIMIT = 40
@dataclass(frozen=True)
class ShortlistConfig:
candidate_limit: int = ARBITRAGE_CANDIDATE_LIMIT
type_limit: int = ARBITRAGE_TYPE_LIMIT
station_pair_limit: int = ARBITRAGE_STATION_PAIR_LIMIT
rank_mode: str = "and"
@dataclass
class Trade:
from_station: int
to_station: int
from_order_id: int
to_order_id: int
item_name: str
type_id: int
item_cargo_volume: float
from_price: float
to_price: float
quantity: int
cargo: float
gross_profit: float
net_profit: Optional[float] = None
async def arbitrage(
market: Market,
cargo_space: int,
tax_rate: float,
profit_threshold: float,
max_capital: float,
shortlist: Optional[ShortlistConfig] = None,
) -> List[Trade]:
if not market.db_conn:
return []
shortlist = shortlist or ShortlistConfig()
if shortlist.rank_mode not in {"and", "or"}:
raise ValueError(f"Unsupported shortlist rank mode: {shortlist.rank_mode}")
rank_operator = "AND" if shortlist.rank_mode == "and" else "OR"
query = f"""
WITH buy_orders AS (
SELECT * FROM orders WHERE is_buy_order = 1
),
sell_orders AS (
SELECT * FROM orders WHERE is_buy_order = 0
),
raw_trades AS (
SELECT
s.location_id as from_station,
b.location_id as to_station,
s.order_id as from_order_id,
b.order_id as to_order_id,
s.item_name,
s.type_id,
s.item_cargo_volume,
s.price as from_price,
b.price as to_price,
MIN(
s.volume_remain,
b.volume_remain,
CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
) as quantity
FROM sell_orders s
JOIN buy_orders b ON s.type_id = b.type_id
WHERE
b.price * (1 - {tax_rate}) >= s.price
AND s.item_cargo_volume > 0
AND s.price > 0 AND b.price > 0
),
candidate_trades AS (
SELECT
*,
quantity * (to_price * (1 - {tax_rate}) - from_price) as gross_profit,
quantity * item_cargo_volume as cargo,
(quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * item_cargo_volume, 0) as profit_density,
(quantity * (to_price * (1 - {tax_rate}) - from_price)) / NULLIF(quantity * from_price, 0) as roi
FROM raw_trades
WHERE quantity > 0 AND quantity * (to_price * (1 - {tax_rate}) - from_price) >= {profit_threshold}
),
ranked_trades AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY type_id
ORDER BY gross_profit DESC, roi DESC, profit_density DESC
) as type_rank,
ROW_NUMBER() OVER (
PARTITION BY from_station, to_station
ORDER BY gross_profit DESC, roi DESC, profit_density DESC
) as station_pair_rank
FROM candidate_trades
)
SELECT
from_station,
to_station,
from_order_id,
to_order_id,
item_name,
type_id,
item_cargo_volume,
from_price,
to_price,
quantity,
cargo,
gross_profit
FROM ranked_trades
WHERE (type_rank <= {shortlist.type_limit}
{rank_operator} station_pair_rank <= {shortlist.station_pair_limit})
ORDER BY gross_profit DESC, roi DESC, profit_density DESC
LIMIT {shortlist.candidate_limit}
"""
trades = []
async with market.db_conn.execute(query) as cursor:
async for row in cursor:
from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity, cargo_volume, gross_profit = row
trades.append(Trade(
from_station=from_station,
to_station=to_station,
from_order_id=from_order_id,
to_order_id=to_order_id,
item_name=item_name,
type_id=type_id,
item_cargo_volume=item_cargo_volume,
from_price=from_price,
to_price=to_price,
quantity=quantity,
cargo=cargo_volume,
gross_profit=gross_profit
))
logger.info(
"Generated %d arbitrage candidates after shortlist (limit=%d, type_limit=%d, station_pair_limit=%d, rank_mode=%s)",
len(trades),
shortlist.candidate_limit,
shortlist.type_limit,
shortlist.station_pair_limit,
shortlist.rank_mode,
)
return trades
async def snipe(market: Market, cargo_space: int, current_region: int, tax_rate: float, max_capital: float, snipe_profit_threshold: float) -> Optional[Trade]:
if not market.db_conn:
return None
query = f"""
WITH buy_orders AS (
SELECT * FROM orders WHERE is_buy_order = 1
),
sell_orders AS (
SELECT * FROM orders WHERE is_buy_order = 0 AND region_id = {current_region}
)
SELECT
s.location_id as from_station,
b.location_id as to_station,
s.order_id as from_order_id,
b.order_id as to_order_id,
s.item_name,
s.type_id,
s.item_cargo_volume,
s.price as from_price,
b.price as to_price,
MIN(
s.volume_remain,
b.volume_remain,
CAST({cargo_space} / NULLIF(s.item_cargo_volume, 0) AS INTEGER),
CAST({max_capital} / NULLIF(s.price, 0) AS INTEGER),
CAST({max_capital} / NULLIF(b.price, 0) AS INTEGER)
) as quantity
FROM sell_orders s
JOIN buy_orders b ON s.type_id = b.type_id
WHERE
b.price * (1 - {tax_rate}) >= s.price
AND s.item_cargo_volume > 0
AND s.price > 0 AND b.price > 0
"""
best_trade = None
max_profit = 0
async with market.db_conn.execute(query) as cursor:
async for row in cursor:
from_station, to_station, from_order_id, to_order_id, item_name, type_id, item_cargo_volume, from_price, to_price, quantity = row
if quantity is None or quantity <= 0:
continue
effective_buy_price = to_price * (1 - tax_rate)
gross_profit = quantity * (effective_buy_price - from_price)
if gross_profit > max_profit:
cargo_volume = quantity * item_cargo_volume
best_trade = Trade(
from_station=from_station,
to_station=to_station,
from_order_id=from_order_id,
to_order_id=to_order_id,
item_name=item_name,
type_id=type_id,
item_cargo_volume=item_cargo_volume,
from_price=from_price,
to_price=to_price,
quantity=quantity,
cargo=cargo_volume,
gross_profit=gross_profit
)
max_profit = gross_profit
if max_profit > snipe_profit_threshold and best_trade:
return best_trade
return None
|
