Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgYou may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.
Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about ooBase in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software ®, 3/06.
I am an amateur stock market investor. I buy and sell shares.
Put simply, what I want to do is buy a company when its shares are cheap, and hold those shares for many years while their value rises, I hope faster than inflation. While I hold the shares, it is nice if the company pays a dividend.
From time to time, other investors "go crazy" and are willing to pay me much more than shares I own are worth. So I exchange my shares for their money.
Well. That's the plan, anyway.
Sadly, not only do prices not always read the book I've written, but also governments like to levy taxes.
To stay on top of what I own, and in order that I may prepare the necessary figures for tax payments, I maintain a database in connection with my investing activities.
There are many, many ways you could set up such a database. The following isn't "the right way" to do it. Read what follows not for "the answer", but for the discussion of why the different elements of my system are there.
====
I am going to tamper with reality somewhat in what follows. So when I say "I have two brokerage accounts", you can read that as if it were true, but don't be too sure that is exactly true. While this case study is heavily based on reality, and many inconvenient odds and ends from the real world will intrude to make things "interesting", I am going to streamline things a little, to reduce the clutter.
Un-avoidable "little wrinkles" arise in reality which make simple answers to an investor's needs inadequate. Some of them should become clear as this essay progresses.
My first pre-conception is that computers are weak timorous beasties, and not to be trusted.
Thus I have a transaction log, ink on paper. If the computer and all my backups die, I should be able to reconstruct everything from that. Every purchase, every sale gets recorded there. And a lot more besides. If investing were as simple as just buying 100 shares of XYZ today and selling them five years from now, I'd be a very happy camper. It isn't that simple... even if you have magic answers to the questions of when and what you should buy or sell.
But, having said that, records of purchases and sales are at the heart of it all.
Every month, when my brokerage account statements arrive, I get out the transaction log, and enter a record (a line of information) on that for every transaction relevant to my ongoing positions. I'll give examples in a moment.
From time to time, I fire up the computer, and enter those transactions into a database inside the computer. Once that data has been entered, the computer saves me hours of work by pulling out information that is useful to me.
One of the main reasons for the database is that it allows me to generate a printout of my current holdings. It might look like the table below. The first column "names" companies, using their "ticker symbol", the investment industry standard "codes" for the companies. Many, e.g. "IBM" are obvious. Others, e.g. "TEU", which was the ticker for Canadian Pacific Shipping, have more obscure origins. And some are delightfully whimsical, e.g. "DNA" for Genentech, one of the big biotech firms. But I digress.
The "PIB" column lists how many shares of that company I have in one of my brokerage accounts, which I'm pretending is at a fictitious firm called Putnam Investor's Bank, and the "OBF" column is for the shares I have at my other account, with "Other Brokerage Firm".
First word of wisdom: Avoid o's and i's and l's in abbreviations. They make sense to you at the time you choose them.... and then later you start putting zeros where a letter "oh" should be, ones where a letter "ell" or "eye" is needed.
Another word of wisdom: Where a database is going to have a bunch of entries for something like the two accounts I've got, give them names that start with different letters. Under this rule, it would have been a Bad Idea for me to identify the two accounts as "AcPIB" and "AcOIB". (It slows down sorting operations for a start.)
Sample of my "Holdings" report....
Company PIB OBF ------- --- --- ABD - 93 BAC 200 - CSCO 500 300 (I wish!) DNA 50 - FO 100 -
That will suffice to continue our discussion.
A digression:
My record keeping operates on two levels.
I have relatively up to date records at one level, but that information isn't as reliable as the information available the other way. The less up to date version is more likely to be exactly right.
It works like this....
Once in a while I do a fresh printout of my holdings. I.e. I regenerate the table I just showed you. That table, henceforth, is called XTAB, for "Cross-tabulated table of holdings".
Then, inevitably, something happens. Let's say I buy some more BAC for PIB (by which I mean "my account at Putnam Investors Bank"). Or sell some of the AIG.
Within a few days of requesting the transaction, I'll receive an ink-on-paper confirmation of the trade from the broker.
Once that document arrives....
1) I'll make a manuscript correction to XTAB, with the date of the transaction. In connection with this, I'll put an "X" on the transaction slip.
2) I'll enter the transaction in a whole OTHER system which I maintain. (That other system is about monitoring my investment success, and about recording my reasons for purchases, sales, and... equally important... the times I didn't buy or sell something. In connection with this, I'll put a "P" on the transaction slip. ("P" for Personal Stock Monitor, the program I use for this work.)
3) I'll make a correction to my informal, every-day-use record of the cash I have in the account. In connection with this, I'll put a "C" on the transaction slip.
The slip is then put with the transaction log. When that transaction appears on a statement, the numbers are cross checked, and the slip moves on to long-term storage. (I've needed 10 year old slips before now... usually in connection with class action suits. I merely file them in envelopes marked with the range of dates the slips in that envelope contains. Adequate for the needs that arise.) The slips also provide a double check that I've extracted all relevant transactions from the broker statements. There tends to be "noise" in those statements, which can lead to important items getting overlooked.
So... what does the transaction log look like?
Date Co Shares From To $ 1/1/04 IBM 100 bou pib 8108.78 1/2/04 CSCO -400 pib sold -26490.27 2/2/04 IBM 100 split pib nil 2/4/04 IBM -200 pib obf 2/4/04 IBM 200 pib obf 1/1/05 FO 100 bou pib 8000.00 8/17/05 ABD 23.5018 spin pib 8/17/05 ABD -0.5018 pib sold -11.00
Date: When it happened
Co: Company involved
Shares: How many shares added (+ve) or lost (-ve)
From: The source of the shares....
bou: I bought them from someone
pib: The came from my holdings at Putnam Investor's Bank
split: They arose from a "stock split"... see below
spin: They arose from a "spin off"... see below
To: Where the shares went.....
pib/ obf: My accounts, at Putnam Investor's Bank or Other Brokerage Firm
sold: I sold them to someone
Get a cup of coffee. We're about to go through the example transactions, and it will be tedious... but necessary. Don't skim!
1/1/04 IBM: A nice simple one. I bought 100 shares. They cost me, including fees, etc, $8108.78 Notice that is entered as a positive number. Think of it as my account is worth more, so the number is positive.
1/2/04 CSCO: Another nice simple one. I sold some Cisco. I was paid 26490.27 for them. Notice that is entered as a negative number. Although I've received cash, my stock holdings have gone down.
2/2/04: IBM "declared a 2 for 1 split". Companies "declare splits" from time to time. If it is a "2 for 1" split, then for every share you held before the split, you are given a new one. Don't get excited. Each share is worth half of what the pre-split shares were worth. And your dividend per share will be cut in half. Don't worry, though... if you were getting 10 cents a share on 100 shares, you were getting $10. Now you will be getting 5 cents a share on 200 shares... still $10. Splits are used to keep a company's share price at a reasonable level.
2/4/04: The two lines about IBM on this date are a rather clever way to record the fact that I moved the shares from my account with Putnam Investors Bank to my other account, the one with Other Brokerage Firm. Just read what the lines say... the first says that the number of shares at PIB went down by 200, and the second says that the number of shares at OIB went up by 200. This is a cousin of double entry book-keeping. Seems a bit odd at first, maybe... but it works!
1/1/05: I bought some FO (Fortune Brands) this is here to pave the way for....
8/17/05: In the real world, on that date, for every 100 shares of FO that anyone held, they were given 23.5018 shares of a new company called Acco Brands, ticker "ABD". That is the actual date and numbers.
Now... no one actually received the fractional shares. But it works better to write things up as if they were received. I actually received 23 whole shares, and (we'll call it) $11.00 "cash in lieu", in lieu of the fractional shares I was entitled to.
What you see in the transaction table is all you need to track things properly so far. Spin offs are a pain in the neck, and more consequences will be discussed later in this essay, but for now, that's most of what you need.
Where did ABD come from? The company was created out of things that were previously part of FO. Another example of a spin off that's easier to recognize is the break up of Rolls Royce. Years ago, there was one company, and some divisions made cars, while others made aircraft engines. Eventually, the board of directors decided that the two divisions has so little in common that it made sense to break the "cars+aero Rolls Royce" up into two separate companies. There was a delicious if absurd little squabble after the split up. The aero engine company's lawyers tried to say their company owned the brand, and that the cars couldn't be called "Rolls Royce" anymore. The squabble passed.
So! You've seen a bunch of transactions. They cover most of the things which arise.
From time to time, things happen to change the number of shares I have in something, or where I have them. Every one of those events gives rise to a line in the transaction log. (Those lines will come from lines in my statements from the brokers.)
When it suits me, from time to time I fire up the computer and enter any new transaction log entries into the "xactions" table (Which I will refer to as "Xactions" hereafter. Assume I mean the table when you see that, with a capital "X") of my investments tracking database.
The transactions table has columns (fields) just like those in the transaction log. My data entry form has the columns in the same order as they appear in the transaction log, to facilitating data entry.
Besides the columns from the transactions log, Xactions has the following....
Primary key: An auto incrementing integer. It doesn't "mean" anything, but it is used by the database for various things.
Pool: An 12 character text field. What goes in it, we will discuss in a moment. First a word about capital gains tax.
Whenever you make any money, the government takes some tax.
If you buy some shares for $4000 and sell them for $6000, you have made $2000, and that income is called "capital gains". And there's a tax on it. A complicated tax. How much you pay depends on how long you held the stock between buying it and selling it. Tax laws change, and this is somewhat simplified, anyway, but at one time, in addition you added up all of your gains on things you'd held for a year or less. Let's say that was $2000. You also worked out capital LOSSES. For instance, if you bought something else for $10,000, but sold it for $9,500, then you had a $500 loss. And, for this example, we'll stipulate that you sold those shares less than a year after buying them. ALSO in that tax year, we'll say that the total of your capital gains on things you'd held for more than a year came to $4000 and that your long term losses were $800.
There are rules about how you combine all those numbers... the gains of $2000 (short term) and of $4000 (long term) and the losses of $500 (short) and $800 (long). I won't trouble you with the exact rules. But! Your losses, via formulas, could be, up to certain limits, be set against your gains, and reduce your taxes!
Thus it is important to know when you bought something, and for how much. That information can be a part of decisions about selling things.
The "pool" field is necessary because of the next "wrinkle". The following example uses "real" numbers.
Let's suppose you bought....
100 shares of IBM in January 2004 for $9500, and
100 shares in January 2006 for $8000.
If you'd wanted to sell 100 shares of IBM in September 2008, you would have received $12000, so it would be best to sell the 2004 shares... only $2500 to pay capital gains tax on. If, however, you were selling in November 2008, when you would have been paid $8000, you might want to sell the 2006 shares, if you had no gains to set losses against.
It may seem silly to talk of "selling the 2004 shares", or the "2008" shares... but you need to, even though, in your account the shares all "look" alike, just as different dollar bills in your wallet look alike, regardless of when they went into your wallet.
It is for this sort of thing (and others) that the "pool" field exists.
When shares are bought, a new pool code is generated for that "bunch" (or pool) of shares. I use....
That would make the pool for our 1/1/04 IBM shares "ibm041". (If I make two or more purchases of the same stock in the same month, in some cases I put them in the same pool. If the price has changed radically, between the buys, I will set up two pools by adding a letter, e.g. "ibm041a" and "ibm041b".)
Nota Bene: Here is one of those little generally applicable gems I promised you: For coding dates, it is sometimes helpful to show the month with a single character. Things start simply enough: 1,2,3... for January, February, March... But what about October, November, December? Just use a, b and c. Simple? Yes. And it even works! For some things. Stock pool id codes, for one.
From then on, each time something happens with stock from the pool, the next "something" has the same "pool id" used for the "pool" field of that record in the database.
So: the extra IBM shares received 2/2/04 in the split get the same pool ID as the shares bought back on 1/1/04. They were the source of the new shares of 2/2/04, so the new shares are in the same pool.
When the FO was bought on 1/1/05, it would have been given a pool ID of "fo051". The ABD shares from the FO spin off of 8/17/08 will ALSO have a pool id of "fo051". Again because the FO was the source of the ABD.
The pool codes are invaluable for some work connected with determining capital gains or losses... which I hope to discuss another time.
We've pretty much finished the "putting in" part of the system. Once all that work has been done... and it needs to be done, if only to be on top of what you've bought and sold, and received with splits and spin offs, then it is relatively easy to get the computer to "tell you things".
Furthermore, the way we've done things, while not error proof, are at least error resistant. And they let you trace back how something came to be... and thus perhaps uncover errors which have crept in. You'll never make something "unbreakable", but that doesn't mean you don't try to make it hard for mistakes to happen. And you try to make things susceptible to repairs.
It would be much simpler just to keep a table showing your current holdings. When you bought something, you'd add it in. When you sold something, you'd take it out. But how, for instance, would you later look up how long you held something, for doing capital gains tax reporting? By the time you are preparing a tax return, the sale, and the change to your "Stocks I Currently Own" database could have been as much as a year ago.
Although the database holds "everything" that has ever happened, you aren't going to want to wade through all of that each time you need to check how many shares you have of, say, IBM, at the moment.
Fear not. The computer can run through the data for you, and deliver up the information you need.
More on that another time!
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .