For each row, you need to buy first and sell second. Only one buy and one sell permitted. Find the buy and sell which will ensure positive maximum profit. In case, profit is not possible, populate NP.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 561
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Maximum Profit from Transactions with Power Query
Power Query solution 1 for Maximum Profit from Transactions, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Id"),
C = Table.UnpivotOtherColumns(B, {"Id"}, "A", "V"),
D = Table.Group(
C,
{"Id"},
{
"All",
(x) =>
let
p = Table.AddColumn(
x,
"U",
each [
a = [V],
b = Number.From(Text.AfterDelimiter([A], "T")),
c = List.Max(List.Transform(List.FirstN(x[V], b), each a - _))
][c]
),
q = Table.FirstN(Table.SelectRows(p, each ([U] = List.Max(p[U]))), 1),
r = Table.AddColumn(
q,
"W",
each [
Profit = if [U] <= 0 then "NP" else [U],
Sell = if [U] <= 0 then "NP" else [V],
Buy = if [U] <= 0 then "NP" else [V] - [U]
][[Buy], [Sell], [Profit]]
)[W]
in
r
}
)[All],
E = (Table.FromRows(D)),
F = Table.ExpandRecordColumn(E, "Column1", {"Buy", "Sell", "Profit"})
in
FPower Query solution 2 for Maximum Profit from Transactions, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Record = Table.AddColumn(
Source,
"R",
each [
L = Record.ToList(_),
T = List.TransformMany(
{0 .. List.Count(L) - 1},
(x) => List.FirstN(L, x),
(x, y) => [Sell = L{x}, Buy = y, Profit = Sell - Buy]
),
S = List.Max(T, null, each [Profit]),
R = if S[Profit] > 0 then S else []
][R]
),
Return = Table.ExpandRecordColumn(Record, "R", {"Buy", "Sell", "Profit"})
in
ReturnPower Query solution 3 for Maximum Profit from Transactions, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Record.ToList(_),
b = List.Transform(
{0 .. List.Count(a) - 2},
each
let
b1 = List.RemoveFirstN(a, _),
b2 = List.Transform(
{0 .. List.Count(b1) - 2},
each {b1{0}, b1{_ + 1}, b1{_ + 1} - b1{0}}
)
in
b2
),
c = List.Select(List.Combine(b), each _{2} > 0),
d = List.Max(List.Transform(c, each _{2})),
e = List.Select(c, each _{2} = d),
f = Table.FromRows(
if List.IsEmpty(e) then {{"NP", "NP", "NP"}} else e,
{"Buy", "Sell", "Profit"}
)
in
f
)[A]
)
in
SolPower Query solution 4 for Maximum Profit from Transactions, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformRows(
Source,
each [
a = Record.ToList(_),
b = List.TransformMany(a, each List.Skip(a, List.PositionOf(a, _) + 1), (x, y) => {x, y}),
c = List.Sort(b, each - (_{1} - _{0})),
d = if c{0}{1} > c{0}{0} then c{0} & {c{0}{1} - c{0}{0}} else {"NP", "NP", "NP"}
][d]
),
Result = Table.FromRows(Transform, {"Buy", "Sell", "Profit"})
in
ResultPower Query solution 5 for Maximum Profit from Transactions, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(S, "A", each Record.ToList(_)),
Fx = (x) =>
let
a = List.Transform(
{0 .. Number.Power(2, List.Count(x)) - 1},
(i) =>
List.Transform(
{0 .. List.Count(x) - 1},
(j) =>
if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1 then x{j} else null
)
),
b = List.Skip(List.Transform(a, each List.RemoveNulls(_))),
c = List.Select(b, each List.Count(_) = 2),
d = List.Transform(c, each _{1} - _{0}),
e = List.Transform(List.Positions(d), each c{_} & {d{_}}),
f = Table.MaxN(Table.FromRows(e, {"Buy", "Sell", "Profit"}), "Profit", 1)
in
f,
B = Table.AddColumn(A, "B", each Fx([A]))[[B]],
C = Table.ExpandTableColumn(B, "B", {"Buy", "Sell", "Profit"}),
D = List.Transform(Table.ToRows(C), each if _{2} <= 0 then {"NP"} & {"NP"} & {"NP"} else _),
Sol = Table.FromRows(D, Table.ColumnNames(C))
in
SolPower Query solution 6 for Maximum Profit from Transactions, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
Result = Table.FromRows(
L(
Table.ToRows(Source),
(f) =>
[
buy = List.Min(f),
sel = List.Max(List.Skip(f, List.PositionOf(f, buy))),
pft = sel - buy,
Lst = L({buy} & {sel} & {pft}, each if pft = 0 then "NP" else _)
][Lst]
),
{"Buy", "Sell", "Profit"}
)
in
ResultPower Query solution 7 for Maximum Profit from Transactions, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
RowToList = Table.ToRows(Source),
listTransform = List.Transform(
RowToList,
each [
ftbs = List.Select(
_,
(x) =>
List.PositionOf(_, x, Occurrence.First)
>= List.PositionOf(_, List.Min(_), Occurrence.First)
),
a = {List.Min(ftbs), List.Max(ftbs), List.Max(ftbs) - List.Min(ftbs)},
b = if a{2} = 0 then List.Repeat({"NP"}, 3) else a,
c = _ & b
][c]
),
result = Table.FromRows(listTransform, Table.ColumnNames(Source) & {"Buy", "Sell", "Profit"})
in
resultSolving the challenge of Maximum Profit from Transactions with Excel
Excel solution 1 for Maximum Profit from Transactions, proposed by Bo Rydobon 🇹🇭:
=IFERROR(--TEXTSPLIT(CONCAT(BYROW(A3:J11,LAMBDA(t,LET(c,COLUMN(t),v,TOCOL(t),p,(c>TOCOL(c))*(t-v),
IFERROR(@TOCOL(IFS((p=MAX(p))*(p>0),v&-t&-p),3),"a"))))&" "),"-"," ",1),"NP")Excel solution 2 for Maximum Profit from Transactions, proposed by Rick Rothstein:
=IFERROR(
0+TEXTSPLIT(
TEXTJOIN(
"*",
,
BYROW(
A3:J11,
LAMBDA(
r,
LET(
m,
MIN(
r
),
n,
XMATCH(
m,
r
),
x,
MAX(
INDEX(
r,
,
SEQUENCE(
11-n,
,
n
)
)
),
IF(
x<=m,
"z",
m&"|"&x&"|"&x-m
)
)
)
)
),
"|",
"*"
),
"NP"
)Excel solution 3 for Maximum Profit from Transactions, proposed by John V.:
=LET(s,BYROW(A3:J11,LAMBDA(x,MAX(DROP(x,,XMATCH(MIN(x),x))))),b,BYROW(A3:J11,MIN),IFERROR(HSTACK(b*s^0,s,s-b),"NP"))
or:
✅=LET(s,BYROW(A3:J11,LAMBDA(x,MAX(DROP(x,,XMATCH(MIN(x),x)-1)))),b,BYROW(A3:J11,MIN),IF(s-b,HSTACK(b,s,s-b),"NP"))Excel solution 4 for Maximum Profit from Transactions, proposed by 🇰🇷 Taeyong Shin:
=REDUCE(
K2:M2,
BYROW(
A3:J11,
LAMBDA(
r,
LAMBDA(
LET(
c,
XLOOKUP(
0,
r,
r,
,
1
),
m,
MAX(
c:TAKE(
r,
,
-1
)
),
IF(
m-c,
HSTACK(
c,
m,
m-c
),
"NP"&{"",
"",
""}
)
)
)
)
),
LAMBDA(
a,
v,
VSTACK(
a,
v()
)
)
)Excel solution 5 for Maximum Profit from Transactions, proposed by 🇰🇷 Taeyong Shin:
=MAKEARRAY(
ROWS(
A3:J11
),
3,
LAMBDA(
r,
c,
LET(
a,
INDEX(
A3:J11,
r,
),
b,
XLOOKUP(
0,
a,
a,
,
1
),
m,
MAX(
b:TAKE(
a,
,
-1
)
),
INDEX(
IF(
m-b,
HSTACK(
b,
m,
m-b
),
"NP"&{"",
"",
""}
),
c
)
)
)
)Excel solution 6 for Maximum Profit from Transactions, proposed by Kris Jaganah:
=REDUCE({"Buy","Sell","Profit"},BYROW(A3:J11,LAMBDA(v,LET(a,--TEXTAFTER($A$2:$J$2,"T"),c,MAP(a,v,LAMBDA(x,y,LET(p,FILTER(v,a>x),IFERROR(MAX(FILTER(p,y0),{"","",""}))))),LAMBDA(q,r,VSTACK(q,IFERROR(--TEXTSPLIT(r,", "),"NP")))) Excel solution 7 for Maximum Profit from Transactions, proposed by Julian Poeltl:
=TEXTSPLIT(
TEXTJOIN(
"|",
,
BYROW(
A3:J11,
LAMBDA(
B,
LET(
C,
MAP(
B,
LAMBDA(
A,
MAX(
A:TAKE(
B,
,
-1
)
)-A
)
),
M,
MAX(
C
),
X,
XLOOKUP(
M,
C,
B
),
S,
X+M,
TEXTJOIN(
",",
,
IF(
M=0,
"NP,NP,NP",
HSTACK(
X,
S,
M
)
)
)
)
)
)
),
",",
"|"
)Excel solution 8 for Maximum Profit from Transactions, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_thunk, BYROW(
A3:J11,
LAMBDA(x,
LET(
first, TAKE(x, , 1),
calc, REDUCE(
"",
x,
LAMBDA(a, b, VSTACK(a, IFNA(HSTACK(TOCOL(first:b), b, TOCOL(b - first:b)), b)))
),
drop, DROP(calc, 1),
profit, TAKE(drop, , -1),
return, FILTER(drop, (profit = MAX(profit)) * (profit > 0), {"NP", "NP", "NP"}),
LAMBDA(return)
)
)
),
_return, DROP(REDUCE("", _thunk, LAMBDA(a, b, VSTACK(a, b()))), 1),
_return
)Excel solution 9 for Maximum Profit from Transactions, proposed by Timothée BLIOT:
=REDUCE(
{"Buy",
"Sell",
"Profit"},
ROW(
1:9
),
LAMBDA(
w,
v,
LET(
A,
INDEX(
A3:J11,
v,
),
B,
MAKEARRAY(
10,
10,
LAMBDA(
x,
y,
IF(
x>y,
INDEX(
A,
,
x
)-INDEX(
A,
,
y
),
0
)
)
),
C,
MAX(
B
),
E,
LAMBDA(
m,
INDEX(
A,
,
TOCOL(
UNIQUE(
IF(
B=C,
m,
1/0
)
),
3
)
)
),
VSTACK(
w,
IF(
C>0,
HSTACK(
E(
SEQUENCE(
,
10
)
),
E(
SEQUENCE(
10
)
),
C
),
{"NP",
"NP",
"NP"}
)
)
)
)
)Excel solution 10 for Maximum Profit from Transactions, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
BYROW(
A3:J11,
LAMBDA(
x,
LET(
b,
MIN(
x
),
s,
MAX(
DROP(
x,
,
XMATCH(
b,
x
)
)
),
IF(
ISERR(
s
),
"NP/NP/NP",
& b&"/"&s&"/"&s-b
)&"|"
)
)
)
),
"/",
"|",
1
)Excel solution 11 for Maximum Profit from Transactions, proposed by Oscar Mendez Roca Farell:
=REDUCE(K2:M2, A3:A11, LAMBDA(i, x, LET(f, TAKE(x:J11, 1), x, XLOOKUP(MIN(f), f, f), m, MAX(x: TAKE(f, ,-1)), IFNA(VSTACK(i, IF(m-x, HSTACK(x, m, m-x), "NP")), "NP"))))Excel solution 12 for Maximum Profit from Transactions, proposed by Sunny Baggu:
=MAKEARRAY(
ROWS(
A3:J11
),
3,
LAMBDA(
r,
c,
INDEX(
LET(
_r,
CHOOSEROWS(
A3:J11,
r
),
_a,
XMATCH(
MIN(
_r
),
_r
),
_b,
INDEX(
_r,
,
_a
),
_c,
MAX(
DROP(
_r,
,
_a
)
),
IF(
ISNUMBER(
_c
),
HSTACK(
_b,
_c,
_c - _b
),
EXPAND(
"NP",
,
3,
"NP"
)
)
),
c
)
)
)Excel solution 13 for Maximum Profit from Transactions, proposed by LEONARD OCHEA 🇷🇴:
=TEXTSPLIT(
TEXTJOIN(
"*",
,
BYROW(
A3:J11,
LAMBDA(
f,
LET(
i,
MIN(
f
),
j,
MAX(
INDEX(
f,
XMATCH(
i,
f
)
):INDEX(
f,
10
)
),
p,
j-i,
G,
LAMBDA(
x,
IF(
p>0,
x,
"NP"
)
),
G(
i
)&"|"&G(
j
)&"|"&G(
p
)
)
)
)
),
"|",
"*"
)Excel solution 14 for Maximum Profit from Transactions, proposed by Anshu Bantra:
=TEXTSPLIT(
ARRAYTOTEXT(
BYROW(
A3:J11,
LAMBDA(rng_,
LET(
min_, MIN(rng_),
max_, MAX(
(XLOOKUP(min_, rng_, rng_):XLOOKUP(TAKE(rng_, , -1), rng_, rng_))
),
diff_, (max_ - min_),
IF(
diff_ < 1,
TEXTJOIN("|", , "NP", "NP", "NP"),
TEXTJOIN("|", , min_, max_, diff_)
)
)
)
)
),
"|", ", "
)Excel solution 15 for Maximum Profit from Transactions, proposed by Md. Zohurul Islam:
=REDUCE(
{"Buy", "Sell", "Profit"},
BYROW(
A3:J11,
LAMBDA(a,
LET(
mn, MIN(a),
mnPos, MATCH(mn, a, 0),
num, 10 - mnPos,
seq, IFERROR(SEQUENCE(1, num, mnPos + 1, 1), 10),
mxRange, CHOOSECOLS(a, seq),
mx, MAX(mxRange),
prft, mx - mn,
profit, IF(prft > 0, prft, "NP"),
buy, IF(prft > 0, mn, "NP"),
sell, IF(prft > 0, mx, "NP"),
result, TEXTJOIN("/", FALSE, buy, sell, profit),
result
)
)
),
LAMBDA(x, y,
LET(
p, ABS(TEXTSPLIT(y, "/")),
q, IFERROR(p, "NP"),
Report, VSTACK(x, q),
Report
)
)
)Excel solution 16 for Maximum Profit from Transactions, proposed by Asheesh Pahwa:
=DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A3:A11
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
i,
INDEX(
A3:J11,
y,
),
m,
MIN(
i
),
xm,
10-XMATCH(
m,
i
),
_i,
IF(
xm,
xm,
1
),
mx,
MAX(
TAKE(
i,
,
-_i
)
),
_d,
mx-m,
_i2,
IF(
m=mx,
"NP",
m
),
_i3,
IF(
mx=m,
"NP",
mx
),
HSTACK(
_i2,
_i3,
IF(
_d,
_d,
"NP"
)
)
)
)
)
),
1
)Excel solution 17 for Maximum Profit from Transactions, proposed by Jaroslaw Kujawa:
=DROP(TEXTSPLIT(CONCAT(BYROW(A3:J11;LAMBDA(x;LET(minx;MIN(x);maxx;MAX(TAKE(x;;XMATCH(minx;x)-COLUMNS(x)));IFERROR(IF(maxx<=minx;"";CONCAT(minx&";"&maxx&";"&maxx-minx));REPT("NP;";3))&"|"))));";";"|");-1;-1)Excel solution 18 for Maximum Profit from Transactions, proposed by Meganathan Elumalai:
=LET(z,A3:J11,n,SEQUENCE(ROWS(z)),REDUCE({"Buy","Sell","Profit"},n,LAMBDA(a,v,LET(x,INDEX(z,v,),m,DROP(FREQUENCY(0,x),-1),c,INDEX(x,MATCH(1,m,0)),d,INDEX(x,COUNTA(x)),by,FILTER(x,TRANSPOSE(m)),sl,MAX(c:d),VSTACK(a,IFS(d<>c,HSTACK(by,sl,sl-by),1,{"NP","NP","NP"}))))))Excel solution 19 for Maximum Profit from Transactions, proposed by Bilal Mahmoud kh.:
=TEXTSPLIT(TEXTJOIN("|",
,
BYROW(A3:J11,
LAMBDA(x,
LET(a,
MIN(
x
),
b,
MATCH(
a,
x,
0
),
c,
OFFSET(
x,
0,
b,
1,
10-b
),
d,
c-a,
e,
FILTER(
c,
d=MAX(
d
),
"NP"
),
IFERROR(a&"-"&e&"-"&(e-a),
"NP-"&"NP-"&"NP"))))),
"-",
"|")Excel solution 20 for Maximum Profit from Transactions, proposed by Philippe Brillault:
=LET(
t,
T_Inp,
n,
COLUMNS(
t
),
mtu,
MAKEARRAY(
n,
n,
LAMBDA(
i,
j,
N(
i<=j
)
)
),
TEXTSPLIT(
CONCAT(
SCAN(
"",
SEQUENCE(
ROWS(
t
)
),
LAMBDA(
c,
i,
LET(
v,
INDEX(
t,
i,
),
m,
BYROW(
v*mtu,
MAX
),
g,
m-TRANSPOSE(
v
),
im,
MATCH(
MAX(
g
),
g,
0
),
IF(
MAX(
g
)=0,
"NP.NP.NP;",
INDEX(
v,
im
)&"."&INDEX(
m,
im
)&"."&MAX(
g
)&";"
)
)
)
)
),
".",
";",
1
)
)Excel solution 21 for Maximum Profit from Transactions, proposed by El Badlis Mohd Marzudin:
=TEXTSPLIT(TEXTJOIN("|",
,
BYROW(A3:J11,
LAMBDA(x,
LET(a,
x,
b,
MIN(
a
),
c,
XMATCH(
b,
a,
,
-1
),
d,
MAX(TAKE(a,
,
-(COLUMNS(
a
)-c))),
IFERROR(
ARRAYTOTEXT(
HSTACK(
b,
d,
d-b
)
),
"NP, NP, NP"
))))),
", ",
"|")Excel solution 22 for Maximum Profit from Transactions, proposed by Md. Shah Alam, Microsoft Certified Trainer:
=VSTACK(
{"Buy",
"Sell",
"Profit"},
IFERROR(
DROP(
TEXTSPLIT(
CONCAT(
BYROW(
A3:J11,
LAMBDA(
x,
LET(
a,
MIN(
x
),
b,
MATCH(
a,
x,
0
),
c,
SEQUENCE(
10-b,
,
b+1
),
d,
IFERROR(
MAX(
INDEX(
x,
,
c
)
),
0
),
IF(
d<=a,
"NP",
a&"/"&d&"/"&d-a
)
)
)
)&" "
),
"/",
" "
),
-1
),
"NP"
)
)Excel solution 23 for Maximum Profit from Transactions, proposed by André Gonçalves:
=LET(
array;
$A$3:$J$11;
profit;
DROP(
MAP(
array;
LAMBDA(
a;
MAX(
OFFSET(
a;
0;
1;
1;
COLS(
array
)-COL(
a
)
)
)-a
)
);
;
-1
);
profit_val;
BYROW(
profit;
LAMBDA(
a;
IF(
MAX(
a
)<1;
"NP";
MAX(
a
)
)
)
);
buy_pos;
BYROW(
profit;
LAMBDA(
a;
XMATCH(
IF(
MAX(
a
)<1;
"NP";
MAX(
a
)
);
a
)
)
);
buy_val;
IFERROR(
INDEX(
array;
SEQUENCE(
ROWS(
array
)
);
buy_pos
);
"NP"
);
sell_val;
IFERROR(
profit_val+buy_val;
"NP"
);
HSTACK(
buy_val;
sell_val;
profit_val
)
)Solving the challenge of Maximum Profit from Transactions with Python
Python solution 1 for Maximum Profit from Transactions, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "561 Maximum Profit.xlsx"
input = pd.read_excel(path, usecols="A:J", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="K:M", skiprows=1, nrows=10)
test = test.map(lambda x: np.nan if x == "NP" else float(x))
def process_row(row):
max_diff = -np.inf
buy_value = np.nan
sell_value = np.nan
for i in range(len(row) - 1):
for j in range(i + 1, len(row)):
diff = row[j] - row[i]
if diff > max_diff:
max_diff = diff
buy_value = row[i]
sell_value = row[j]
return pd.Series([buy_value, sell_value, max_diff])
result = input.apply(process_row, axis=1)
result.columns = ['Buy', 'Sell', 'Profit']
result['Buy'] = np.where(result['Profit'] <= 0, np.nan, result['Buy'])
result['Sell'] = np.where(result['Profit'] <= 0, np.nan, result['Sell'])
result['Profit'] = np.where(result['Profit'] <= 0, np.nan, result['Profit'])
print(result.equals(test)) # True
Solving the challenge of Maximum Profit from Transactions with Python in Excel
Python in Excel solution 1 for Maximum Profit from Transactions, proposed by Alejandro Campos:
data = xl("A3:J11").to_numpy().tolist()
def find_max_profit(data):
results = []
for row in data:
max_profit = -np.inf
buy_price = None
sell_price = None
for i in range(len(row)):
for j in range(i + 1, len(row)):
profit = row[j] - row[i]
if profit > max_profit:
max_profit = profit
buy_price = row[i]
sell_price = row[j]
if max_profit > 0:
results.append((buy_price, sell_price, max_profit))
else:
results.append(("NP", "NP", "NP"))
return results
results = find_max_profit(data)
df = pd.DataFrame(results, columns=["Buy", "Sell", "Profit"])
df
Python in Excel solution 2 for Maximum Profit f&rom Transactions, proposed by Abdallah Ally:
from itertools import combinations
def get_values(row):
combs = filter(lambda x: x[1] > x[0], combinations(row.values, 2))
values = sorted(combs, key=lambda x: x[0] - x[1])
if values:
return list(values[0]) + [values[0][1] - values[0][0]]
else:
return ['NP', 'NP', 'NP']
df = xl("A2:J11", headers=True)
# Perform data manipulation
df[['Buy', 'Sell', 'Profit']] = df.apply(get_values, axis=1).tolist()
df
Python in Excel solution 3 for Maximum Profit from Transactions, proposed by Anshu Bantra:
def find_buy_sell_price(row):
min_ = min(row)
pos_ = row.index[row == min_][0]
max_ = max(row.iloc[pos_:len(row)])
return (min_, max_, max_-min_) if max_-min_>0 else ('NP','NP','NP')
df = xl("A3:J11")
df[['Buy', 'Sell', 'Profit']] = df.apply(lambda row: pd.Series(find_buy_sell_price(row)), axis=1)
df[['Buy', 'Sell', 'Profit']]
Python in Excel solution 5 for Maximum Profit from Transactions, proposed by Ümit Barış Köse, MSc:
def calculate_profit(row):
prices = row.values
min_prices = np.minimum.accumulate(prices)
max_profits = prices - min_prices
max_profit = np.max(max_profits)
max_profit_index = np.argmax(max_profits)
buy_price = min_prices[max_profit_index]
sell_price = prices[max_profit_index]
return pd.Series([v if max_profit > 0 else "NP" for v in (buy_price, sell_price, max_profit)])
df=xl("A3:J11")
df[['Buy', 'Sell', 'Profit']] = df.apply(calculate_profit, axis=1)
result_df = df[['Buy', 'Sell', 'Profit']]
Solving the challenge of Maximum Profit from Transactions with R
R solution 1 for Maximum Profit from Transactions, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/561 Maximum Profit.xlsx"
input = read_excel(path, range = "A2:J11")
test = read_excel(path, range = "K2:M11") %>%
mutate(across(everything(), ~if_else(.x == "NP", NA_real_, as.numeric(.x))))
process_row <- function(...){
row <- c_across(everything())
cell_list <- map(1:length(row), ~row[.x:length(row)])
df_pairs <- map_dfr(1:length(cell_list), function(i) {
tibble(
from = rep(row[i], length(cell_list[[i]]) - 1),
to = cell_list[[i]][-1]
)
})
df_pairs <- df_pairs %>%
mutate(diff = to - from)
max_pair <- df_pairs %>%
slice_max(diff, with_ties = FALSE)
return(list(
max_diff = max_pair$diff,
from_value = max_pair$from,
to_value = max_pair$to
))
}
result <- input %>%
rowwise() %>%
mutate(result = list(process_row(across(everything())))) %>%
mutate(
Buy = result$from_value,
Sell = result$to_value,
Profit = result$max_diff
) %>%
ungroup() %>%
select(Buy, Sell, Profit) %>%
mutate(across(everything(), ~if_else(Profit <= 0, NA_real_, .x)))
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
Solving the challenge of Maximum Profit from Transactions with Excel VBA
Excel VBA solution 1 for Maximum Profit from Transactions, proposed by Md. Zohurul Islam:
Sub ExcelBI_Excel_Challenge561()
Dim nr, r, mn, mx
Dim rng As Range
Dim mnPos As Integer
Dim mxRng As Range
Dim diff As Long
'headers
Range("L2:N2") = Array("Buy", "Sell", "Profit")
nr = WorksheetFunction.CountA(Range("A3:A100"))
For r = 1 To nr
Set rng = Range(Cells(r + 2, 1), Cells(r + 2, 10))
mn = WorksheetFunction.Min(rng)
mnPos = WorksheetFunction.Match(mn, rng, 0)
Set mxRng = Range(Cells(r + 2, mnPos + 1), Cells(r + 2, 10))
mx = WorksheetFunction.Max(mxRng)
diff = mx - mn
If diff > 0 Then
Range("L" & r + 2) = mn
Range("M" & r + 2) = mx
Range("N" & r + 2) = diff
Else
Range("L" & r + 2) = "NP"
Range("M" & r + 2) = "NP"
Range("N" & r + 2) = "NP"
End If
Next r
End Sub
&