Understand the Private Equity Domain

What is Inside Private Equity Business?

    Private equity business is wonderful business, or one can say its brain game, or I can even say, let your excess money work for other and you also!, I have been developing applications for PE since 
5-6 years. as a Software developer, I have developed different types of business intelligent reports, dashboards, SQL & ETL development for PE Client. as well as I have interest in Stocks and Equity investment, 

   software engineers or professionals who are working in PE domain they must understand this domain, to give more productive output and grow faster in this industry. I tried my best to put Private Equity domain more simple way to understand, even for beginners. We will start from the basics first.

I have divided above article in 5 Topics for better understanding.

1)     Equity Basics

2)     Inside Private Equity business

3)     Types of Private Equity

4)     Fund Operation’s and Security Master

5)     Well known examples, Deals of PE business.

I haven’t written more elaborated descriptions, just to avoid confusion or bouncer balls! my theme is Small and easy to understand.

------------------------------------------------------------------------------------------------------------------------
Lets Start point to point. 
------------------------------------------------------------------------------------------------------------------------

1)     Equity Basics 

 - What is an Equity? First question, this is the first step we should know. We can say an Equity is the wealth of someone!
   Let’s discuss some Basic points of  Equity.

• Equity
• Legal Entity
• Share
• Share Capital
• Security
• Investor & Funds
• Bonds & Debentures

 1)   Equity = Asset Value – Liabilities

Example-  

Asset (Flat 40L + Cash 5L + Car 5L) – Liability (Home Loan 30L + Car Loan 8) = +12Lakh is Equity of Person A.

Asset (Flat 40L + Cash 2L + Car 5L) – Liability (Home Loan 35L + Personal Loan 5 +Car Loan 8) = -1Lakh is Equity of Person B. 

Asset (800cr) – Liability (300cr) = 500Cr is an Equity of K Company.

2) Now what is Legal Entity? – It’s a company or a legal body.

3) Share Capital – Share is a divided equity in a Legal Entity, and Share capital is the total amount raised by the company in sale of shares.

Now we understood Share Capital, but to raise that capital from investors we should give back something trusted contract or government paper or something Security, right?

4) What is Security - A security, in a financial context, its a certificate or other financial instrument that has a monetary value, and can be traded. Securities are generally classified as equity securities, such as stocks. and debt securities, such as bonds and debentures.

5) And who buys all this? an Investor -The investors are those who gives money, in return of some security, like shares, bonds, Funds. (Funds could be an amount collected for special investment purpose)

6) Bonds – bonds are fixed payable contract between investor and Legal Entity. It’s a Security, investors pays money against bonds security.

7) Debentures – are a long term security with fixed rate of interest earning issued by a company.

------------------------------------------------------------------------------------------------------------------------

Now will go further inside of Private Equity business... 

------------------------------------------------------------------------------------------------------------------------

2) What is Inside Private Equity Business?

- a Term private equity is very broadly classified and customized, years on year however,
 “Generally Private Equity is, any equity investment in a company which is not quoted on a stock exchange.” it’s a basic definition.
But we can define it some more ways. like,
Something publicly listed company is taken privately and unlisted,
Or buying big shares in Listed Company as part of portfolio,
Or Investment in Real estate project (IT parks, Housing Project, Road Bridge building Projects, SEZ),
Or the acquisition of other company,
Or the investment in startups (like Snap Deal, OYO, OLA, Swinggy, Byju’s),
Or investment in good but loss making company and convert it into profit making (like Flipkart, Paytm).

Till here we understood that PE business collect money from investors and Invest in different businesses, Shares, Buy a Company, gives loan. now we will see different ways of investments PE company does.
basically Investments are classified in 2 ways.

•  Fund Investing – is something collecting money from multiple investors.

•  Direct Investing - are those who manage this funds are also called GP who does Direct 

       Investing in multiple company buys shares etc., GP have full rights of investment and investment

       strategies

•  LP, GP, OP – are the partners, will understand the partners of company, 

     • LP (Limited Partner) are the investors who invest money in Fund.

     • GP (General Partner) are the Fund managers, PE company.

     • OP (Operational Partner) are those who operate funds of other big fund houses, are generally called as Hedge Funds.

------------------------------------------------------------------------------------------------------------------------

So far we got a definition of Private Equity and GP, LP, OP, but here is the question, what does PE company does with collected money? and how do they make money? are there any types? Lets see that...

------------------------------------------------------------------------------------------------------------------------

3) Types of Private Equity Business

Private equity funds generally fall into two categories. 

Venture Capital and Buyout or Leveraged Buyout.

Venture Capital  
“Venture Capital is nothing but providing capital to Startups new Ventures with unique business ideas”
Typically invest in small early stage and emerging businesses, that are expected to have high growth potential or have innovative Ideas, although venture capital funds carry risks from investing in unconfirmed emerging businesses, they can generate extraordinary returns or losses.

Buyout or a Leveraged Buyout 
“Buyout is something majority share of the stock of the company is acquired”
Buyout - happens in large companies not in small startups which venture capital does,
Leveraged Buyout - A leveraged buyout is the acquisition of another company using a significant amount of borrowed money (bonds or loans) to meet the cost of acquisition

Above two are types of Private Equity business, but I would like to add another area, kind of master type called as Hedge Fund.

Hedge Funds – are the fund collected from big players, or fund houses, or Institutional investors and follows route of either venture or Buyout investments.

Here are some basic differences of Buyout and Leveraged Buyout.

Venture Capital	Buyout or Leveraged Buyout
Small enterprise, Young companies, even start-up	Large or very large enterprise, not startups
Bank debt almost never used	Bank debt almost always used
Converts startups to bigger profitable company	Increases the profit of company by reducing debts
Exit by selling company	Exit by selling Equity

------------------------------------------------------------------------------------------------------------------------

As of now we have learned many things in PE business, from basics to the types of PE business and partners, now we will go some more deep in Operations, how does the operations happen in PE.
     This is more interesting subject for Software Engineers who program PE needs, develop software for PE, designs Database, ETL and reports....

------------------------------------------------------------------------------------------------------------------------

4) Fund Operations and Security Master.

The operational flow is as shown below.
1) Investors Invest money into Fund, (Vehicle) Then
    --> Fund (Legal Entity)  Legal Entity invest that money in  --> Interesting Deals
    --> Deals can have multiple Positions
    --> Now Fund house pays money to Issuer (Seller)
    --> for that Issuer gives back Security like Bonds, Property Papers, Shares.
    --> all above business Transaction will captured for Audit.

I have a detailed diagram to illustrate the entire process below, link above flow with diagram to understand in details.

Looking at above diagram you can understand detail picture of PE business flow. We as a software developer develop application around this, business flow. And database designs are also similar across all PE business. There are multiple products used to drive the PE business
     SunGard Investran is one of the popular software, now its FIS Investran.
Alternative popular application for Investran are
1) Juniper Square, 2) IMS (Investor Management Services), 3) Dynamo,  4) VTS, 5) Stessa, 6) CoStar Investment Analysis, 7) MRI Investment Management, 8) Lease Eagle

#Investran #JuniperSquare #IMS #Dynamo #VTS #Stessa #CoStarInvestmentAnalysis #MRI
 #LeaseEagle

------------------------------------------------------------------------------------------------------------------------

This is what the Private Equity business, i have tried to cover maximum topics in this article with minimum words. however, without knowing best examples one cannot understand PE very well,
lets see some of the popular PE deals....

------------------------------------------------------------------------------------------------------------------------

5) Well known Deals of PE business

Most of us knows these deals but unaware that it’s a PE deal.

1) Venture Capital Deals Example - Paytm, Future Retail, Flipkart

    Soft bank, worlds one of the biggest Private Equity Firm, India Deals.

Blackstone’s Leveraged Buyout / Buyout Deals of taking over of bad Loans 

Few of biggest Buyout deals like Mphasis, IBS, SH Kelkar.

Most of the things i have covered here in this article, If any thing is missing in this article, please add your knowledge share from comments, suggestion, ideas 

Thank you !

How to join two CSV files for SSRS Reporting

How to join two CSV files for SSRS Reporting - Part 2

SSRS report with Inner joining two CSV files

   My recent article has covered “How to Use CSV file as DataSource for SSRS reports”, however one of the reader posted a question on that article, how to join two CSV files, i found it interesting hence i came up with this new article.

Joining two CSV files are as similar as joining two SQL tables, but there is a small syntax difference, consider this article as second part of previous article “How to Use CSV file as DataSource for SSRS reports” for better understanding on CSV as data source.

• First we will see the source data, i have created 2 CSV files with sample data of Mutual Funds and Asset Value,  with common ID for Joining 2 data source.
• Fund_groups.csv 

ID,Fund_Family,Fund_Family_Sub_Group,Line_Item,SubScript

1,SBI Blue Chip,SBI Blue Chip G,Received from General Partner,1

2,SBI Blue Chip,SBI Blue Chip N,Withholdings taxes,2

3,SBI Blue Chip,SBI Blue Chip O,Received from Distributed General Partner,3

4,DSP BR Micro Cap,DSP BR Micro Cap G,Received from General Partner,1

5,DSP BR Micro Cap,DSP BR Micro Cap N,Withholdings taxes,2

6,Birla Sun Life,Birla Sun Life G,Received from General Partner,1

7,Birla Sun Life,Birla Sun Life N,Withholdings taxes,2 

• Fund_Assets.csv 

ID,Total_Fund_Asset

1,10000000

2,1500000

3,8000000

4,40000000

5,41000000

6,4000000

7,4500000 

• Next querying those 2 data sources, and using it in SSRS reports, for this we need to use OLEDB Driver to establish connection.  (Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\CSV Source";Extended Properties="text;HDR=YES;FMT=Delimited")

• Write an ANSI SQL to join 2 CSV files, use complete file name as Table, we can use any joins Inner, Left, Right or Full Outer join, below is a sample query.

SELECT   fg.ID, fg.Fund_Family, fg.Fund_Family_Sub_Group, fg.Line_Item, fg.SubScript, 

fa.Total_Fund_Asset  

FROM Fund_groups.csv fg

INNER JOIN Fund_Assets.csv fa ON fg.ID = fa.ID

•      Now we got report data set, use it for report development, we can use any csv file no matter how large it is, joins will work. 
  

  

This is how we can join 2 different csv files using ANSI SQL. please leave your comments, messages, feedback, thank you

Microsoft SQL Server Database and DB log File Shrinking in one shot

Microsoft SQL Server Database and DB log File Shrinking in one shot

Most of the time  our database server use to go out of memory, whenever we restore Prod copy on Dev, then we use to do shrinking of log files and Database, doing this one by one is big task. Then I thought of doing this in one shot, here I am sharing a SQL code for the same.

I have used [sysdatabases] and [master_files] tables to get required metadata, and “while loop” for repeating DBCC Shrink for all databases, we can even do this using Courser but I prefer While loop in simple cases.

----------------------------------------------------------------------------

--#1 Get all Database names, Log file names and metadata by this SQL

----------------------------------------------------------------------------

select db.name as DBName,db.[dbid],df.name as DBFileName,df.type_Desc,df.State_desc

into #DBNames from [master].[sys].[sysdatabases] db inner join

[master].[sys].[master_files] df on df.database_id = db.[dbid]

and db.name not in ('master','tempdb','model','msdb') and df.State_desc='ONLINE'

-- Sample Result

/*

DBName        dbid   DBFileName           type_Desc    State_desc

-----------------------------------------------------------

AccountsDW    5             AccountsDW_Data      ROWS         ONLINE

AccountsDW    5             AccountsDW_log       LOG          ONLINE

SalesMaster   6             SalesMaster_Data ROWS             ONLINE

SalesMaster   6             SalesMaster_Log      LOG          ONLINE

*/

----------------------------------------------------------------------------

--#2 Declare required variables

----------------------------------------------------------------------------

Declare @DBID int =0, @MaxDBID int =0,@MinDBID int =0

Declare @SQL varchar(max)='',@DBName varchar(200),@Filename varchar(100)=''

----------------------------------------------------------------------------

--#3 Take Min Max DB id for further looping logic

----------------------------------------------------------------------------

Select @MinDBID=Min([dbid]),@MaxDBID=Max([dbid]) from #DBNames

-- Setting Starting point from Minimun DBID

Set @DBID = @MinDBID

----------------------------------------------------------------------------

--#4 Take DB name, log file name for shrinking in loop.

----------------------------------------------------------------------------

---- Here i have used 'LOG' in where condition because we need Log file name in ShringFile() function .

---- and Databse name for ShringDatabase() Function.

Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @MinDBID and type_Desc = 'LOG'

----------------------------------------------------------------------------

--#5 Loop for all databases

----------------------------------------------------------------------------

while @DBID<=@MaxDBID

begin

  -- Used Dynamic SQL for all databases.

  Set @SQL ='Use '+@DBName+ ' '+Char(10)

  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)

  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

 

  --#6 Increment DBid for looping over all databases

  Set @DBID = @DBID+1

  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'

  Print (@SQL)

  Exec (@SQL)

end

----------------------------------------------------------------------------

--#7 Final Output Print, wheich will execute one by one.

/*

Use AccountsDW

DBCC SHRINKFILE(AccountsDW_log,5)

DBCC SHRINKDATABASE(AccountsDW)

Use SalesMaster

DBCC SHRINKFILE(SalesMaster_Log,5)

DBCC SHRINKDATABASE(SalesMaster)

*/

----------------------------------------------------------------------------

Sys table help

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-2017

Thank you,
please leave your feedback and suggestions, your new ideas could help many more developers. 

Mobile Internet Issue On ASUS Zenfone Max Pro M1, ASUS_X00TD

Mobile Internet Issue On ASUS Zenfone Max Pro M1

  ASUS Zenphone max Pro M1 (ASUS_X00TD), recently i faced data connection issue in my phone 4G Volt, i tried all options, Installing new internet settings, Updating APN

but it did not worked, after doing lot of search and study i fixed this, here is how.

go to your setting -->

1)    Network & Internet --> Mobile Network --> Advanced --> APN --> Here Delete all APN's

2) Then restart phone without APN

3) go to again same APN Setting  Network & Internet --> Mobile Network --> Advanced --> APN --> Options on right top --> Click Reset Default.

after this your internet will start as usual and fast.

Thank you.

   

Get First Business day of Month, in MS SQL Server

Get First Business day of the Month

In this article, we will discuss how to get First day of the month and First Business day of the month (Excludes Saturday and Sunday if it comes on 1^st day of month), SQL server does not have direct function to get all this we need to write some workaround code.

First, get First day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-15', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Now, get First Business day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-5', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth) -- Gets Sunday

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth) -- Gets Saturday

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

Let’s write Function to get First Business day of the Month

Create  Function [dbo].[ufnGetFirstBusinessDay](@YourDate Datetime)

Returns Datetime as

Begin

--This part gets First date of the month.

Declare @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

--This part gets First business day of the month,

--excludes Saturday and Sunday if it comes on 1st day of month

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth)

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth)

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

End

--Select dbo.GetFirstBusinessDay('2019-6-15')

Accounting Format for numbers in SSRS report

    SSRS report does not have Accounting-formatting feature like MS Excel have. But some banking and Financial Client asks Accounting Formatting to numbers in SSRS, here is the solution for this

Image shows Differences of accounting right align currency format

To achieve this you need to Tweak Expression as shown below, give space after 0 for accounting format and after – to align no data in same format. you can change this expression as per your Need, Space is the Solution to get Accounting Format.

#,0;(#,0);'-'    

#,0 ;(#,0);'- '

Please look into below image

Thank you, Please give your reply, question,  Feedback and any additional Tweaks 

Msg 5094, The operation cannot be performed on a database with database snapshots or active DBCC replicas

Error:
Msg 5094, Level 16, State 2, Line 2
The operation cannot be performed on a database with database snapshots or active DBCC replicas.

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally, .

This error basically comes when there is Snapshot file present for same DB, solution for this is very simple, go to the data file location Eg. C:\MSSQL\DATA\ or wherever you have stored your data files. and Delete YourDatabase.SS file.

Then you can restore your database .

Database restore Command as shown below.


sp_helpdb MY_DATABASE
--to see Logical file name of Data and Log file and location


ALTER DATABASE [MY_DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Take your database on Single user for safe side.


RESTORE DATABASE [MY_DATABASE]
FROM DISK = 'D:\PROD Backup\MY_DATABASE.bak'
WITH
MOVE 'MyDatabase_Datafile' TO 'D:\MSSQL\DATA\MY_DATABASE.mdf',
MOVE 'MyDatabase_Logfile' TO 'D:\MSSQL\LOG\MY_DATABASE_log.ldf'


ALTER DATABASE [MY_DATABASE] SET MULTI_USER
-- Back to multi user