Home » Daily Stock Closing Value

Daily Stock Closing Value

Yesterday’s challenge was a tough one. Here, is an easier one though not straightforward in Power Query. Find the closing stock every day for table T1. The open stock on 1-Nov-23 has been given in table T2. Closing stock on 1-Nov-23 = Open Stock from Table T2 + IN-OUT Closing stock on succeeding days = Previous day’s closing stock + IN – OUT

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 134
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Daily Stock Closing Value with Power Query

Power Query solution 1 for Daily Stock Closing Value, proposed by Zoran Milokanović: 
let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  T1 = Source("Table1"), 
  S = Table.AddColumn(
    T1, 
    "Closing Stock", 
    (r) =>
      let
        l = List.Sum, 
        t = Table.SelectRows(T1, each [Store] = r[Store] and [Date] <= r[Date])
      in
        Source("Table2"){[Store = r[Store]]}[Open Stock] + l(t[IN]) - l(t[OUT])
  )
in
  S
Power Query solution 2 for Daily Stock Closing Value, proposed by Zoran Milokanović: 
let
  Source = each Excel.CurrentWorkbook(){[Name = _]}[Content], 
  T1 = Source("Table1"), 
  S = Table.FromRows(
    List.Accumulate(
      Table.ToRows(T1), 
      {}, 
      (s, c) =>
        s
          & {
            c
              & {
                let
                  l = List.Last(s, {""})
                in
                  c{2}
                    - c{3}
                    + (if l{0} = c{0} then l{4} else Source("Table2"){[Store = c{0}]}[Open Stock])
              }
          }
    ), 
    Table.ColumnNames(T1) & {"Closing Stock"}
  )
in
  S
Power Query solution 3 for Daily Stock Closing Value, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = T1, 
  Sol = Table.Combine(
    Table.Group(
      Source, 
      {"Store"}, 
      {
        {
          "All", 
          (x) =>
            let
              a = T2[Open Stock]{List.PositionOf(T2[Store], x[Store]{0})}, 
              b = Table.AddColumn(x, "A", each [IN] - [OUT]), 
              c = List.Skip(List.Accumulate(b[A], {a}, (s, c) => s & {List.Last(s) + c})), 
              d = Table.FromColumns(
                Table.ToColumns(x) & {c}, 
                Table.ColumnNames(x) & {"Closing Stock"}
              )
            in
              d
        }
      }
    )[All]
  )
in
  Sol
Power Query solution 4 for Daily Stock Closing Value, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "Acc", 
    each [
      a = Table.SelectRows(Tabela2, (x) => [Store] = x[Store] and Date.Day(Date.From([Date])) = 1)[
        Open Stock
      ]{0}?
        ?? [IN]
        - [OUT], 
      b = if Date.Day(Date.From([Date])) = 1 then [IN] - [OUT] + a else [IN] - [OUT]
    ][b]
  ), 
  gp = Table.Group(
    add, 
    {"Store"}, 
    {
      {
        "Contagem", 
        each [
          a = Table.AddIndexColumn(_, "Ind", 1, 1), 
          b = Table.AddColumn(a, "Valor", each List.Sum(List.FirstN(a[Acc], [Ind])))
        ][b]
      }
    }
  ), 
  res = Table.ExpandTableColumn(gp, "Contagem", {"Date", "IN", "OUT", "Valor"})
in
  res
Power Query solution 5 for Daily Stock Closing Value, proposed by Alexis Olson: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Store", type text}, {"Date", type date}, {"IN", Int64.Type}, {"OUT", Int64.Type}}
  ), 
  #"Added Custom" = Table.AddColumn(#"Changed Type", "NET", each [IN] - [OUT], Int64.Type), 
  #"Merged Queries" = Table.NestedJoin(
    #"Added Custom", 
    {"Store"}, 
    T2, 
    {"Store"}, 
    "T2", 
    JoinKind.LeftOuter
  ), 
  #"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"Open Stock"}, {"Open Stock"}), 
  #"Grouped Rows" = Table.Group(
    #"Expanded T2", 
    {"Store"}, 
    {
      {
        "Subtable", 
        each 
          let
            OpenStock = List.Max([Open Stock]), 
            NetInOut = List.Buffer([NET]), 
            RunningTotal = List.Generate(
              () => [RT = NetInOut{0} + OpenStock, i = 0], 
              each [i] < List.Count(NetInOut), 
              each [i = [i] + 1, RT = [RT] + NetInOut{i}], 
              each [RT]
            )
          in
            Table.FromColumns(
              Table.ToColumns(_) & {RunningTotal}, 
              Table.ColumnNames(_) & {"Closing Stock"}
            )
      }
    }
  ), 
  #"Expanded Subtable" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Subtable", 
    {"Date", "IN", "OUT", "Closing Stock"}
  )
in
  #"Expanded Subtable"
Power Query solution 6 for Daily Stock Closing Value, proposed by Ramiro Ayala Chávez: 
let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content], 
  a = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  d = Table.AddColumn(
    a, 
    "O", 
    each 
      let
        b = Origen[Open Stock], 
        c = List.PositionOf(Origen[Store], [Store])
      in
        b{c}
  ), 
  e = Table.Group(d, {"Store"}, {{"B", each _}})[[B]], 
  f = Table.TransformColumns(
    e, 
    {
      "B", 
      each 
        let
          g = Table.AddColumn(_, "C", each [IN] - [OUT]), 
          h = Table.InsertRows(
            g, 
            0, 
            {[Store = null, Date = null, IN = null, OUT = null, O = null, C = [O]{0}]}
          ), 
          i = Table.AddIndexColumn(h, "I", 1), 
          j = Table.Skip(
            Table.AddColumn(i, "Closing Stock", each List.Sum(List.Range(i[C], 0, [I])))
          )
        in
          j
    }
  ), 
  Sol = Table.Combine(f[B])[[Store], [Date], [IN], [OUT], [Closing Stock]]
in
  Sol
Power Query solution 7 for Daily Stock Closing Value, proposed by Eric Laforce: 
let
  t1 = Excel.CurrentWorkbook(){[Name = "tData134"]}[Content], 
  t2 = Table.Buffer(Excel.CurrentWorkbook(){[Name = "tData134_2"]}[Content]), 
  Group = Table.Group(
    t1, 
    {"Store"}, 
    {
      "All", 
      (t) =>
        let
          _OpenStk = t2[Open Stock]{List.PositionOf(t2[Store], t[Store]{0})}, 
          _Transform = List.Accumulate(
            Table.ToRecords(t), 
            [Stk = _OpenStk, r = {}], 
            (s, c) =>
              let
                _NewStk = s[Stk] + c[IN] - c[OUT]
              in
                [Stk = _NewStk, r = s[r] & {Record.AddField(c, "Closing Stock", _NewStk)}]
          )
        in
          Table.FromRecords(_Transform[r])
    }
  ), 
  Combine = Table.Combine(Group[All])
in
  Combine
Power Query solution 8 for Daily Stock Closing Value, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content], 
  S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content], 
  G = Table.Group(
    S1, 
    {"Store"}, 
    {
      {
        "Tbl", 
        each _, 
        type table [
          Store = nullable text, 
          Date = nullable datetime, 
          IN = nullable number, 
          OUT = nullable number
        ]
      }
    }
  ), 
  A = Table.AddColumn(G, "Tbl2", each Table.AddIndexColumn([Tbl], "I", 1, 1)), 
  R = Table.SelectColumns(A, {"Tbl2"}), 
  Tbls = (Tbl) =>
    let
      In = Table.AddColumn(Tbl, "S", each [IN] - [OUT], Int64.Type), 
      A2 = Table.AddColumn(In, "C", each List.Sum(List.FirstN(In[S], [I])))
    in
      A2, 
  IN2 = Table.AddColumn(R, "Tbls", each Tbls([Tbl2])), 
  R2 = Table.SelectColumns(IN2, {"Tbls"}), 
  Ex = Table.ExpandTableColumn(
    R2, 
    "Tbls", 
    {"Store", "Date", "IN", "OUT", "I", "S", "C"}, 
    {"Store", "Date", "IN", "OUT", "I", "S", "C"}
  ), 
  C2 = Table.NestedJoin(Ex, {"Store"}, S2, {"Store"}, "New", JoinKind.LeftOuter), 
  E = Table.ExpandTableColumn(C2, "New", {"Open Stock"}, {"Open Stock"}), 
  In3 = Table.AddColumn(E, "S1", each [Open Stock] + [C], type number), 
  R3 = Table.RenameColumns(In3, {{"S1", "Closing Stock"}}), 
  Rem = Table.SelectColumns(R3, {"Store", "Date", "IN", "OUT", "Closing Stock"})
in
  Rem
Power Query solution 9 for Daily Stock Closing Value, proposed by Dominic Walsh: 
let
  T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content], 
  Join = Table.Join(T1, "Store", T2, "Store"), 
  Sort = Table.Sort(Join, {{"Store", Order.Ascending}, {"Date", Order.Ascending}}), 
  Shift = Table.FromColumns(
    Table.ToColumns(Sort) & {{null} & List.RemoveLastN(Sort[Store], 1)}, 
    Table.ColumnNames(Sort) & {"Shifted"}
  ), 
  End = Table.AddIndexColumn(Shift, "End", 0, 1, Int64.Type), 
  Start = Table.AddColumn(End, "Start", each if [Store] <> [Shifted] then [End] else null), 
  Fill = Table.FillDown(Start, {"Start"}), 
  Sum = Table.AddColumn(
    Fill, 
    "Closing Stock", 
    each List.Sum(List.Range(Start[IN], [Start], [End] - [Start] + 1))
      - List.Sum(List.Range(Start[OUT], [Start], [End] - [Start] + 1))
      + [Open Stock]
  ), 
  Final = Table.RemoveColumns(Sum, {"End", "Open Stock", "Shifted", "Start"})
in
  Final

Solving the challenge of Daily Stock Closing Value with Excel

Excel solution 1 for Daily Stock Closing Value, proposed by Bo Rydobon 🇹🇭: 
=HSTACK(A2:D12,MAP(A2:A12,C2:C12,D2:D12,LAMBDA(a,c,d,VLOOKUP(a,F2:G4,2,)+SUMIFS(C2:c,A2:a,a)-SUMIFS(D2:d,A2:a,a))))
Excel solution 2 for Daily Stock Closing Value, proposed by Rick Rothstein: 
=IF(A2<>A1,LOOKUP(A2,G$2:G$4,H$2:H$4),E1)
Excel solution 3 for Daily Stock Closing Value, proposed by محمد حلمي: 
=SCAN(0,A2:A12,LAMBDA(a,c,IF(c=OFFSET(c,-1,),a,
VLOOKUP(c,F2:G4,2,))+SUM(OFFSET(c,,2,,2)*{1,-1})))
Excel solution 4 for Daily Stock Closing Value, proposed by محمد حلمي: 
=MAP(A2:A12,LAMBDA(d,INDEX(SCAN(VLOOKUP(d,F2:G4,2,),FILTER(C2:C12-D2:D12,A2:A12=d),LAMBDA(c,v,c+v)),
COUNTIF(A2:d,d))))
Excel solution 5 for Daily Stock Closing Value, proposed by محمد حلمي: 
=REDUCE("Closing Stock",F2:F4,LAMBDA(A,d,
VSTACK(A,SCAN(VLOOKUP(d,F2:G4,2,),FILTER(
C2:C12-D2:D12,A2:A12=d),LAMBDA(c,v,c+v)))))


=REDUCE("Closing Stock",
UNIQUE(A2:A12),LAMBDA(A,d,LET(
i,FILTER(C2:D12,A2:A12=d),VSTACK(A,SCAN(VLOOKUP(
d,F2:G4,2,),TAKE(i,,1)-DROP(i,,1),LAMBDA(c,v,c+v))))))
Excel solution 6 for Daily Stock Closing Value, proposed by Kris Jaganah: 
=LET(a,A1:A12,b,B1:B12,c,C1:C12,d,D1:D12,e,F2:F4,f,G2:G4,g,IF(b=MIN(b),XLOOKUP(a,e,f),0),h,TOCOL(HSTACK(IF(g>0,a,1/0),c-d+g),3),i,SCAN(0,h,LAMBDA(x,y,IF(ISTEXT(y),0,x+y))),HSTACK(a,b,c,d,VSTACK("Closing Stock",FILTER(i,i<>0)))
Excel solution 7 for Daily Stock Closing Value, proposed by Duy Tùng: 
=LET(a,A2:A12,b,ROW(a),HSTACK(A1:D12,VSTACK("Closing Stock",LOOKUP(a,F2:G4)-MMULT((a=TOROW(a))*(b>=TOROW(b)),D2:D12-C2:C12))))
Excel solution 8 for Daily Stock Closing Value, proposed by Sunny Baggu: 
=HSTACK(
 A2:D12,
 DROP(
 REDUCE(
 "",
 F2:F4,
 LAMBDA(a, v,
 VSTACK(
 a,
 SCAN(
 XLOOKUP(v, F2:F4, G2:G4),
 FILTER(C2:C12 - D2:D12, A2:A12 = v),
 LAMBDA(x, y, x + y)
 )
 )
 )
 ),
 1
 )
)
Excel solution 9 for Daily Stock Closing Value, proposed by Mey Tithveasna: 
=VSTACK (A1:E1,HSTACK(A2:D12,MAP(A2:A12,C2:C12,D2:D12, LAMBDA(a,c,d,
LOOKUP(a,F2:G4)+SUMIF(A2:a,a,C2:c)-SUMIF(A2:a,a,D2:d)))))
Excel solution 10 for Daily Stock Closing Value, proposed by Ziad A.: 
={A2:D12,SCAN(,A2:A12,LAMBDA(a,c,IF(c=OFFSET(c,-1,),a,VLOOKUP(c,F2:G4,2,))+OFFSET(c,,2)-OFFSET(c,,3)))
Excel solution 11 for Daily Stock Closing Value, proposed by Edwin Tisnado: 
=LET(a,A2:D12,b,F2:G4,v,HSTACK(A1:D1,"Closing Stock"),i,TAKE(a,,-2),j,TAKE(i,,1)-TAKE(i,,-1),c,TAKE(a,,1),s,c=OFFSET(c,-1,),t,VLOOKUP(c,b,2),f,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(x,y,LET(u,INDEX(j,y),IF(INDEX(s,y),x+u,u)))),VSTACK(v,HSTACK(a,f+t)))
Excel solution 12 for Daily Stock Closing Value, proposed by Hazem Hassan: 
=IF(A2=A1,I1+C2-D2,VLOOKUP(A2,$F$2:$G$4,2,)+C2-D2)

Solving the challenge of Daily Stock Closing Value with Python in Excel

Python in Excel solution 1 for Daily Stock Closing Value, proposed by Alejandro Campos: 
df = pd.merge(
 xl("A1:D12", headers=True).sort_values(by=['Store', 'Date']).assign(
 Date=lambda x: pd.to_datetime(x['Date'], format='%d/%m/%Y')),
 xl("F1:G4", headers=True),
 on='Store')
df['Closing Stock'] = df.groupby('Store').apply(
 lambda g: g.assign(Closing_Stock=g['Open Stock'].cumsum() + g['IN'].cumsum() - g['OUT'].cumsum()))
 .reset_index(drop=True)['Closing_Stock']
result_df = df[['Store', 'Date', 'IN', 'OUT', 'Closing Stock']].to_numpy().tolist()
result_df

Solving the challenge of Daily Stock Closing Value with R

R solution 1 for Daily Stock Closing Value, proposed by Konrad Gryczan, PhD: 
I yesterday was at 10/10, today we have like strong 8/10.
library(tidyverse)
library(readxl)
input1 = read_excel("PQ_Challenge_134.xlsx", range = "A1:D12")
input2 = read_excel("PQ_Challenge_134.xlsx", range = "F1:G4")
test = read_excel("PQ_Challenge_134.xlsx", range = "J1:N12")
opening = input2 %>% 
 mutate(Date = min(input1$Date)) %>%
 rename(open = `Open Stock`)
result <- opening %>%
 rename(IN = open, OUT = integer(length(open))) %>%
 bind_rows(input1) %>%
 group_by(Store) %>%
 arrange(Store, Date) %>%
 mutate(OUT = ifelse(is.na(OUT), 0, OUT),
 closing_stock = cumsum(IN) - cumsum(OUT)) %>%
 ungroup() %>%
 filter(OUT != 0) %>%
 select(Store, Date, IN, OUT, `Closing Stock` = closing_stock)

&&

Leave a Reply