Electoral Bonds is my favorite hot topic these days and I have been following developments on this issue with keen interest. Early in March when Supreme Court asked SBI to provide the information about who donated to whom, SBI asked more than three months’ extra time to furnish the required details. It said that 22 thousand bonds had been issued to various political parties between April 2019 and February 15 this year and the data about the issued and redeemed bonds was in two silos. It needs time to correlate the information from these two silos.
However, the Supreme Court insisted that SBI releases the data in two separate silos as it is, without trying to correlate it. After a little push from SC, SBI finally provided this Electoral Bond data to EC yesterday and it was published as two PDFs on EC website:
- Bonds Issue details - 386 Pages
- Bonds Redemption details - 551 Pages
Anyone interested in Data analysis will be tempted by these large data sets to try this challenging exercise with a goal to determine who donated how much and to which party.
I also tried my hand at it in Microsoft Excel with the following steps:
1. Process the Bonds Issue details PDF
- Load the data from the PDF file in a table in PowerQuery.
- Each bond has a Prefix (eg TL) and a Bond Number (eg 11448). Create unique Bond id for each bond by combining these two columns. eg TL-11448.
- Keep only the following columns in the Bonds Issue table, removing the rest:
- Bond id
- Name of the Purchaser
- Date of Purchase
- Denominations
2. Process the Bonds Redemption details PDF
- Load the data from the PDF file in a table in PowerQuery.
- Each bond has a Prefix (eg TL) and a Bond Number (eg 11448). Create unique Bond id for each bond by combining these two columns. eg TL-11448.
- Keep only the following columns in the Bonds Redemption Table, removing the rest:
- Bond id
- Name of the Political Party
- Date of Encashment
- Denominations
3. Merge the two Tables
Merge Bonds Redemption Table and Bonds Issue table with unique Bond id field. With this, you get a table with the following columns:
- Bond id
- Name of the Political Party
- Date of Encashment
- Denominations
- Name of the Purchaser
- Date of Purchase
- Issue.Denominations
This table has all the desired information we are looking for. It was really an interesting exercise, and just for record, it took about two hours to complete it. Last night most newspapers did similar exercise and published the results.
I am still wondering what made SBI overestimate the effort involved by such a large factor.
