Determine interdependent individuals on board memberships using python
Definition of interdependent persons according to Art. 105.1 of the Tax Code of the Russian Federation is a rather trivial task for a corporate lawyer.
Usually this is straightforward and it seems, why is there something to automate?
That's right, if a society two or three compare their composition of diabetes there is no problem. But, if there are more than two dozen companies and at the same time different companies have different composition of directors, including the number of members? Here you already have to spend time. We will spend it on a program that will calculate related parties for us, analyzing the composition of the board of directors.
Recall that interdependent persons are determined by a number of criteria given in the Tax Code of the Russian Federation.
In paragraph 2 of Art. 105.1 of the Tax Code of the Russian Federation lists the grounds according to which individuals are recognized as interdependent. So, for tax purposes, interdependent persons are:
There are many reasons, but we are interested in one of them, namely:
- organizations in which more than 50% of the board of directors are the same individuals.
This basis differs from the rest in that the lawyer needs to compare all the composition of the board of directors in the companies with a coincidence of more than 50% of their members.
Suppose all the composition of the Board of Directors we have summarized in an Excel table with boards of directors and a total number of companies of more than twenty:
We need our program, following the table, to select companies if there is a coincidence of more than 50% of the total number of board members. Next, the program should display the result either on the screen or in a file.
Logically, the program will execute the following algorithm. Having received the name of the directors from the user, she will compare them with each line of Excel, which contains the name of the directors for each company. That is, every three out of 5 user SD members are compared with every 3 out of 5 in the table. At this stage, the program will not analyze CDs with more than 5 members, skipping them.
The first lines of our program will be standard:
Here we imported the module for working with Excel and opened the file with our table.
Now we will offer the user to enter five members of the Board of Directors, whom the program will further search in societies for comparison. We write all the user-entered members of the SD in the variables a, b, c, e, f:
This is an important point. The name of the director must be entered without spaces, for example, Ivanov I.I. In the same form, the names of the directors must be present in the Excel table.
Let's create two lists. One is empty, the second is with societies where the Board of Directors has more than 5 members:
We also opened the text file 55555.txt, in which we will save the results. The coincidence of the SD by more than 50% means that 3 members out of 5 should match. So the program should make 10 checks for matching names. That is how many combinations can be for diabetes from 5 members.
Therefore, in order not to write the same code 10 times, making the program cumbersome, we will create a function and then call (execute) it 10 times.
Here is our function:
Let's analyze the code.
We set the boundaries of the table for B2: L36. If the cell is empty, it will be skipped.
Next, the function will consider the name of the 1st director, if the name in the cell corresponds to the name entered by the user, the function goes further and compares the 2nd, then the 3rd director. At the end, the function forms a list of companies and writes each of them to the file 55555.txt.
After a function is written, it will not be automatically executed by the program.
It must be called:
But here we called the function only for the first three directors. It is necessary to repeat the function call for the remaining combinations:
Run the program and enter in the python interpreter window any 5 names of directors from
those that are found at least once in the excel table:
After the program is executed, look at the file that the program will create -
55555.txt:
In the file - numbers of the companies where matches were found. It should be noted that these are society numbers from column A, not Excel line numbers:
The program is ready, but it must be remembered that it does not analyze the composition of the board with members of more than 5 people!
Download the program code - here .
Download the test chart here .
PS: any matches in the table with real personalities are random.
Usually this is straightforward and it seems, why is there something to automate?
That's right, if a society two or three compare their composition of diabetes there is no problem. But, if there are more than two dozen companies and at the same time different companies have different composition of directors, including the number of members? Here you already have to spend time. We will spend it on a program that will calculate related parties for us, analyzing the composition of the board of directors.
A few definitions
Recall that interdependent persons are determined by a number of criteria given in the Tax Code of the Russian Federation.
In paragraph 2 of Art. 105.1 of the Tax Code of the Russian Federation lists the grounds according to which individuals are recognized as interdependent. So, for tax purposes, interdependent persons are:
- organization, if one organization directly and (or) indirectly participates in another organization and the proportion of such participation is more than 25%;
- an individual and an organization if such an individual directly and (or) indirectly participates in such an organization and the proportion of such participation is more than 25%;
- organizations, if the same person directly and (or) indirectly participates in these organizations and the proportion of such participation in each organization is more than 25%;
- organization and person (including an individual together with his spouse), parents (including adoptive parents), children (including adopted), half-brothers and siblings, guardians (trustees) and wards), with authority by appointment (election) of the sole executive body of this organization or by appointment (election) of at least 50% of the composition of the collegial executive body or board of directors (supervisory board) of this organization;
- organizations whose sole executive bodies or at least 50% of the collegial executive body or board of directors (supervisory board) are appointed or elected by decision of the same person (individual together with his spouse), parents (including adoptive parents ), children (including adopted), full and half brothers and sisters, guardians (trustees) and wards);
- organizations in which more than 50% of the composition of the collegial executive body or board of directors (supervisory board) are the same individuals together with their spouse, parents (including adoptive parents), children (including adopted), full-fledged and half-brothers and sisters, guardians (trustees) and wards;
- organization and person exercising the powers of its sole executive body;
- organizations in which the powers of the sole executive body are exercised by the same person;
- organizations and (or) individuals in case the share of direct participation of each previous person in each subsequent organization is more than 50%;
- individuals in the event that one individual is subordinate to another individual by official position;
- an individual, his spouse, parents (including adoptive parents), children (including adopted), full and half brothers and sisters, a guardian (trustee) and ward.
Get to the point
There are many reasons, but we are interested in one of them, namely:
- organizations in which more than 50% of the board of directors are the same individuals.
This basis differs from the rest in that the lawyer needs to compare all the composition of the board of directors in the companies with a coincidence of more than 50% of their members.
Suppose all the composition of the Board of Directors we have summarized in an Excel table with boards of directors and a total number of companies of more than twenty:
We need our program, following the table, to select companies if there is a coincidence of more than 50% of the total number of board members. Next, the program should display the result either on the screen or in a file.
Logically, the program will execute the following algorithm. Having received the name of the directors from the user, she will compare them with each line of Excel, which contains the name of the directors for each company. That is, every three out of 5 user SD members are compared with every 3 out of 5 in the table. At this stage, the program will not analyze CDs with more than 5 members, skipping them.
The first lines of our program will be standard:
import openpyxl wb = openpyxl.load_workbook('sd3.xlsx') sheet=wb.get_active_sheet()
Here we imported the module for working with Excel and opened the file with our table.
Now we will offer the user to enter five members of the Board of Directors, whom the program will further search in societies for comparison. We write all the user-entered members of the SD in the variables a, b, c, e, f:
a=str(input("-1: ")) b=str(input("-2: ")) c=str(input("-3: ")) e=str(input("-4: ")) f=str(input("-5: "))
This is an important point. The name of the director must be entered without spaces, for example, Ivanov I.I. In the same form, the names of the directors must be present in the Excel table.
Let's create two lists. One is empty, the second is with societies where the Board of Directors has more than 5 members:
found = [] found2=[1,10,11,12,13,14,18,27,31,32] # sd3, 5 . h = open('55555.txt','a')
We also opened the text file 55555.txt, in which we will save the results. The coincidence of the SD by more than 50% means that 3 members out of 5 should match. So the program should make 10 checks for matching names. That is how many combinations can be for diabetes from 5 members.
Therefore, in order not to write the same code 10 times, making the program cumbersome, we will create a function and then call (execute) it 10 times.
Charge function
Here is our function:
def myfun(x,y,z): for rowOfCellObjects in sheet['B2':'L36']: for cellObj in rowOfCellObjects: if cellObj.value ==None: continue #print (cellObj) if cellObj.value == x: for cellObj in rowOfCellObjects: if cellObj.value == y: for cellObj in rowOfCellObjects: if cellObj.value == z: d = list(cellObj.coordinate) d[0]='A' dd=d[0]+d[1] if len(d)>2: dd=d[0]+d[1]+d[2] i=sheet[str(dd)].value # , 3- q = cellObj.row if i not in found and i not in found2: found.append(i) h.write (str(i)+'\n')
Let's analyze the code.
We set the boundaries of the table for B2: L36. If the cell is empty, it will be skipped.
Next, the function will consider the name of the 1st director, if the name in the cell corresponds to the name entered by the user, the function goes further and compares the 2nd, then the 3rd director. At the end, the function forms a list of companies and writes each of them to the file 55555.txt.
After a function is written, it will not be automatically executed by the program.
It must be called:
myfun(a,b,c)
But here we called the function only for the first three directors. It is necessary to repeat the function call for the remaining combinations:
myfun(a,b,e) myfun(a,b,f) myfun(a,e,f) myfun(a,c,e) myfun(a,c,f) myfun(b,c,e) myfun(b,c,f) myfun(b,e,f) myfun(c,e,f) h.close()
We launch
Run the program and enter in the python interpreter window any 5 names of directors from
those that are found at least once in the excel table:
After the program is executed, look at the file that the program will create -
55555.txt:
In the file - numbers of the companies where matches were found. It should be noted that these are society numbers from column A, not Excel line numbers:
The program is ready, but it must be remembered that it does not analyze the composition of the board with members of more than 5 people!
Download the program code - here .
Download the test chart here .
PS: any matches in the table with real personalities are random.
All Articles