I am using an excel sheet for the buying and selling stocks and I need some checks (data validation probably can help)
I tried all that I can but it didn't help me.
here are the points to be added to the sheet:
1- Selling dates in E shouldn't be less than Buying dates in cell A - it can be less or equal to buying dates - Action to be taken = STOP
2- Selling quantity in F shouldn't be less or more than Buying quantity in cell B - it must be equal to buying quantity only - Action to be taken = STOP
3- if the selling rate in G is lower than buying rates in C then a warning message should pop that "stock is sold on loss" - Action to be taken = WARNING or INFORMATION
4- if the selling rate in G is higher than buying rate in C then a warning message should pop that "congrats - you sold it on profit" - Action to be taken = WARNING or INFORMATION
5- in both (3 & 4) cases (sold on loss or sold on profit) is it possible to calculate the profit or loss in the pop-up message? If YES then how it is possible?
6- if the total buying amount in D is more than buying power in K12 then stop with the message "cannot buy more than current buying power" - Action to be taken = STOP
in this sheet, some cells in E & F are blank - which means I am still holding these stocks.
Please add 100,000 in K12 - I was trying to use to mini-sheet but it's not working. It shows that mini sheet is save but i cannot paste in the forum (it used to work perfectly before)
I tried all that I can but it didn't help me.
here are the points to be added to the sheet:
1- Selling dates in E shouldn't be less than Buying dates in cell A - it can be less or equal to buying dates - Action to be taken = STOP
2- Selling quantity in F shouldn't be less or more than Buying quantity in cell B - it must be equal to buying quantity only - Action to be taken = STOP
3- if the selling rate in G is lower than buying rates in C then a warning message should pop that "stock is sold on loss" - Action to be taken = WARNING or INFORMATION
4- if the selling rate in G is higher than buying rate in C then a warning message should pop that "congrats - you sold it on profit" - Action to be taken = WARNING or INFORMATION
5- in both (3 & 4) cases (sold on loss or sold on profit) is it possible to calculate the profit or loss in the pop-up message? If YES then how it is possible?
6- if the total buying amount in D is more than buying power in K12 then stop with the message "cannot buy more than current buying power" - Action to be taken = STOP
in this sheet, some cells in E & F are blank - which means I am still holding these stocks.
Please add 100,000 in K12 - I was trying to use to mini-sheet but it's not working. It shows that mini sheet is save but i cannot paste in the forum (it used to work perfectly before)
| Buy Date | Buy Quantity | Buying Rate | Total Buying Amount | Sell Date | Sell Quantity | Selling Rate | Total Selling Amount |
01-Feb-2021 | 500 | 88.0000 | 44,000.0000 | 09-Feb-2020 | 500 | 84.0100 | 42,005.0000 |
01-Feb-2021 | 4 | 240.0000 | 960.0000 | 02-Feb-2020 | 4 | 246.5000 | 986.0000 |
03-Feb-2021 | 165 | 252.0000 | 41,580.0000 | 11-Feb-2021 | 165 | 256.0000 | 42,240.0000 |
09-Feb-2021 | 500 | 29.0000 | 14,500.0000 | 02-Mar-2021 | 500 | 25.7000 | 12,850.0000 |
09-Feb-2021 | 100 | 220.0000 | 22,000.0000 | 09-Feb-2021 | 100 | 223.9500 | 22,395.0000 |
09-Feb-2021 | 100 | 220.0000 | 22,000.0000 | 01-Mar-2021 | 100 | 230.0000 | 23,000.0000 |
10-Feb-2021 | 500 | 7.7000 | 3,850.0000 | 25-Feb-2021 | 500 | 8.4000 | 4,200.0000 |
12-Feb-2021 | 100 | 244.0000 | 24,400.0000 | 16-Feb-2021 | 100 | 236.0000 | 23,600.0000 |
15-Feb-2021 | 100 | 134.0000 | 13,400.0000 | 02-Mar-2021 | 100 | 135.3400 | 13,534.0000 |
16-Feb-2021 | 500 | 7.0000 | 3,500.0000 | 25-Feb-2021 | 500 | 7.8800 | 3,940.0000 |
16-Feb-2021 | 3,000 | 7.4000 | 22,200.0000 | 25-Feb-2021 | 3,000 | 7.8800 | 23,640.0000 |
25-Feb-2021 | 500 | 145.0000 | 72,500.0000 | 02-Mar-2021 | 500 | 149.0000 | 74,500.0000 |
25-Feb-2021 | 500 | 146.0000 | 73,000.0000 | 02-Mar-2021 | 500 | 149.0000 | 74,500.0000 |
25-Feb-2021 | 14,000 | 7.1500 | 100,100.0000 | 25-Feb-2021 | 14,000 | 7.8800 | 110,320.0000 |
02-Mar-2021 | 500 | 149.0000 | 74,500.0000 | 09-Mar-2021 | 500 | 137.5000 | 68,750.0000 |
02-Mar-2021 | 122 | 149.0000 | 18,178.0000 | 11-Mar-2021 | 122 | 122.0200 | 14,886.4400 |
02-Mar-2021 | 378 | 149.0000 | 56,322.0000 | 31-Mar-2021 | 378 | 159.5000 | 60,291.0000 |
02-Mar-2021 | 2 | 884.0000 | 1,768.0000 | 03-Mar-2021 | 2 | 881.0000 | 1,762.0000 |
02-Mar-2021 | 2,500 | 7.5000 | 18,750.0000 | 19-Mar-2021 | 2,500 | 7.6500 | 19,125.0000 |
03-Mar-2021 | 3,000 | 7.7000 | 23,100.0000 | 19-Mar-2021 | 3,000 | 7.6500 | 22,950.0000 |
04-Mar-2021 | 18 | 238.5000 | 4,293.0000 | 05-Mar-2021 | 18 | 241.0000 | 4,338.0000 |
08-Mar-2021 | 500 | 7.8000 | 3,900.0000 | 19-Mar-2021 | 500 | 7.6500 | 3,825.0000 |
09-Mar-2021 | 500 | 22.8000 | 11,400.0000 | 06-Apr-2021 | 500 | 23.5000 | 11,750.0000 |
09-Mar-2021 | 5,000 | 6.8500 | 34,250.0000 | 19-Mar-2021 | 5,000 | 7.6500 | 38,250.0000 |
10-Mar-2021 | 3,000 | 6.9000 | 20,700.0000 | 19-Mar-2021 | 3,000 | 7.6500 | 22,950.0000 |
12-Mar-2021 | 400 | 228.0000 | 91,200.0000 | 12-Mar-2021 | 400 | 233.0000 | 93,200.0000 |
12-Mar-2021 | 500 | 245.0000 | 122,500.0000 | 12-Mar-2021 | 500 | 248.0000 | 124,000.0000 |
15-Mar-2021 | 1,000 | 86.5000 | 86,500.0000 | 16-Mar-2021 | 1,000 | 88.0000 | 88,000.0000 |
15-Mar-2021 | 100 | 228.0000 | 22,800.0000 | 16-Mar-2021 | 100 | 233.0000 | 23,300.0000 |
15-Mar-2021 | 500 | 198.0000 | 99,000.0000 | 17-Mar-2021 | 500 | 211.5000 | 105,750.0000 |
15-Mar-2021 | 1,500 | 7.2000 | 10,800.0000 | 19-Mar-2021 | 1,500 | 7.6500 | 11,475.0000 |
16-Mar-2021 | 500 | 87.5000 | 43,750.0000 | 16-Mar-2021 | 500 | 88.5000 | 44,250.0000 |
16-Mar-2021 | 2,000 | 22.7000 | 45,400.0000 | 06-Apr-2021 | 2,000 | 23.5000 | 47,000.0000 |
16-Mar-2021 | 2,000 | 27.0500 | 54,100.0000 | 28-Oct-2022 | 2,000 | 18.2500 | 36,500.0000 |
17-Mar-2021 | 500 | 16.9000 | 8,450.0000 | 05-Jul-2021 | 500 | 18.0000 | 9,000.0000 |
17-Mar-2021 | 1,540 | 67.7500 | 104,335.0000 | 18-Mar-2021 | 1,540 | 67.8000 | 104,412.0000 |
17-Mar-2021 | 60 | 67.2000 | 4,032.0000 | 18-Mar-2021 | 60 | 67.8000 | 4,068.0000 |
19-Mar-2021 | 100 | 576.0000 | 57,600.0000 | 25-Mar-2021 | 100 | 600.0000 | 60,000.0000 |
19-Mar-2021 | 100 | 568.0000 | 56,800.0000 | 25-Mar-2021 | 100 | 600.0000 | 60,000.0000 |
19-Mar-2021 | 100 | 558.0000 | 55,800.0000 | 25-Mar-2021 | 100 | 600.0000 | 60,000.0000 |
24-Mar-2021 | 500 | 95.0000 | 47,500.0000 | 05-Apr-2021 | 500 | 78.0000 | 39,000.0000 |
25-Mar-2021 | 1,007 | 166.5000 | 167,665.5000 | 31-Mar-2021 | 1,007 | 160.1000 | 161,220.7000 |
25-Mar-2021 | 93 | 166.5000 | 15,484.5000 | 31-Mar-2021 | 93 | 160.2500 | 14,903.2500 |
30-Mar-2021 | 100 | 340.0000 | 34,000.0000 | 0 | 0.0000 | 0.0000 | |
30-Mar-2021 | 20 | 5,850.0000 | 117,000.0000 | 14-Sep-2021 | 20 | 6,155.0100 | 123,100.2000 |
