calculating stock market bonus shares

[mwvirk]

hello

I am calculating how many bonus shares (Flag marked as B) I have received from the stock market for each of my stocks
and then also checking if I sell some bonus shares then how many are still in my portfolio

in this excel file, I have 2 sheets
sheet1 is for buying, selling and bonus shares received
sheet2 is to check the status that how many bonus shares were received and how many are left. if all are sold then I should get 0 in sheet2 cell D
(but in all cases, I want to keep the actual bonus shares received in sheet2 cell C)

I have multiple issues here in this sheet2:

1- if the cell H in sheet1 is blank then cell D in sheet2 is not counting the total (i should have a value of 0 at least in sheet1 cell H to get the result since the blank cell is not showing the count in sheet2 cell D)
2- if I partially or fully sell bonus shares in sheet1 then it's showing balance zero in all cases in sheet2 cell D (i am checking here to know how many are still in my portfolio)

kindly check and help.

thanks

Test-Copy.xlsx
	A	B	C	D	E	F	G	H
1	Stock name		Flag			Received		Sold
2	Apple		R			100		0
3	Google		R			150		0
4	Intel		R			200		0
5	Facebook		R			250		0
6	Apple		B			10		5
7	Google		B			15		0
8	Intel		B			20		0
9	Facebook		B			25		0
10	Apple		R			200		0
11	Google		R			250		0
12	Intel		R			300		0
13	Facebook		R			350		0
14	Apple		B			15
15	Google		B			20
16	Intel		B			25
17	Facebook		B			30
Sheet1

Test-Copy.xlsx
	A	B	C	D
1	Apple		25	0
2	Google		35	15
3	Intel		45	20
4	Facebook		55	25
Sheet2

Cell Formulas
Range	Formula
C1:C4	C1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B")
D1:D4	D1	=SUMIFS(Sheet1!F:F,Sheet1!A:A,A1,Sheet1!C:C,"B",Sheet1!H:H,0)

 

[KRice]

Thank you...that helps. I was clear on most of this. But my question is not about the structure of a candidate's name or the party. Rather the question is about whether the same candidates must appear on the ballots in all of the numbered areas for the same two-letter code. For example, if Michael Steven John appears on the ballot for NA-001, must he also appear on the ballot for NA-002, NA-003, NA-004, etc.,..or is it possible that each numbered area has a slightly different list of candidates? I am asking this because it changes how a combination table needs to be built, or whether this approach is even viable. If exactly the same candidate/parties appear on the ballot for each NA numbered area that becomes easier to do because the candidate/party group for every NA# would be the same, and this repetition could be handled automatically. But if the candidates can vary among different NA#'s, then this idea would not work. I am not sure you have answered this specific question but I think that perhaps the candidates can be different among different NA#'s?

 

[mwvirk]

if Michael Steven John appears on the ballot for NA-001, must he also appear on the ballot for NA-002, NA-003, NA-004, etc

if Michael Steven John appears for NA-001 then there are chances that he can be in any other NA multiple times. It may be in NA-002 as well
it is not important that if he is in NA-001 then he MUST appear in NA-002 - it can be any random number
he can be in NA-001 and might be in NA-002 as well as in any other NA e.g. (NA-001 - NA-002 - NA-007 - NA-090 - NA-180 - NA-200 - NA-260 any NA)
he can be in NA-001 - NA-002 - NA-010 - NA-240 - NA-260 and the same time in PP-001 - PP-020 - PP-100 - PB-003 - PB-010 - PS-100 - PS-200 - KP-020 - KP-150 - KP-200

suppose you are in America (Country). and you have multiple states New york, New Jersey, Chicago, Baltimore, and Washington (for me these are NA)
now in Baltimore (NA), you have multiple areas. Maryland, ABC, DEF, XYZ (for me these are provinces)

now KRice is a candidate for the Republican Party. now KRice is participating on behalf of the Republican Party from different NA (New York and Baltimore etc)
then KRice is also a candidate for the Republican Party from Maryland (the name is anything in my case. it can be PP PB PS KP)
then KRice can also be a candidate for the Republican Party from any area (province) of the New York

any party is having maximum winners from NA will be the rolling party in the country
any party is having maximum winner from PP will be the rolling party for the country for that province PP only
any party is having maximum winner from PS will be the rolling party for the country for that province PS only
any party is having maximum winner from PB will be the rolling party for the country for that province PB only
any party is having maximum winner from KP will be the rolling party for the country for that province KP only

 

[mwvirk]

if i have 300 NA#
and each NA# i am assuming 30 candidates
then physically there will be 9,000 candidates

same applies for PP PB PS KP

 

[mwvirk]

candidates can vary among different NA#'s

yes

candidates can be different among different NA#'s

yes

 

[KRice]

Thank you...that clears up my question. So constructing combinations--like I was envisioning--will not work because the ballots (the list of candidates and their parties) for the NA#'s could differ from one NA# to another. Wow!...constructing your initial list of candidates/parties becomes very messy, as each ballot (for each NA#) may differ. I think what confused me was the initial example where Candidate Name 001, etc. appeared for each NA# and I assumed "Candidate Name 001" referred to a specific person or every ballot (for NA-001, NA-002, etc.), rather than being a placeholder for different candidates.

 

[mwvirk]

Do you think we should roll back the recent changes? since it's taking full CPU utilization and taking a long time to just input 1 single cell?
- We can remove the newly inserted row 'J'
- un-merge the A row and either uses NA# once on top of the record A2 for NA-001 and then A32 for NA-002
- un-merge O P Q R and keep the winner name on O2 for candidate name for NA-001 P2 for party name then Q2 R2 and for the next record (NA-002) use the O32 P32 Q32 R32
- remaining cell O3:O31 P3:P31 Q3:Q31 R3:R31 can be ignored or anything for not being used?

earlier we were having issues that everything was working fine but only the condition check was not working.
now everything is perfect and working fine but the system is hanging with just 1 sheet of 8161 rows

 

[KRice]

I believe my earlier idea about using repetition of a candidate list to populate the vote tabulation table is flawed because, as you have explained, the candidate lists can vary between Regions (NA, PP, etc.) and even between Area Codes within the same Region (e.g., NA-001, NA-002, etc.). I suspect your method for creating the list of candidates on each ballot must be done manually. If that is the case, I have a recommendation. Earlier you mentioned that separate lists were maintained for each region (NA, PP, etc.). Consider converting each of those lists to an official Excel table (click in the list and hit Ctrl-t) and then give each of the tables a meaningful name. In the example I have created, I used names of tblNA, tblPP, etc. Use those tables for your vote tabulations...there is no need to combine the tables just yet, and maintaining the votes in each table might prove to be easier. In my example, I have a formula for each of these tables to flag if any vote tallies are missing (then the "Blanks" cell shows a non-zero number and it turns red).

After all votes have been tallied (and the "Blanks" checks are all green), then the summary table can be completed. I have a simple Power Query script that combines all of the individual vote tabulation tables into a single table similar to your original one. This table occupies columns Q:U, but it uses no in-cell formulas. The instructions to combine the tables and perform the [% of Candidate Votes] computation are in the M code:

let
    NA = Excel.CurrentWorkbook(){[Name="tblNA"]}[Content],
    PP = Excel.CurrentWorkbook(){[Name="tblPP"]}[Content],
    PS = Excel.CurrentWorkbook(){[Name="tblPS"]}[Content],
    PB = Excel.CurrentWorkbook(){[Name="tblPB"]}[Content],
    KP = Excel.CurrentWorkbook(){[Name="tblKP"]}[Content],
    tbl1 = NA & PP & PS & PB & KP,
    #"Changed Type" = Table.TransformColumnTypes(tbl1,{{"Votes", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Region-Area Code"}, {{"Region-Area Total Votes", each List.Sum([Votes]), type number}, {"All", each _, type table [#"Region-Area Code"=text, Candidate Name=text, Party Name=text, Votes=number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Candidate Name", "Party Name", "Votes"}, {"Candidate Name", "Party Name", "Votes"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "% of Candidate Votes", each [Votes] / [#"Region-Area Total Votes"] ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Region-Area Total Votes"})
in
    #"Removed Columns"

The table produced by this Power Query script is another official Excel table that is loaded onto the summary sheet in cell Q1 (upper left). There are mostly advantages with this "table" being an official table, because formulas can use structured references, which dynamically adjust to the length of the table. The biggest disadvantage is that Excel does not allow for structured references in formulas for Conditional Formatting. I investigated several options. The two most promising involved either wrapping all of the structured references with INDIRECT functions or using named ranges. I am generally not fond of using volatile functions (like INDIRECT), and while it worked okay in my tests, performance may be a problem as the table grows. Therefore, I adopted Named Ranges, where the table columns for [Region-Area Code] and [Votes] were named aryRACodes and aryVotes, respectively, using a dynamic range formula based on INDEX and employing a helper cell that identifies the bottom row of the table (cell AA2). Using these named ranges, conditional formatting is applied to the table to highlight the winner and runner-up. Another conditional formatting formula inserts a black cell border between different groups of Region-Area Codes.

Another Power Query script creates a simple one-column table (column W) showing the unique Region-Area Codes that are found in the combined vote tabulation table. This single column table can be used to confirm that all of them are present in the main summary table (columns A:O). I have inserted a formula to make this check in Y1:Y2 (if a Region-Area code is missing, a "Problem" will be flagged). Similarly, the blank votes checks performed on each of the region vote tally tables are examined in Z1:Z2 to report if any blanks exist. These cells (Y1:Z2) provide a convenient check before the combined vote tabulation table is refreshed. This table is not dynamic...it needs to be refreshed (I recommend using Date > Refresh All to refresh both PQ queries).

I rearranged some columns in the main summary table so that everything manually entered is on the left and everything delivered by formulas on the right. The formulas have been revised to use structured references.

Mrexcel_20220924_mwvirk.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	NA # (2018)	Province	NA # (2013)	Seat Name	Registered Voters	Rejected Votes	Valid Cast Votes	Total Cast Votes	% Of Total Registered Votes	Winner's Name	Winner Party	Runner up Name	Runner up Party	Previous Winner's Name In 2018	Previous Party Name In 2018
2	NA-001	Kingdom Valley	Blue Area 1	Blue Area 1A	318150	500	7000	7500	2.357%	Name 0004	Party AD	Name 0002	Party AB
3	NA-002	Kingdom Valley	Blue Area 2	Blue Area 2B	318150	500	4300	4800	1.509%	Name 0001	Party AA	Name 0002	Party AB
4	NA-003	Kingdom Valley	Blue Area 3	Blue Area 3C	318150	500	11000	11500	3.615%	Name 0006	Party AF	Name 0003	Party AC
5	PB-001	Province-2	Name-004	Area-004			4000	4000		Name I	Party AF	Name I	Party AF
6	PB-002	Province-2	Name-005	Area-005			2000	2000		Name F	Party AD	Name F	Party AD
7	PP-001	Province-3	Name-006	Area-006			3000	3000		Name F	Party AD	Name F	Party AD
8	PP-002	Province-4	Name-007	Area-007			4000	4000		Name F	Party AD	Name F	Party AD
9	PP-003				318150	500	3700	4200	1.320%	Name M	Party AD	Name 0009	Party AI
10	PS-001						5030	5030		Name J	Party AG	Name H	Independent
11	PS-002				318150	500	9000	9500	2.986%	Name J	Party AG	Name K	Party AH
12	KP-001				318150	500	3002	3502	1.101%	Name 0007	Party AG	Name 0005	Party AE
13	KP-002				318150	500	2001	2501	0.786%	Name 0008	Party AH	Name 0009	Party AI
14	KP-003				318150	500	3900	4400	1.383%	Name 0007	Party AG	Name 0006	Party AF
Election 2022_v4

Cell Formulas
Range	Formula
G2:G14	G2	=SUMIF(tblAll[Region-Area Code],$A2,tblAll[Votes])
H2:H14	H2	=SUM($F2:$G2)
I2:I14	I2	=IF($E2=0,"",$H2/$E2)
J2:J14	J2	=IFERROR(INDEX(tblAll[Candidate Name],AGGREGATE(15,6,(ROW(tblAll)-ROW(tblAll[#Headers]))/((tblAll[Region-Area Code]=$A2)*(tblAll[Votes]=(LARGE((tblAll[Votes])*(tblAll[Region-Area Code]=$A2),1)))),1)),"")
K2:K14	K2	=IFERROR(INDEX(tblAll[Party Name],AGGREGATE(15,6,(ROW(tblAll)-ROW(tblAll[#Headers]))/((tblAll[Region-Area Code]=$A2)*(tblAll[Votes]=(LARGE((tblAll[Votes])*(tblAll[Region-Area Code]=$A2),1)))),1)),"")
L2:L14	L2	=IFERROR(INDEX(tblAll[Candidate Name],AGGREGATE(15,6,(ROW(tblAll)-ROW(tblAll[#Headers]))/((tblAll[Region-Area Code]=$A2)*(tblAll[Votes]=(LARGE((tblAll[Votes])*(tblAll[Region-Area Code]=$A2),2)))),1)),"")
M2:M14	M2	=IFERROR(INDEX(tblAll[Party Name],AGGREGATE(15,6,(ROW(tblAll)-ROW(tblAll[#Headers]))/((tblAll[Region-Area Code]=$A2)*(tblAll[Votes]=(LARGE((tblAll[Votes])*(tblAll[Region-Area Code]=$A2),2)))),1)),"")

Named Ranges
Name	Refers To	Cells
'Election 2022_v4'!aryRACodes	='Election 2022_v4'!$Q$2:INDEX('Election 2022_v4'!$Q:$Q,'Election 2022_v4'!$AA$2)	J2:M14, G2:G14
'Election 2022_v4'!aryVotes	='Election 2022_v4'!$T$2:INDEX('Election 2022_v4'!$T:$T,'Election 2022_v4'!$AA$2)	J2:M14, G2:G14
'Election 2022_v4'!ExternalData_1	='Election 2022_v4'!$Q$1:$U$45	J2:M14

Here is a portion of the combined vote tabulation table and the helper cells:

Mrexcel_20220924_mwvirk.xlsx
	Q	R	S	T	U	V	W	X	Y	Z	AA
1	Region-Area Code	Candidate Name	Party Name	Votes	% of Candidate Votes		Region-Area Code		Mismatched RA Codes	Blank Vote Tallies	Keep: helper bottom row
2	NA-001	Name 0001	Party AA	1000	14.29%		KP-001		Okay	Okay	45
3	NA-001	Name 0002	Party AB	2000	28.57%		KP-002
4	NA-001	Name 0003	Party AC	1000	14.29%		KP-003
5	NA-001	Name 0004	Party AD	3000	42.86%		NA-001
6	NA-002	Name 0001	Party AA	1200	27.91%		NA-002
7	NA-002	Name 0002	Party AB	1100	25.58%		NA-003
8	NA-002	Name 0003	Party AC	1000	23.26%		PB-001
9	NA-002	Name 0004	Party AD	1000	23.26%		PB-002
10	NA-003	Name 0005	Party AE	1000	9.09%		PP-001
11	NA-003	Name 0006	Party AF	5000	45.45%		PP-002
12	NA-003	Name 0003	Party AC	4000	36.36%		PP-003
13	NA-003	Name 0004	Party AD	1000	9.09%		PS-001
14	PP-001	Name F	Party AD	1000	33.33%		PS-002
15	PP-001	Name G	Party AE	1000	33.33%
16	PP-001	Name H	Independent	1000	33.33%
17	PP-002	Name F	Party AD	1000	25.00%
Election 2022_v4

Cell Formulas
Range	Formula
Y2	Y2	=IF(SUM(--NOT(ISNUMBER(MATCH(tblRACodes[Region-Area Code],A2:A20,0))))>0,"Problem","Okay")
Z2	Z2	=IF(SUM('Election 2022_v4_ballots'!A2:X2)>0,"Problem","Okay")
AA2	AA2	=ROWS(tblAll[#Data])+ROW(tblAll[#Headers])

Named Ranges
Name	Refers To	Cells
'Election 2022_v4'!aryRACodes	='Election 2022_v4'!$Q$2:INDEX('Election 2022_v4'!$Q:$Q,'Election 2022_v4'!$AA$2)	AA2
'Election 2022_v4'!aryVotes	='Election 2022_v4'!$T$2:INDEX('Election 2022_v4'!$T:$T,'Election 2022_v4'!$AA$2)	AA2
'Election 2022_v4'!ExternalData_1	='Election 2022_v4'!$Q$1:$U$45	AA2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:U45	Expression	=$Q3<>$Q2	text	NO
T2:U45	Expression	=AGGREGATE(14,6,aryVotes/(aryRACodes=$Q2),1)=$T2	text	NO
T2:U45	Expression	=AGGREGATE(14,6,aryVotes/(aryRACodes=$Q2),2)=$T2	text	NO

And an example of the individual regional vote tally tables:

Mrexcel_20220924_mwvirk.xlsx
	A	B	C	D	E	F	G	H	I
1
2			Blanks	0				Blanks	0
3
4	Region-Area Code	Candidate Name	Party Name	Votes		Region-Area Code	Candidate Name	Party Name	Votes
5	NA-001	Name 0001	Party AA	1000		PP-001	Name F	Party AD	1000
6	NA-001	Name 0002	Party AB	2000		PP-001	Name G	Party AE	1000
7	NA-001	Name 0003	Party AC	1000		PP-001	Name H	Independent	1000
8	NA-001	Name 0004	Party AD	3000		PP-002	Name F	Party AD	1000
9	NA-002	Name 0001	Party AA	1200		PP-002	Name G	Party AE	1000
10	NA-002	Name 0002	Party AB	1100		PP-002	Name H	Independent	1000
11	NA-002	Name 0003	Party AC	1000		PP-002	Name M	Party AD	1000
12	NA-002	Name 0004	Party AD	1000		PP-003	Name 0008	Party AH	1000
13	NA-003	Name 0005	Party AE	1000		PP-003	Name 0009	Party AI	1300
14	NA-003	Name 0006	Party AF	5000		PP-003	Name M	Party AD	1400
15	NA-003	Name 0003	Party AC	4000
16	NA-003	Name 0004	Party AD	1000
17
Election 2022_v4_ballots

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(tblNA[Votes],"")
I2	I2	=COUNTIF(tblPP[Votes],"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2,I2,N2,S2,X2	Expression	=D$2=0	text	NO
D2,I2,N2,S2,X2	Expression	=D$2<>0	text	NO

Setting this up may be complicated, so the working file I used is available here (and it includes past worksheet iterations/formulas). I would recommend looking at the last two worksheets from which the above content was taken.

Mrexcel_20220924_mwvirk.xlsx

Shared with Dropbox

www.dropbox.com

 

[mwvirk]

Thank you so much and appreciate your kind support.
This is working perfectly now and I have updated my entire sheet.

Regards,
MWVirk

 

[KRice]

You're welcome. I'm happy to help.