Hi Mynda,
I was amazed to watch your video and interacted with you there, where you asked me to post question in forum here. Here is the sample file link. I have also attached
https://1drv.ms/x/s!AkTmieVNgP8mgaoGMY2_Jmjn_Z18oQ?e=pPDnsf
There are 4 worksheets. I have posted my notes in "Ledger" worksheet as below. I am not sure which formulas can help me to get the desired results for my "Proforma" worksheet. Somehow I was able to create formulas for "Cost Basis and Profit" worksheet."
Notes
1 Col F ( Order execution time) helps to determine correct gain or loss after each sell transaction in case of multiple transactions of buy and sell in one day.
2 Problem here is that it gives multiple rows ( sometime 8-10 rows) for a single buy or sell order.
3 I am looking to automate to see results as per sheet " Proforma" where I have copy pasted gain or loss , adjusted cost basis dollar amount and Balance quantity results form sheet "Cost basis and profit"
4 "Cost basis and profit" which I prepaired manually that is very cumbersome.
5 Also I wish to have one summary sheet ; refer Balance holdings sheet.
6 I learned excel from YouTube and not familiar with formulas to make my sheet. Looking for help here.
I will be highly obliged if you can advise how to proceed.
Looking for expert reply.
Thank you,
Arun
Hi Arun,
Welcome and thanks for sharing your file. The purpose of the forum is to help with specific Excel questions. What you have here is a brief for a consultant. i.e. a wish list of functionality as opposed to a specific formula or Excel feature you need help with.
You're on the right track creating the Proforma sheet as this is in the correct Tabular Format required for making analysis easy.
A few pointers:
1. the times i column F of the Ledger sheet aren't in a format Excel can work with. They are text instead of date/time serial numbers. You mention that these times help determine the correct gain or loss, but you haven't said how. If you want to automate this then you'll be best to fix the time format so they're date/time serial numbers.
2. The formulas that contain IFERROR in the Cost Basis and Profit sheet can be simplified e.g. cell O6 could be simplified from this:
=IF(M6>1,IFERROR(G6*M6,0),IFERROR(G6*M6,0))
to this:
=IFERROR(IF(M6>1,G6*M6,G6*M6),0)
Hopefully you can make a start by moving these formulas across to the Proforma sheet. If you would prefer a consultant to help you and you have the budget to allow it, please get in touch via email and I'll put you in touch with someone who can help.
Mynda
Hi Mynda,
First of all I appreciate your time and efforts for quick reply & simplified formula re IFERROR.
Yes I accept times are not in acceptable format as entire Ledger sheet is typed manually with fictitious data. Original data download from broker site will have correct format, no worries. Sorry my error, I should have included time column in Proforma Sheet.
Regarding correct gain or loss calculations based on time; I simply meant that time stamp helps me to sort correctly when I have multiple Buy & Sell orders in a single day for single Symbol. This helps me to recall my emotions attached to those orders when I calculate gain or loss at a later date say; after a week or month. For example orders might be like this; Buy-Buy-Sell-Buy-Sell-Buy OR Buy-Sell-Sell-Buy-Sell , i.e. in any sequence. I am novice to investing along with my other regular full time job, and do trade occasionally on vacations etc. as hobby.
Regarding my Proforma Sheet; basically my need is as follow;
- Summarize rows as per Symbol, Order id, Date, & Execution time wise with selected columns ( Action, Qty Rate & commission) to Proforma Sheet.
- And auto calculate rest of the column in proforma Sheet. (BUY- AMT -SELL AMT -ACB -Bal Qty -COST- PnL -USD XCHNG -COMM2 -BUY AMT -SELL AMT-A C B -COST$CAD-P & L.
My immature Excel knowledge makes me feel that I may only need to input another 2-3 Excel functions to sort/summarize along with profit & loss formulas to get the desired results. If you feel it doesn't requires highly advance knowledge, I will really appreciate if you can give your tips which functions (like VLOOKUP, SUMIF, COUNTIF etc.) can be used so I can give a try to build this sheet. I am kind of DIYer with above average learning capabilities.
Re your suggestion for a consultant, it depends on cost. I will appreciate if I can get an idea. I may even opt in later half of the year to go for one of your course based on my needs and your suggestions as I love learning & I found Excel just amazing thing to addon to knowledge; though my only need is to make this Proforma sheet currently.
Arun
