Hello guys!
I want to write a macro to receive data from the 'Transaction' sheet and gives me an 'Inventory' sheet but I don't know how to do it
(the 'Transaction' sheet may contain many shares so the macro should count them all and if we sell a share that we don't have in the first place,(short it) it should warn us)
from
to
could you please help me??


Also asked here Transaction to Inventory excel VBA using macro - OzGrid Free Excel/VBA Help Forum
and here Transaction to Inventory excel VBA using macro
I want to write a macro to receive data from the 'Transaction' sheet and gives me an 'Inventory' sheet but I don't know how to do it
(the 'Transaction' sheet may contain many shares so the macro should count them all and if we sell a share that we don't have in the first place,(short it) it should warn us)
from
transaction help.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Ticker | Tranaction | Amount | Buy/Sell Price | |||
2 | Share1 | Buy | 4,000 | 6,582 | |||
3 | Share2 | Buy | 1,240 | 8,065 | |||
4 | Share3 | Buy | 1,940 | 5,930 | |||
5 | Share1 | Buy | 6,000 | 7,737 | |||
6 | Share1 | Buy | 15,000 | 7,989 | |||
7 | Share4 | Buy | 2,000 | 7,234 | |||
8 | Share4 | Buy | 1,000 | 7,234 | |||
9 | Share1 | Sell | 5,010 | 7,124 | |||
10 | Share5 | Buy | 1,000 | 2,110 | |||
11 | Share6 | Buy | 13,000 | 7,536 | |||
12 | Share7 | Buy | 3,000 | 6,014 | |||
13 | Share5 | Sell | 1,000 | 2,287 | |||
14 | Share8 | Buy | 1,500 | 27,734 | |||
15 | Share1 | Buy | 2,300 | 8,700 | |||
16 | Share7 | Sell | 2,000 | 6,145 | |||
17 | |||||||
Transaction |
to
transaction help.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Ticker | Buy Volume | Cost | Average Buying Price | Sell Volume | Sell Amount | Average Sell Price | Inventory | Profit/Loss | |||
2 | Share1 | 27,300 | 211,547,700 | 7,749 | 5,010 | 35,691,240 | 7,124 | 22,290 | (3,131,250) | |||
3 | Share2 | 1,240 | 10,000,600 | 8,065 | - | 1,240 | - | |||||
4 | Share3 | 1,940 | 11,504,200 | 5,930 | - | 1,940 | - | |||||
5 | Share4 | 3,000 | 21,702,000 | 7,234 | - | 3,000 | - | |||||
6 | Share5 | 1,000 | 2,110,000 | 2,110 | 1,000 | 2,287,000 | 2,287 | - | 177,000 | |||
7 | Share6 | 13,000 | 97,968,000 | 7,536 | - | 13,000 | - | |||||
8 | Share7 | 3,000 | 18,042,000 | 6,014 | 2,000 | 12,290,000 | 6,145 | 1,000 | 262,000 | |||
9 | Share8 | 1,500 | 41,601,000 | 27,734 | - | 1,500 | - | |||||
10 | ||||||||||||
11 | ||||||||||||
12 | 414,475,500 | |||||||||||
Inventory |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F9,C2:C9 | C2 | =B2*D2 |
I2:I9 | I2 | =E2*(G2-D2) |
C12 | C12 | =SUM(C2:C9) |
could you please help me??


Also asked here Transaction to Inventory excel VBA using macro - OzGrid Free Excel/VBA Help Forum
and here Transaction to Inventory excel VBA using macro
Last edited by a moderator: