Import Shares Sale / Purchase Transactions into Tally Software
Looking for a software utility to import your SHARES Sale / Purchase transactions
into Tally Software ? Try out udiMagic.
Although, UDI-Magic can import any type of transactions into Tally, herein we shall
focus on importing sales and purchase transactions of SHARES done through Reliance
Money.
Contents
UDI-Magic is a software utility that allows you to Import Excel data into Tally
Accounting Software. It supports all Vouchers types (pre-defined or user-defined)
and works with Tally 6.3 or higher versions.
SHARES trading (Sale and Purchase)
A number of companies in the Market offer Online Shares-trading
and related services. Some of the well-known names are Reliance Money, ICICIDirect.com,
HDFC bank, ShareKhan etc. All these companies offer trading portals with online-trading
facility. If you have a Computer with Internet Connection, you can purchase and
sell SHARES / MFs on your own.
To keep track of your transactions and the Profit/loss thereon, you may have felt
the need to enter these transactions into an Accounting Software. The first name
that comes to mind is Tally Software which is widely used for Accounting.
Though, the Tally Accounting software does not have any special features relating
to SHARES TRADING or related activities, you can get sufficient information to manage
your porfolio. However, entering the transactions manually into Tally Software
can be a tedious task. Here's where udiMagic can ease your burden.
How do I transfer my SHARES
Purchase and Sale transactions from my Online Trading A/c into Tally ?
The above task involves two main things:-
- Export data from your Online-trading portal to MS-Excel
- Import the data from MS-Excel into Tally Software using udiMagic
Remarks:-
a) Herein, we shall take Reliance Money Online trading A/c to illustrate how you
can transfer the shares Sale/purchase transactions into
Tally. You can however, use udiMagic to transfer data from any Online-trading portal.
b) Almost, all online-portals provide "Export" feature which allows you to export
all your transactions into MS-EXCEL (or CSV format). Once,
you get the data in MS-Excel, it is quite easy to import it into Tally Software
using udiMagic.
System Date Format
Before we begin we the real task, you need to check the System Date-format in your
Computer. As most of the portals (including Reliance Money) use the
dd-mm-yyyy format, we need to ensure that the Windows Operating System
Date-format is set to dd-mm-yyyy. The need arises as the default date-format in
Windows XP systems is mm-dd-yyyy (English -US).
Steps:-
a) From the Windows START menu, select SETTINGS
b) Next, select CONTROL PANEL
c) Next, select REGIONAL AND LANGUAGE OPTIONS
d) Select option ENGLISH (United Kingdom) under the "Regional Options" tab. This
will display short-date format as dd/mm/yyy.
e) Click on Ok to save the changes.
Brokerage and other Taxes
The Brokerage (Reliance Money) and other Taxes
for sale/purchase of shares is given below :-
|
Brokerage/Taxes |
Charges |
Remarks |
|
Brokerage |
1 paise / per trade
|
Applicable for Reliance Money Customers under Scheme Rs 500/-
for Rs 500,000/- turnover. |
|
Transaction Charges |
Rs 12 / per sale transaction
(for online-instructions) |
SELL (Market & Off Market)
- For Instructions given in Physical form Rs 25/-
- For Instructions given via online-trading Rs 12/-
|
Other Charges
a) Call-centre Charges
b) Assisted trade Charges |
a) Rs 12 / per executed order
b) Rs 12 / per executed order |
These charges are not applicable if you do online-trading using your own Computer
and Internet connection.
However, you need to ensure that you DO NOT opt for these
facilities when you open your Reliance Money account; otherwise Rs 12/-
per each trade will be deducted from your account.
|
|
Service Tax |
12.24 % on brokerage |
Nil for Reliance Money Customers who have opted for the above scheme.
Remarks:-
Just try calculating 12.24% of 1 paise. It is 0.00
paise when rounded to 2 decimals. |
|
Securities Transaction Tax (STT) |
0.125 % of the trade value
or
0.025% of the trade value |
Case 1: 0.125% of trade value
In case of either Purchase or Sell transaction,
STT will be 0.125% of trade value
Example:-
Purchased 100 shares of ABC CO. Ltd @Rs 100
on 1-4-2007. STT will be 0.125% of Rs 10000/-
Sold 100 shares of ABC CO. Ltd @ 150
on 2-4-2007. STT will be 0.125% of Rs 15000/-
Case 2: 0.025% of Sale value
In case, both Purchase and Sell transaction is
done on the same day, STT will be 0.025% of
the trade value.
Example:-
Purchased and sold 100 shares of ABC CO. Ltd
on 1-4-2007. STT will be 0.125% on Sale value
only.
|
|
Stamp Duty |
0.01% of the trade value |
Not taken in sample Excel sheet Template |
|
Turnover tax |
0.0035% of the trade value |
Not taken in sample Excel sheet Template |
Remarks:-
- The Brokerage and Fees shown above are for Reliance Money Online-trading as on
Dec-2007.
- STT and brokerage are levied in case of both Sales and Purchase transactions.
Hence your net sale and purchase price will be different
then the actual traded price.
- We have ignored the Stamp duty and Turnover Tax which is levied separately on
each Contract-note.
Herein, we shall take up how to Export data (i.e. sale/purchase transactions) from
your Reliance-Money A/c.
Steps:-
a) Login to your
Reliance-Money A/c.
b) Select the HISTORY tab under EQUITY menu-option
c) Select the TRADE option
d) Enter the FROM and TO date and then click on the EXPORT button. This will export
data (sales and purchases) into a Columnar format.
e) Next, press Ctrl+A to select all data and then copy it by pressing Ctrl+C.
f) Next, open a new MS-Excel worksheet and then paste the data using Ctrl+V.
That's all. Now, you have all your Reliance Money transactions (sales and purchase
of shares) in MS-Excel.
To import data from MS-Excel into Tally, you need to tell udiMagic what data you
have and where is it stored in the Excel sheet. This is done by using XML tags.
Those who are not familiar with XML, need not worry. You can simply copy the 1st
row from the template Excel sheet provided herewith.
Some of the important columns in the template Excel-sheet are :-
Column H - Buy or Sell
Column I - Scrip Name
Column J - Qty
Column K - Price
Column L - Amount
Column Q - ID
Steps:-
a) Download the Excel-sheet template "Reliance Money-SHARES Sales and Purchase Transactions.xls"
b) Copy the 1st Row from this template sheet into your Excel sheet.
c) Copy the Column Q contents from the template sheet into your Excel sheet.
Remarks:-
Those who are using other online-trading portals like ICICIdirect.com; ShareKhan;
HDFC etc. can send us the Excel files which
contains the SHARES sale / purchase transactions. We shall write the XML tags required
to import data into Tally.
TECHNICAL STUFF
This section contains technical details. Those are not familiar with XML may ignore
this topic an jump to the next topic
Import Data from MS-Excel into Tally
Explanation of XML tags used in the template Excel file
udiMagic uses XML tags to identify data in Excel sheet. The XML tags are to be written
in the COMMENTS section of any cell in the first row in the Excel sheet.
The XML tags specified in cell A1
(comments section) are given below:-
|
XML Tags |
Remarks |
<MASTER TYPE="UNIT" SINGLERECORD="YES">
<NAME>Nos</NAME> <ORIGINALSYMBOL>Numbers</ORIGINALSYMBOL>
<ISSIMPLEUNIT>Yes</ISSIMPLEUNIT>
</MASTER>
|
Create UNIT master
Explanation:-
The attribute SINGLERECORD="Yes" specifies that this is to be processed
once only. Hence, udiMagic creates only one master-record.
These XML tags instruct udiMagic to create a single UNIT-master record viz "Nos" |
<MASTER TYPE="STOCKGROUP" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Shares</NAME>
</NAME.LIST>
<PARENT/>
</MASTER> |
Create STOCKGROUP master
These XML tags instruct udiMagic to create a single STOCKGROUP-master record
viz "Shares" |
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Purchase of Shares</NAME>
</NAME.LIST>
<PARENT>Purchase Accounts</PARENT>
<AFFECTSSTOCK>Yes</AFFECTSSTOCK>
<USEFORVAT>No</USEFORVAT>
</MASTER>
|
Create LEDGER master
These XML tags instruct udiMagic to create a single LEDGER-master record
viz "Purchase of Shares"
- NAME.LIST specifies a static Ledger-Name "Purchase of Shares"
- PARENT is set to "Purchase Accounts"
- AFFECTSSTOCK is set to Yes |
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Sale of Shares</NAME>
</NAME.LIST>
<PARENT>Sales Accounts</PARENT>
<AFFECTSSTOCK>Yes</AFFECTSSTOCK>
<USEFORVAT>No</USEFORVAT>
</MASTER>
|
Create LEDGER master
These XML tags instruct udiMagic
to create a single LEDGER-master record viz "Sales of Shares"
- NAME.LIST specifies a static Ledger-Name "Sale of Shares"
- PARENT is set to "Sales Accounts"
- AFFECTSSTOCK is set to Yes |
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME>Reliance Money A/c</NAME>
</NAME.LIST>
<PARENT>Sundry Debtros</PARENT>
</MASTER>
|
Create LEDGER master
These XML tags instruct udiMagic to create a single LEDGER-master record
viz "Reliance Money A/c"
- NAME.LIST specifies a static Name "Reliance Money A/c"
- PARENT is set to "Sundry Debtors"
|
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<NAME COLUMNREFERENCE="I"/>
</NAME.LIST>
<PARENT>Shares</PARENT>
<BASEUNITS>Nos</BASEUNITS>
<COSTINGMETHOD>FIFO</COSTINGMETHOD>
</MASTER>
|
Create STOCKITEM masters
The <MASTER TYPE="STOCKITEM"> tag instructs udiMagic to create STOCKITEM-Masters
- NAME is to be taken from Column I
- PARENT is set to "Shares". Hence, Tally will create the StockItem under StockGroup
"Shares"
- BASEUNITS is set to "Nos"
- CostingMethod is kept as FIFO.
In brief, udiMagic processes all rows in the Excel sheet and creates StockItem Masters
based on the Column I values |
<VOUCHER>
<GUID FORMULA='=+"662d23-6472-4hk2-27bb-
ed24aff58ac9" & "-" &
IF(LEN(Q#)>=8,"",REPT("0",8-LEN(Q#))) & RIGHT(Q#,8)'/>
<DATE COLUMNREFERENCE="M"/>
<EFFECTIVEDATE COLUMNREFERENCE=""/>
<VOUCHERTYPENAME FORMULA='=IF(H#="S","Sales","Purchase")'/>
<REFERENCE COLUMNREFERENCE="C"/>
<ISINVOICE>Yes</ISINVOICE>
<LEDGERENTRIES.LIST>
<LEDGERNAME>Reliance Money A/c</LEDGERNAME>
<ISDEEMEDPOSITIVE FORMULA='=IF(H#="S","Yes","No")'/>
<AMOUNT FORMULA='=IF(H#="S",(+L#-Round
(+L#*0.00125,2)-0.01-12.00)*-1,Round(+L#*1.00125,2)
+0.01)'/>
</LEDGERENTRIES.LIST>
<ALLINVENTORYENTRIES.LIST SCROLL='YES'>
<STOCKITEMNAME COLUMNREFERENCE="I"/>
<ISDEEMEDPOSITIVE FORMULA='=IF(H#="S","No","Yes")'/>
<RATE FORMULA='=IF(H#="S",(L#-Round(L#*0.00125,2)-
0.01-12.00)/J#,(Round(+L#*1.00125,2)+0.01)*-1)/J#'/>
<AMOUNT FORMULA='=IF(H#="S",+L#-Round
(+L#*0.00125,2)-0.01-12.00,(Round(+L#*1.00125,2)+0.01)
*-1)'/>
<ACTUALQTY COLUMNREFERENCE="J"/>
<BILLEDQTY COLUMNREFERENCE="J"/>
<ACCOUNTINGALLOCATIONS.LIST>
<LEDGERNAMEFORMULA='=IF(H#="S","Sale of shares","Purchase of Shares")'/>
<ISDEEMEDPOSITIVEFORMULA='=IF(H#="S","No","Yes")'/>
<AMOUNT FORMULA='=IF(H#="S",+L#-Round
(+L#*0.00125,2)-0.01-12.00,(Round(+L#*1.00125,2)+0.01)
*-1)'/>
</ACCOUNTINGALLOCATIONS.LIST>
<BATCHALLOCATIONS.LIST>
<GODOWNNAME>Main Location</GODOWNNAME>
<BATCHNAME>Primary Batch<BATCHNAME>
<DESTINATIONGODOWNNAME>Main Location<DESTINATIONGODOWNNAME>
<AMOUNT FORMULA='=IF(H#="S",+L#-Round
(+L#*0.00125,2)-0.01-12.00,(Round(+L#*1.00125,2)+0.01)
*-1)'/>
<ACTUALQTY COLUMNREFERENCE="J"/>
<BILLEDQTY COLUMNREFERENCE="J"/>
</BATCHALLOCATIONS.LIST>
</ALLINVENTORYENTRIES.LIST>
</VOUCHER>
|
Specifies data for the Voucher records
- GUID is a mandatory field. GUID is a unique value which is used by Tally to identify
each Voucher record uniquely. As this formula is bit difficult, we suggest that
you skip it for the time-being. Only note that the GUID is based in Column Q which
is specified by Q#
- DATE is to be taken from Column M
- EFFECTIVEDATE is generally same as DATE and is set to Column M
- VOUCHERTYPENAME is set to "Sales" or "Purchase" depending upon the value in Column
H
- REFERENCE is to be taken from Column C
- ISINVOICE is set to static value "Yes"
LEDGERENTRIES.LIST contains tags for the Party Ledger
- LEGERNAME is set to static value "Reliance Money A/c"
- ISDEEMEDPOSITIVE tag is set to 'Yes' in case of "Sales" and 'No' in case of "Purchase"
- AMOUNT is a formula. It is worked out as follows:-
Formula for Sales
+L#-Round(+L#*0.00125,2)-0.01)*-1
Remarks:-
- The STT and brokerage is deducted from the Sales Amount
- Sales Amount is in Column L
- STT is 0.125%
- Brokerage is 1 paise
- As the Party a/c is to be debited in Sales entry, the Amount is shown as Negative
Formula for Purchase
+Round(+L#*1.00125,2)+0.01)
Remarks:-
- The STT and brokerage are added to the Purchase Amount
- Brokerage is 1 paise
- As the Party a/c is to be credited in Purchase entry, the Amount is shown as Positive
ALLINVENTORYENTRIES.LIST contains tags for the StockItems that appear in the
Voucher
- STOCKITEMNAME is to be taken from Column I
- ISDEEMEDPOSITIVE must be set to "No" for StockItems in Sales Vouchers, and Yes
in cae of Purchase
- RATE is a Formula which adjusts the Sales/Purchase price by STT and brokerage
amount
- AMOUNT is a Formula which adjusts the Sales/Purchase price by STT and brokerage
amount
- ACTUALQTY/BILLEDQTY are to be take from Column J
ACCOUNTINGALLOCATIONS.LIST contains tags for Ledger allocations with respect
to each StockItem
- LEDGERNAME is set to "Sale of Shares" or "Purchase of Shares" depending upon the
value of Column H
- ISDEEMEDPOSITIVE is set "Yes" when Sales and "no" when purchase
- AMOUNT is a Formula which adjusts the Sales/Purchase price by STT and brokerage
amount
BATCHALLOCATIONS.LIST contains tags for StockItem-wise batch allocations.
Herein, default values are used for the GODOWNNAME and BATCHNAME.
- AMOUNT is a Formula which adjusts the Sales/Purchase price by STT and brokerage
amount
|
The XML tags specified in cell Q1
(comments section) are given below:-
|
XML Tags |
Remarks |
<COLUMNNAME.LIST>
<COLUMNNAME>ID<COLUMNNAME>
<COLUMNNAME.LIST>
|
Specifies the key-field
Explanation:-
- COLUMNNAME.LIST
XML tag is used to specify the Name for the Excel column. Herein, we specify the
Column-name as ID
- udiMagic uses the ID
field/Column as the key-field
- ID field/column is
mandatory for Vouchers
|
udiMagic comes with a easy-to-use wizard interface. Simply run the udiMagic software
and select the Excel file which contains the data that you want to import into Tally.
Steps:-
a) Start Tally and create a new Company (say TEST). Keep the books beginning date
as 1-4-2007.
b) Minimize Tally.
c) Start udiMagic utility and follow instructions in the Wizard.
d) Select the EXCEL file "reliance-money-shares-sales-purchase-transations.xls"
when prompted.
e) Click on the START button to import data into Tally.
udiMagic first checks and creates masters, if required and then processes the Voucher
Records.
Let's take a sample Purchase and Sale transaction and see it's Accounting effect.
|
Scrip Name / Particulars |
Purc
Qty |
Purc
Price |
Purc
Amt (Rs) |
Sale
Qty |
Sale
Price |
Sale
Amt (Rs) |
Remarks |
ABC Co. Ltd
a) Brokerage @ 1 paise per trade
b) ServiceTax @ 12.24% on brokr.
c) Securities Trn.Tax (STT) 0.125%
d) Transaction charges
Net |
100 |
100.00
______
100.125 |
10000.00
+0.01
Nil
+12.50
Nil
_______
10012.51
|
100 |
150.00
______
149.692 |
15000.00
-0.01
Nil
-18.75
-12.00
________
14969.24 |
Service Tax is calculated on Brokerage.
STT is calculated on trade value
Trans. charges Rs 12/- per trade for SALE
|
Remarks:-
a) Service Tax is Nil in the above case, as Serv.Tax @ 12.24% on the Brokerage (of
1 paise) is 0.00 paise when rounding-off
to 2 decimals.
b) It is assumed that Purchase and Sale transactions have been done on different
days. Hence, STT applicable rate will
be 0.125% of trade value.
Accounting Entry
|
Particulars |
Debit |
Credit |
Remarks |
Purchase of Shares
Reliance Money A/c |
10012.51 |
10012.51 |
On purchase of Shares, Rs 10012.51 is payable to Reliance Money. |
Reliance Money A/c
Sale of shares |
14969.24 |
14969.24 |
On sale of shares, Rs 14969.24 is receivable from Reliance Money |
Remarks:-
1) The above given accounting entries are for illustrative purpose only.
2) If required, a different Accounting entry can be passed by changing the XML tags
in Excel sheet (Cell A1).
Downloads
UDI-Magic Demo version
Reliance Money-SHARES Sales and Purchase Transactions.xls
(Excel sheet Template)
Links
udiMagic Home Page
udiMagic Articles and sample
Excel templates
Tags:-
udiMagic, Reliance Money, ICICIdirect.com, ShareKhan, HDFC bank, Excel to Tally
|
