You will need to buy first and sell later. You can’t sell without buying. Also only one buy and one sell is permitted. You need to provide a formula to list Buy day and Sell day to maximize profit by buying first and selling later. In case of a tie, gap between Buy and Sell days should be minimum. For example – Value of 7 is for Day 4 and Day 6 both and sell is for Day 8 to maximize profit. But we will choose Day 6 and Day 8 to minimize the gap between Buy and Sell Day.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 287
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Maximize Buy-Sell Profit Days with Power Query
Power Query solution 1 for Maximize Buy-Sell Profit Days, proposed by Bo Rydobon 🇹🇭:
let
z = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]),
Ans = Table.FromColumns(
{
{"Buy", "Sell"},
List.Skip(
List.Last(
List.Sort(
List.Transform(
{1 .. List.Count(z) - 1},
(s) =>
List.Last(
List.Sort(
List.Transform({0 .. s - 1}, (b) => {z{b}{1} - z{s}{1}, z{b}{0}, z{s}{0}}),
each _{0}
)
)
),
each _{0}
)
)
)
}
)
in
AnsPower Query solution 2 for Maximize Buy-Sell Profit Days, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S =
let
l = Source[Price],
f = (s, p1, p2) =>
let
dp = l{p1} - l{p2},
ds = l{s{0}} - l{s{1}}
in
if s = {} or dp > ds or (dp = ds and p2 - p1 < s{1} - s{0}) then {p1, p2} else s,
c = {"Buy", "Sell"},
p = List.Accumulate(
{0 .. List.Count(l) - 2},
{},
(ss, cc) =>
let
r = List.Accumulate({cc + 1 .. List.Count(l) - 1}, {}, (s, c) => f(s, cc, c))
in
f(ss, r{0}, r{1})
)
in
Table.FromRows(
List.Transform(List.Positions(p), each {c{_}, Source[Day]{p{_}}}),
{"Answer", "Expected"}
)
in
SPower Query solution 3 for Maximize Buy-Sell Profit Days, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Count = Table.RowCount(Source) - 1,
Generate = List.TransformMany(
{0 .. Count},
(x) => {x + 1 .. Count},
(x, y) => [
B = Source[Price]{x},
S = Source[Price]{y},
D1 = B - S,
D2 = y - x + 1,
Buy = Source[Day]{x},
Sell = Source[Day]{y}
]
),
Max = List.Max(Generate, null, each [D1])[D1],
Return = List.Min(List.Select(Generate, each [D1] = Max), null, each [D2])[[Buy], [Sell]]
in
ReturnPower Query solution 4 for Maximize Buy-Sell Profit Days, proposed by Luke Jarych:
let
sourceTable = Table1,
numDays = Table.RowCount(sourceTable),
buyDay = List.Min(sourceTable[Price])-1,
sellDayOptions = List.Numbers(buyDay + 1, numDays - buyDay - 1), // Generate the records of expected data
pairsForBuyDay = List.Transform(
sellDayOptions,
each [BuyDay = sourceTable{buyDay}[Day], SellDay = _ , SellDayName = sourceTable{SellDay}[Day] , Profit = sourceTable{SellDay}[Price] - sourceTable{buyDay}[Price],
PriceBuy = sourceTable{buyDay}[Price], PriceSell = sourceTable{SellDay}[Price]]
),
transformedTable = Table.FromRecords(pairsForBuyDay),
#"Filtered Rows" = Table.SelectRows(transformedTable, each ([Profit] = 14)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"SellDay"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"SellDayName", "SellDay"}})
in
#"Renamed Columns"
In power Query to have the same result as yours in R ;-)
Assumption is that i am buying in day with the lowest price.
Solving the challenge of Maximize Buy-Sell Profit Days with Excel
Excel solution 1 for Maximize Buy-Sell Profit Days, proposed by Bo Rydobon 🇹🇭:
=LET(z,
B2:B11,
d,
A2:A11,
r,
ROW(
z
),
t,
TOROW(
r
),
c,
LAMBDA(
x,
TOROW(
IF(
rExcel solution 2 for Maximize Buy-Sell Profit Days, proposed by Bo Rydobon 🇹🇭:
=LET(z,
B2:B11,
d,
A2:A11,
r,
ROW(
z
),
t,
TOROW(
r
),
c,
LAMBDA(
x,
TOROW(
IF(
rExcel solution 3 for Maximize Buy-Sell Profit Days, proposed by John V.:
=LET(d,
A2:A11,
p,
B2:B11,
s,
ROW(
p
),
t,
TOROW(
s
),
v,
IF(sExcel solution 4 for Maximize Buy-Sell Profit Days, proposed by محمد حلمي:
=B2:B11-TOROW(
B2:B11
)
=--(SEQUENCE(
,
10
)>SEQUENCE(
10
))
3 - 1*2
=D2#*O2#
=D15#*SEQUENCE(
10
)
=LET(a,
A2:A11,
b,
B2:B11,
r,
ROW(
b
),
i,
r*(b-TOROW(
b
))*(TOROW(
r
)>r),
HSTACK(
{"Buy";"Sell"},
TEXTSPLIT(
@TOCOL(
IFS(
MAX(
i
)=i,
a&"-"&TOROW(
a
)
),
2
),
,
"-"
)
))Excel solution 5 for Maximize Buy-Sell Profit Days, proposed by محمد حلمي:
=LET(
a,
A2:A11,
b,
B2:B11,
r,
ROW(
b
),
i,
r*(b-TOROW(
b
))*(TOROW(
r
)>r),
HSTACK(
{"Buy";"Sell"},
TEXTSPLIT(
@TOCOL(
IFS(
MAX(
i
)=i,
a&"-"&TOROW(
a
)
),
2
),
,
"-"
)
))Excel solution 6 for Maximize Buy-Sell Profit Days, proposed by Kris Jaganah:
=LET(a,
A2:A11,
b,
B2:B11,
c,
--TEXTAFTER(
a,
"Day"
),
d,
TOROW(
c
),
e,
IF(
b0)*d),
i,
IF(
h-g=0,
"",
h-g
),
HSTACK(
{"Buy";"Sell"},
FILTER(
VSTACK(
"Day"&g,
"Day"&h
),
i=MIN(
i
)
)
)) Excel solution 7 for Maximize Buy-Sell Profit Days, proposed by Timothée BLIOT:
=LET(
A,
MAKEARRAY(
10,
10,
LAMBDA(
x,
y,
IF(
y>x,
INDEX(
B2:B11,
x
)-INDEX(
B2:B11,
y
),
0
)
)
),
"Day"&VSTACK(
MAX(
IF(
A=MAX(
A
),
SEQUENCE(
10
)
)
),
MIN(
IF(
A=MAX(
A
),
SEQUENCE(
,
10
)
)
)
)
)Excel solution 8 for Maximize Buy-Sell Profit Days, proposed by Oscar Mendez Roca Farell:
=LET(_d,
B2:B11,
_f,
ROW(
1:10
),
_c,
TOROW(
_f
),
_m,
N(
_f<_c
)*(_d-TOROW(
_d
)),
_e,
TOCOL((_c+100*_f)/(_m=MAX(
_m
)),
2),
_r,
MOD(
_e,
100
),
_i,
INT(
_e/100
),
_t,
TAKE(
SORTBY(
HSTACK(
_i,
_r
),
_r-_i
),
1
),
HSTACK(
{"Buy";"Sell"},
"Day"&TOCOL(
_t
)
))Excel solution 9 for Maximize Buy-Sell Profit Days, proposed by Oscar Mendez Roca Farell:
=LET(_d,
B2:B11,
_f,
ROW(
1:10
),
_c,
TOROW(
_f
),
_m,
N(
_f<_c
)*(_d-TOROW(
_d
)),
_e,
TOCOL(_f&"00"&_c/(_m=MAX(
_m
)),
2),
_n,
TEXTSPLIT(
CONCAT(
_e&"-"
),
"00",
"-",
1
),
_b,
BYROW(
_n,
LAMBDA(
r,
SUM(
r*{-1,
1}
)
)
),
HSTACK(
{"Buy";"Sell"},
"Day"&TOCOL(
FILTER(
_n,
_b=MIN(
_b
)
)
)
))Excel solution 10 for Maximize Buy-Sell Profit Days, proposed by Sunny Baggu:
=LET(
_day,
A2:A11,
_price,
B2:B11,
_val1,
TOROW(
_price
) - _price,
_val2,
DROP(
IFNA(
REDUCE(
0,
SEQUENCE(
ROWS(
_day
)
),
LAMBDA(
a,
v,
HSTACK(
a,
SEQUENCE(
v,
,
0,
0
)
)
)
),
1
),
,
1
),
_val3,
IF(
_val2 > 0,
_val1,
0
),
_max,
MAX(
_val3
),
_col1,
TOCOL(
IF(
_val3 = _max,
TOROW(
_day
),
1 / x
),
3
),
_col2,
TOCOL(
IF(
_val3 = _max,
_day,
1 / x
),
3
),
HSTACK(
D2:D3,
TOCOL(
FILTER(
HSTACK(
_col1,
_col2
),
MIN(
RIGHT(
_col2
) - RIGHT(
_col1
)
) = RIGHT(
_col2
) - RIGHT(
_col1
)
)
)
)
)Excel solution 11 for Maximize Buy-Sell Profit Days, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,
A2:A11,
p,
B2:B11,
r,
TOROW(
p
),
s,
SEQUENCE(
,
ROWS(
p
)
),
w,
(s>TOCOL(
s
))*(p-r)+ROW(
p
)*10^-9,
m,
MAX(
w
),
VSTACK(
HSTACK(
"Buy",
@TOCOL(
IFS(
w=m,
d
),
2
)
),
HSTACK(
"Sell",
@TOCOL(
IFS(
w=m,
TOROW(
d
)
),
2
)
)
))Excel solution 12 for Maximize Buy-Sell Profit Days, proposed by Pieter de Bruijn:
=LET(a,A2:A11,r,ROWS(a),m,DROP(REDUCE(0,B2:B11,LAMBDA(x,y,IFNA(HSTACK(x,TOCOL(y-y:B11)),""))),,1),x,XMATCH(MAX(m),TOCOL(m)),b,MOD(x,r),WRAPROWS(HSTACK("Buy",INDEX(a,b),"Sell",INDEX(a,b+ROUNDUP(x/r,)-1)),2))Excel solution 13 for Maximize Buy-Sell Profit Days, proposed by samir tobeil:
=LET(a,A2:A11,b,B2:B11,s,MAP(b,LAMBDA(x,MAX(B2:x)-MIN(x:B11))),f,--(s=MAX(s))*b,
HSTACK({"Buy";"Sell"},VSTACK(XLOOKUP(MAX(f),f,a,,,-1),INDEX(a,XMATCH(MIN(FILTER(f,f>0)),f)))))Solving the challenge of Maximize Buy-Sell Profit Days with Python in Excel
Python in Excel solution 1 for Maximize Buy-Sell Profit Days, proposed by Bo Rydobon 🇹🇭:
pr = xl("A2:B11").values
np.transpose([['Buy','Sell']]+[sorted(sorted([pr[j,1]-pr[i,1],pr[j,0],pr[i,0]] for j in range(i))[-1] for i in range(1,len(pr)))[-1][1:]])Python in Excel solution 2 for Maximize Buy-Sell Profit Days, proposed by John V.:
Hi everyone!
p = xl("B2:B11")[0]
max_diff = 0
min_gap = len(p)
buy_day = 0
sell_day = 0
for x in range(len(p)):
for y in range(x+1, len(p)):
diff = p[x] - p[y]
gap = y - x
if diff > max_diff or (diff == max_diff and gap < min_gap):
max_diff = diff
min_gap = gap
buy_day = x + 1
sell_day = y + 1
[["Buy", "Day" + str(buy_day)], ["Sell", "Day" + str(sell_day)]]
Blessings!
Solving the challenge of Maximize Buy-Sell Profit Days with R
R solution 1 for Maximize Buy-Sell Profit Days, proposed& by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Buy Sell.xlsx") %>% select(1,2)
achieve_max_profit = function(df) {
df = df %>%
mutate(day_number = parse_number(Day))
grid = expand.grid(buy_day = df$day_number, sell_day = df$day_number) %>%
filter(sell_day > buy_day)
buy_prices = df %>%
select(buy_day = day_number, buy_price = Price)
sell_prices = df %>%
select(sell_day = day_number, sell_price = Price)
joined_data = grid %>%
left_join(buy_prices, by = join_by(buy_day)) %>%
left_join(sell_prices, by = join_by(sell_day)) %>%
mutate(sell_delay = sell_day - buy_day,
profit = sell_price - buy_price) %>%
arrange(-profit, sell_delay) %>%
slice(1)
return(joined_data)
}
achieve_max_profit(input)
&&