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.

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:

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:


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 -

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