473,903 Members | 6,268 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Number of days stock held

Hello,

Does anyone have a formula for calculating the number of days a
stock was owned? Of course its real easy if you buy 100 shares on
April 1 and sell 100 shares on July 1 but what about multiple buys and
sells of different number of shares.

As an example: Buy 100 April 1, Buy 200 May 1, Sell 50 June 1,
Sell 250 July 1. For tax purposes we need to know the average number
of days the stock was held. In the calculation, I believe you have to
use the FIFO method. I was considering something like share/days but
wasn't sure how to implement it.

Any ideas would be appreciated.

Thanks,
Hank Reed

Mar 27 '06 #1
2 3393
I dont have any code but this is how I woudl lay out the data:-

Table - Stocks
... Holds stock information, ( stockID [unique primary key],company,
nominal value, market traded upon etc)

Table - Purchases
... Holds Purchase information (Purchase ID [Unique Primary], Stock ID,
Purchase Date, Purch Qty, Unit Value, Total Purchase.

Table - Sales
... Holds Sales information Sales ID [Unique Primary], Stock ID, Sale
Date, Sale Qty, Unit Value, Total Sale price

Table - SP Matches
... Holds Match ID [unique Primary] , Stock ID, Purch ID, Sales ID, Qty
As each Sale could come from many purchases and one purchase could
result in many sales, the SP Matches table would be used to work out
how many of each Purchase instance went to each sale. You can then
work out the # days for each assignment and the net gain or loss.
Weighted averages can then be applied if necessary.

You would create the SP match records by looking at the sale qty and
finding the last unmatched purchase record for the stock and generating
the SP match record with the lesser of unmatched Purchase Qty or sale
qty. If the sale qty > unmatched Purchase Qty, you would need to
create a second SP Match record for the balance against the next
purchase. Obvioulsy you would want some sanity checks so that you
could not match sales with purchases that happened after the sale
(unless you are playing futures markets!!)

You would probably also need to consider what you would do for share
splits and consolidations / rights issues / sahre conversions.

Incidentally this technique is also very useful for matching invoices
to reciepts if you are running any sort of accouting functions.

Mar 27 '06 #2
Aaron,

Thanks for the input. I actually download the transactions from my
stock site (unfortunately in .csv format) I convert them to an .xls and
then import them straight into my database. So all the buys, sells,
dividends, and splits are in one table, but they are clearly marked and
easy to sort through.

Your approach makes sense. Each Sell must be reconciled against a
previous Buy. I don't do short sales or futures. Using the FIFO
idea, which is what you are doing, I can calculate the actual number of
days each share is held. I'm sure you know that you are taxed much
higher on profits on stocks held less that one year. Kind of takes the
fun out of day trading, doesn't it?

The dividends only effect profit and loss, and not days held, so I
ignore them in this calculation. However, it was good that you pointed
out the situation with the stock splits ( I forgot ) because they are
just another Buy with no cost. And again we are just concerned with
days here, not dollars.

I do keep a separate table with the basic stock information, like
Symbol, Company Name, and almost anything else I can dredge up from the
stock analysis screen. I call them tiebreakers and you can pick
anything that you think will help make a decision. Analyst's
recommendations , Technical analysis, PE, Volume, et cetera.

In my day job ;-), I do reconcile Invoices but, unlike my stock shares,
they are serialized or at least numbered.

I'll keep working on it.

Thanks,
Hank Reed

Mar 27 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1960
by: Marion Slaughter | last post by:
----FA48570F00BB51C Content-Type: text/html; charset="iso-35B2-E" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Untitled Document</title> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859=
7
15158
by: Shuffs | last post by:
Could someone, anyone please tell me what I need to amend, to get this function to take Sunday as the first day of the week? I amended the Weekday parts to vbSunday (in my code, not the code attached), yet when I ran it for 28/09/2003 (UK date format) it returned Week 39. I would have expected it to return Week 40. However, I'm really stuck and my head is busting over this, so any pointers would be gratefully appreciated. Many thanks...
52
1565
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How do I convert a Number into a String with exactly 2 decimal places? ----------------------------------------------------------------------- When formatting money for example, to format 6.57634 to 6.58, 6.5 to 6.50, and 6 to 6.00? Rounding of x.xx5 is uncertain, as such numbers are not represented exactly.
1
1332
by: cknkomo | last post by:
Hie I am learning VB and I have a table with the following fields: - EmployeeNo - Date of Engagement - From Date - End Date - Days Acrued - Days taken
2
2037
by: tyeung168 | last post by:
I am taking a C++ class in college and having problems completing last week's homework. Any help is greatly appreciated. The program is to read from a filing containing stock trades. Here is an example: CSCO 200 92.5 100.3 QQQ 250 24.52 21.52 DELL 600 25.45 27.52
1
5799
by: asmusdk | last post by:
I am working on creating a stock management database for a small company. The company receives the merchandise from a handful of suppliers and stocks it before it is sent to the customers – retail outlets. The company works on a concession basis – i.e. the customers do not pay for the delivered goods but sell it on and then take a percentage of the sales. The goods delivered to the retailers can be considered to be “in stock” at the...
1
3627
by: zafm86 | last post by:
Hi everyone! I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore. I'm working with an AS400 and I mhave to do an "interface" in Access. It's like a little system to make a production plan. It must get information about part numbers, description, set where the part number belongs, quantity of part numbers that the set uses, stock number(this is a text value, it...
0
1138
by: john | last post by:
SETP-08 Draft paper submission deadline is just few days from now The 2008 International Conference on Software Engineering Theory and Practice (SETP-08) (website: www.PromoteResearch.org ) will be held during July 7-10 2008 in Orlando, FL, USA. We invite draft paper submissions and deadline for submission is extended until February 19 2008 due to several requests from the authors. The conference will be held at the same time and...
0
1234
by: john | last post by:
EISWT-08 Draft paper submission is deadline is just few days from now The 2008 International Conference on Enterprise Information Systems and Web Technologies (EISWT-08) (website: www.PromoteResearch.org ) will be held during July 7-10 2008 in Orlando, FL, USA. We invite draft paper submissions and the deadline is extended until February 19 2008 due to several requests from the authors. The conference will be held at the same time and...
0
9999
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9848
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10986
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
8049
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7208
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6094
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4727
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.