• 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

Using time/date formula in a securities transactions|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Using time/date formula in a securities transactions|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…Using time/date formula in a securi…
sp_PrintTopic sp_TopicIcon
Using time/date formula in a securities transactions
Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
1
April 18, 2020 - 6:14 am
sp_Permalink sp_Print

Hi there,

Hope you can help me with a formula I want to make for my worksheet.

I am trying to make a spreadsheet to record stock transactions and to figure out gain/loss.

The spreadsheet I already made is attached.

My  question is what formula to use so that I get result as “Y” for yes or “N” for No in column L (superficial loss column) as shown in the spreadsheet depending on certain criteria. Right now, I have put “N” or “Y” in column L manually.

A loss is considered superficial loss when following 2 conditions are met:

  1. When you sell a security for a loss,
  2. And you have bought the same security during the period starting 30 calendar days before the sale and ending 30 calendar days after the sale.

As you can see in the spreadsheet, highlighted cells L7, L8 and L20 are marked “Y” (manually) by me as they meet the following 3 conditions:

  1. Rows 7, 8 and 20 is for “Sell” transactions as shown in column A,(Cells A7, A8,A20),
  2. In all above 3 rows, there is corresponding loss in column I (Column I7, I8,I20),
  3. And, the same shares were bought either 30 days before or 30 days after the “sell” date in column A.

 

I did not mark cell L22 as “Y” because it meets the above 2 criteria but not the third one (i.e. the same shares were not bought either 30 days before or 30 days after the sell date.

Basically, it should give result as "Y" for yes in column L only if the above 3 criteria are met, otherwise it should give “N” result.

I am not that great at excel. I learned and made this spreadsheet as it shows now, but the formula I am asking you for help seems to be too complicated for my level.

Hope you can help. Thanks

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 18, 2020 - 11:13 am
sp_Permalink sp_Print

Hi Jay,

There's no information on your workbook about when/if a security has been bought or sold in the 30 days before or after any particular transaction.  So there's no way to know the information you need in order to come up with a formula to solve your problem.

Specifically, looking at Row 22, we know that transaction was a Sell and we know it was a loss, but how do we know that those shares were not bought 30 days before or after the transaction recorded in Row 22?

You'll need to record all transactions so that you can look back at what you've traded previously.  But there's no way to look forward in time to see what you will trade. 

So with Row 22, if we look at things as they stand now, it is a loss because we don't know the future.  But if you purchased these sames shares again within 30 days after after this Sell, you'll be retrospectively adjusting Row 22 from a loss to not being a loss.

Regards

Phil

Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
3
April 19, 2020 - 12:44 am
sp_Permalink sp_Print sp_EditHistory

Column B shows the date of transaction i.e. buy date  or sell date or SS(short-sale) date or SSFL(short-sale buy-back) date or split date.

There is always chance that there is going to be last one or two sale transactions with loss as in Row 22. In that case, it is assumed that if there is no buy transaction in previous 30 days, then there is no buy transaction (expected) in next 30 days as well and calculations has to be done considering that (we don't know how things are going to turn out in future unless planned).

I assume that result will be updated automatically by excel for that particular cell (in this case, in cell L22) in future if a buy transaction is added in next 30 days..... or as you mentioned, it has be retrospectively adjusted from a loss to not being a loss. No choice but to do that.

Hope this answers your question. Thanks

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
April 19, 2020 - 11:49 am
sp_Permalink sp_Print

But how do I know what shares were bought when?  I don't see where the actual share is identified.  Are you buying/selling the same share (company) in every transaction?

Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
5
April 20, 2020 - 1:14 am
sp_Permalink sp_Print sp_EditHistory

Although different securities are bought over a time, they are recorded seperately in a different worksheet. Each securities are sorted by name at the end of the year and transferred to this worksheet i.e copied to this worksheet(cross-referenced) . So yes, this worksheet is for same security. 

I thought of making a single worksheet to record ( & also calculate loss & gain) for different share(company). But while doing all these calculations & also while using the worksheet routinely many times over a long period, chances are that a person may hit  a button on the keyboard, or do something wrong by mistake.

To avoid that, a different worksheet is made which records the transactions of different shares chronologically by date. The plan is to check each transactions at the end of the year to make sure that there is no discrepancy & then "protect" that worksheet to avoid making any changes by mistake. Once that worksheet is accurate, then this worksheet should calculate the loss/gain correctly (main data i.e date, type of transaction, quantity, price per share, total buy/sell price will be cross-referenced to the original worksheet to maintain the accuracy) . 

This way, transactions can be viewed whichever way you want to view... either chronologically, all different company shares by date, .... OR..  all shares of the same company by date.   

Also, if the same company's shares are grouped together, catching any mistake visually is easier.

The worksheet which I have sent in the original post with my question is an easier stripped down version. I thought that by making it simpler, my question will be easier to understand & answer. Little did I know that it will generate more question/confusion. Sorry about that and thanks for taking time to answer my question.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
April 20, 2020 - 10:17 am
sp_Permalink sp_Print sp_EditHistory

Hi Jay,

You can use COUNTIFS to check the conditions (Loss and within 30 days).  

=IF(AND(I7<0,COUNTIFS(B$3:$B6, ">="&B7-30,$A$3:A6, "=Buy")),"Y","N")

In the attached workbook I've entered this formula into Col L.  If you want to adjust it to take account of Buy's in the 30 days after a loss you can add another condition to the COUNTIFS.

Regards

Phil

sp_AnswersTopicAnswer
Answers Post
Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
7
April 20, 2020 - 12:00 pm
sp_Permalink sp_Print

Thanks for the guidance.

I will try to amend the formula to take account Buy's in 30 days after a loss. 

A small confusion. Did you mean "COUNTIFS" instead of "SUMIFS" when you said, "If you want to adjust it to take account of Buy's in the 30 days after a loss you can add another condition to the SUMIFS" ?

Thanks

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
April 20, 2020 - 12:48 pm
sp_Permalink sp_Print

Yeah sorry, I meant COUNTIFS!  I've adjusted my reply.

Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
9
April 20, 2020 - 2:36 pm
sp_Permalink sp_Print

So finally, I amended the formula to take account of Buys in 30 days before and after a loss and is in the attached workbook. 

I had to change some data and add couple of rows to check & make sure that the formula works properly.

Also, I changed the result of the formula as "YES" instead of "Y" so that it can be detected by just looking at the worksheet.

Now, ... I don't know much about excel... so I am not sure if the formula I have created is just right or can it be simplified further? I just know that it works properly, gives correct result  & serves the purpose. 

Any feedback is appreciated. Thanks once again for your guidance & the time you took to help me out.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
April 20, 2020 - 6:58 pm
sp_Permalink sp_Print

Hi Jay,

You need to wrap the 2 x COUNTIFS in an OR because you want to check both forwards or backwards in time for another Buy

=IF(AND(I7<0, OR(COUNTIFS(B$3:$B6, ">="&B7-30,$A$3:A6, "=Buy"), COUNTIFS(B8:B$100, "<="&B7+30,A8:A$100, "=Buy"))), "YES","N")

If you leave both COUNTIFS inside the AND then you'll get a false when either one of them does not contain a Buy inside 30 days, regardless of whether or not there is a Buy in the other direction i.e. forwards or backwards in time.

Cheers

Phil

Avatar
Jay Patel
Member
Members
Level 0
Forum Posts: 6
Member Since:
April 18, 2020
sp_UserOfflineSmall Offline
11
April 21, 2020 - 2:49 am
sp_Permalink sp_Print

Thanks for the guidance & help. I learned a lot.

Regards, Jay

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1513
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
12
April 21, 2020 - 7:55 am
sp_Permalink sp_Print

no worries. 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Chandler Davis
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:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27212

 

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