Open "Tijori" and Collect Financial Gem - A way to improve real time personal finance experience

Being a part of the Financial Industry my typical day involves working with plenty of financial data. One of the crucial tasks that I perform is to put data from financial statements to excel templates. People involved in similar work knows, it is a very cumbersome work. However, there is a website that will help you to do this task with little ease. The name of the website is www.tijorifinance.com. Those who are looking for financial data from different companies can refer to this site for sure. They have 10 years of data for all major financial items. However, It is not enough to have the data only on the website. Copying data from the web page to an excel sheet can be very messy. I am here to save you some time on that. The purpose of this article is to educate people about VBA and selenium and their use and make you happy and reduce some work.

There are a lot of ways to track companies that one wants to invest in. You can spend money and hire a financial adviser, or you can track them by yourself. Now tracking and understanding the financial market is tough especially for people of non-finance backgrounds. We have a long way to go. But before we do anything, we first need to collect the data. This first post will at least help you to do that without putting much effort.

Let's now talk about the website. The primary page looks like the below image. Here, you can enter the name of any Indian company and you will get a lot of information about the same.

However, we are only concerned about the financials (available under the financial tab after you enter a company and the search result appears). With the use of VBA and Selenium, we will extract data from the financial tab. We will extract all the data underlined in blue in the below image. However, I have not extracted the headings of each section, like the name of the company from the benchmark section, or years for Financial Statements sections. Anyone interested to explore the below code, I have kept this section for you.

To apply the following codes explained below, one needs to install selenium in their system. I would recommend the below blog post for selenium installation.

Before we dive into the code, I would like to describe the file that I have used for the code. The file, on which the below code is based, have two sheets, Sheet2 and Sheet1. Both of them are required if you want to follow this exact code. The final result will appear in the Sheet1. In the Sheet2 cell E2 I have pasted the link of the company I intend to scrap. If you look at the link, you will understand how to copy when you will use the file. Here, in this example, I am scraping "Reliance Industries". The result will only give the data for Standalone Financial Statements for now. Later I will add codes for Consolidated Statements and headings that I have left for the enthusiastic readers. The excel file with my setup looks like the below image.

There is no guarantee that the code will definitely work in your system. At the time of this post, it is working fine in my system. The result might not look exactly like you have intended as I didn't get enough time to try on different companies. The purpose of this article is to teach people. The business use of these codes written below is prohibited. I don't claim the authenticity of any data gathered through the below codes. Anyone from "Tijori" can contact me if they think I have violated any policy. I will take this post down. If you guys face any issue, please comment or drop an email to [email protected]. I am not pro in VBA, any improvement in the code is also welcome. Let's grow together.

    End If
    
    Next elm
    
    Set elms = bot.FindElementsByTag("tr")
    
    For Each elm In elms
    If InStr(1, elm.Text, "+") Then
    elm.Click
    
    End If
    Next elm
    
    Dim t As String
    t = 0
    Set elms = bot.FindElementsByTag("td")
    For Each elm In elms
    t = t + 1
    Sheet2.Range("A" & t).Value = elm.Text
    Next elm
    Call copy
    MsgBox "Done"
End Sub

Sub copy()
Dim k As Long
Dim c As Long, r As Long
c = 1
k = 1
On Error Resume Next
For r = 1 To Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
    If (IsNumeric(Sheet2.Range("A" & r).Value) = False) Then
    If Sheet2.Range("A" & r).Value = "n/a" Then GoTo mido
    If InStr(1, Sheet2.Range("A" & r), "Rs") > 0 Then GoTo mido
    If InStr(1, Sheet2.Range("A" & r), "Lakh") > 0 Then GoTo mido
    If InStr(1, Sheet2.Range("A" & r), "(Cr") > 0 Then GoTo prev
    If InStr(1, Sheet2.Range("A" & r), "Cr") > 0 Then GoTo mido
    If InStr(1, Sheet2.Range("A" & r), "$/BL") > 0 Then GoTo mido
prev:
    c = 1
    Sheet1.Cells(k, c).Value = Sheet2.Range("A" & r).Value
    k = k + 1
    GoTo lst


    Else
mido:
    Sheet1.Cells(k - 1, c + 1).Value = Sheet2.Range("A" & r).Value
    c = c + 1
    End If
lst:
Next r
Call ladd


End Sub


Sub ladd()

Dim k As Long
Dim c As Long, r As Long
On Error Resume Next
    For r = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row To 1 Step -1


    If InStr(1, Sheet1.Cells(r, 1).Value, "Net Sales") > 0 Then
    Sheet1.Cells(r, 1).EntireRow.Insert
    Sheet1.Cells(r, 1).Value = "Quaterly Result"
    Sheet1.Cells(r, 1).Interior.ColorIndex = 37
    GoTo Nxt
    End If
    Next r
Nxt:
    k = r
    For r = k To 1 Step -1
    If InStr(1, Sheet1.Cells(r, 1).Value, "Operational Ratios -") > 0 Then
    Sheet1.Cells(r, 1).EntireRow.Insert
    Sheet1.Cells(r, 1).Value = "Ratios"
    Sheet1.Cells(r, 1).Interior.ColorIndex = 37
    GoTo Nxt1
    End If
    Next r
Nxt1:
    k = r
    For r = k To 1 Step -1
    If InStr(1, Sheet1.Cells(r, 1).Value, "Cash from Operating Activity") > 0 Then
    Sheet1.Cells(r, 1).EntireRow.Insert
    Sheet1.Cells(r, 1).Value = "Cash Flow"
    Sheet1.Cells(r, 1).Interior.ColorIndex = 37
    GoTo Nxt2
    End If
    Next r
Nxt2:
    k = r
    For r = k To 1 Step -1
    If InStr(1, Sheet1.Cells(r, 1).Value, "Sales") > 0 Then
    Sheet1.Cells(r, 1).EntireRow.Insert
    Sheet1.Cells(r, 1).Value = "Profit & Loss"
    Sheet1.Cells(r, 1).Interior.ColorIndex = 37
    GoTo Nxt3
    End If
    Next r
Nxt3:
    k = r
    For r = k To 1 Step -1
    If InStr(1, Sheet1.Cells(r, 1).Value, "Non Current Assets -") > 0 Then
    Sheet1.Cells(r - 1, 1).EntireRow.Insert
    Sheet1.Cells(r - 1, 1).Value = "Balance Sheet"
    Sheet1.Cells(r - 1, 1).Interior.ColorIndex = 37
    GoTo Nxt4
    End If
    Next r
Nxt4:
    Sheet1.Range("A1").EntireRow.Insert


End Sub

The final result will look like the below image. Each column with data represents each year mentioned in the website.
vI hae attached a image in the beginning to compare both the website and my result side by side. Hope this will help few people. Please provide feedback. Cheers!!!