• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Portfolio Analysis |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Portfolio Analysis |General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Portfolio Analysis
sp_PrintTopic sp_TopicIcon
Portfolio Analysis
Avatar
ARUN KAINTH
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 15, 2021
sp_UserOfflineSmall Offline
1
March 19, 2021 - 2:38 pm
sp_Permalink sp_Print sp_EditHistory

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!AkTmieVNgP.....Q?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 

 

 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 19, 2021 - 4:39 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
ARUN KAINTH
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 15, 2021
sp_UserOfflineSmall Offline
3
March 20, 2021 - 3:28 pm
sp_Permalink sp_Print sp_EditHistory

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;

  1.  Summarize rows as per Symbol, Order id, Date, & Execution time wise with selected columns ( Action, Qty Rate & commission) to Proforma Sheet.
  2.  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 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.