Hi Friends,
Many times i require a complete history of profit/loss i have ever made during trading years/months.
To get these values i am showing you simple steps in excel to achieve the same
So if you have brought one scrip multiple times over the years and sold them, it will show you net profit loss
Pls note this applies to only to:
1. delivery shares (it might apply to intraday/options if you get the steps).
2. This applies to report that is generated from ICICIdirect.com (also possible from other trading sites if you can get the data in format as mentioned in Step 5 below)
Steps:
1. Logon to icicidirect
2. Go to section Equity > Trade Book
3. Change the date of the field "Date From" as required
4. Click on "view" and then click on "export to Excel" button, A excel file will be saved on your harddisk
5. Open the excel file and delete all columns except "Stock" "Action" "Trade Value"
you excel should look like this now:
Stock | Action|Trade Value
JMSHAR| Sell| 3500
JMSHAR| Buy| 2000
JMSHAR| Buy| 1000
6. Now we will generate profit/Loss for each script using these simple steps
7. Go to "Data" menu of excel and click on "Pivot Chart and Pivot Table Wizard"
8. Click next, it will ask for data range. Select all 3 columns which includes all data (not just columns)
9. Click next and Click Finish
10. You will get an empty excel with some marker tables
11. Now:
Drag and Drop "Stock" field to "drop row fields here"
Drag and Drop "Action" Field to "drop Columns fields here"
Drag and Drop "Trade Value" Field to "Drop Data Items here"
and you will see some values filled up, these are default values which need to be corrected
12. Just right click on any of these values and click on "field settings" and select "Sum"
You are done!!!!!!!!!!
What you see is per stock total buy and total sell price.
So from the above example it will look like this:
Stock Buy Sell
JMSHAR 3000 3500
From here you can do basic excel to find where your money went
Hope this helps
If you have any problems pls let me know and i will try to help you
Many times i require a complete history of profit/loss i have ever made during trading years/months.
To get these values i am showing you simple steps in excel to achieve the same
So if you have brought one scrip multiple times over the years and sold them, it will show you net profit loss
Pls note this applies to only to:
1. delivery shares (it might apply to intraday/options if you get the steps).
2. This applies to report that is generated from ICICIdirect.com (also possible from other trading sites if you can get the data in format as mentioned in Step 5 below)
Steps:
1. Logon to icicidirect
2. Go to section Equity > Trade Book
3. Change the date of the field "Date From" as required
4. Click on "view" and then click on "export to Excel" button, A excel file will be saved on your harddisk
5. Open the excel file and delete all columns except "Stock" "Action" "Trade Value"
you excel should look like this now:
Stock | Action|Trade Value
JMSHAR| Sell| 3500
JMSHAR| Buy| 2000
JMSHAR| Buy| 1000
6. Now we will generate profit/Loss for each script using these simple steps
7. Go to "Data" menu of excel and click on "Pivot Chart and Pivot Table Wizard"
8. Click next, it will ask for data range. Select all 3 columns which includes all data (not just columns)
9. Click next and Click Finish
10. You will get an empty excel with some marker tables
11. Now:
Drag and Drop "Stock" field to "drop row fields here"
Drag and Drop "Action" Field to "drop Columns fields here"
Drag and Drop "Trade Value" Field to "Drop Data Items here"
and you will see some values filled up, these are default values which need to be corrected
12. Just right click on any of these values and click on "field settings" and select "Sum"
You are done!!!!!!!!!!
What you see is per stock total buy and total sell price.
So from the above example it will look like this:
Stock Buy Sell
JMSHAR 3000 3500
From here you can do basic excel to find where your money went
Hope this helps
If you have any problems pls let me know and i will try to help you