• 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

Stock Portfolio Dashboard - realized gain/loss|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Stock Portfolio Dashboard - realized gain/loss|Dashboards & Charts|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 ForumDashboards & Charts Stock Portfolio Dashboard - realiz…
sp_PrintTopic sp_TopicIcon
Stock Portfolio Dashboard - realized gain/loss
Avatar
Mike Richardson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 23, 2021
sp_UserOfflineSmall Offline
1
March 23, 2021 - 3:48 am
sp_Permalink sp_Print

How to add realized gain/loss to the Stock Portfolio Dashboard?14

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 23, 2021 - 9:09 am
sp_Permalink sp_Print

Hi Mike,

Welcome to our forum!

Unfortunately, your question is too vague. Please provide a sample Excel file that clearly illustrates the data you're working with and your desired result so we know what you mean.

Thanks,

Mynda

Avatar
Mike Richardson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 23, 2021
sp_UserOfflineSmall Offline
3
March 25, 2021 - 2:11 am
sp_Permalink sp_Print

Hi Mynda-

A sample Excel file is attached.  You can see several examples of

Unrealised gain where the formulas follow your example

Realised gain (MCD, NVDA, TSLA, and VEEV) where I made a stab at it by an IF statement that only calculates gain/loss when SHARES > 0.  This in turn disclosed that with fractional shares, a add and subtract in the same amount (example: MCD 0.62073) shares doesn't calculate to exactly zero.  

And I could figure out a formula for a stock that has both open and closed positions (example AMZN) so I did it by a one-time formula.

-Mike

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 25, 2021 - 9:03 am
sp_Permalink sp_Print

Hi Mike,

No file attached. Be sure to click the 'Start Upload' button after selecting your file and wait for the green notification under the button to tell you that your file has completed uploading.

Mynda

Avatar
Mike Richardson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 23, 2021
sp_UserOfflineSmall Offline
5
March 25, 2021 - 10:00 pm
sp_Permalink sp_Print

Second try to upload file

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 26, 2021 - 8:10 pm
sp_Permalink sp_Print

Hi Mike,

Thanks for sharing your file. 

Not sure how the Amazon shares work. It appears you bought 28 on Feb 4, then sold 32 on March 3, which is more than you had. Then April 16 you bought 32?

Mynda

Avatar
Mike Richardson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 23, 2021
sp_UserOfflineSmall Offline
7
March 28, 2021 - 1:18 am
sp_Permalink sp_Print

Mynda-

The 32 shares (Lot #1) were bought in 2020, and sold in 2021.

The 28 shares (Lot #2) were bought in 2021

Lot #1

2020-04-16 Buy 32x AMZN

2021-03-03 Sell 32x AMZN

Lot #2

2021-02-04 Buy 28x AMZN

So Lot #1 is a closed position, and Lot #2 is an open position.

-Mike

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
March 28, 2021 - 3:07 pm
sp_Permalink sp_Print

Doh, I misread the year! I shouldn't answer questions at night after a long day 🙂 

You can add a Status column to the ledger that marks shares as sold. Then you can add a criteria to your Unrealised and Realised Gain/Loss SUMIFS that only include transactions marked as open or sold respectively. If you only sell part of a holding, then you'd need to split the transaction into two rows, one for the sold shares and one for the shares you still hold.

Hope that makes sense. See example attached.

Mynda

Avatar
ARUN KAINTH
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 15, 2021
sp_UserOfflineSmall Offline
9
March 29, 2021 - 3:31 pm
sp_Permalink sp_Print

Hi Mike,

I noticed that your Lot #1 2020-04-16 Buy 32x AMZN  is showing as Interest in Col C ( Action)  and not Buy.

 

Arun  

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

Hi Mike,

MCD resulting as 0.62073 due to Dividend transaction. 

Arun  

Avatar
Mike Richardson

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 23, 2021
sp_UserOfflineSmall Offline
11
March 29, 2021 - 10:56 pm
sp_Permalink sp_Print

Fixed the errors.  Thanks.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Emma Klonowski, Murat Hasanoglu
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31912
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.