You may find that the database included in OpenOffice (and Libre Office, I believe, but I make no procises for that variant) delights you as much as it has me. This page tries to help you use it.
Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!
There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.
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 fully explained, and there are more tips, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software, 2/06-7/15.
I recently had an unexpected success with something. I have to confess that I don't fully understand what is going on (!), but maybe what follows will be of use anyway. Although my example comes from the world of the stock market, I'm sure that the techniques have wider application.
This tutorial is rather longer than most, but it doesn't "lose the plot"... it just happens to cover a lot of ground! If you are just getting started with ooBase, for now you may prefer to spend your time "knocking down" some of the "low hanging fruit", but eventually what is in this tutorial will be useful to you. It illustrates some rather neat extraction of data in fields from multiple tables via a query using links creating joins between the fields in the different tables. One table is even linked to a "master" table two different ways!
As I said, this tutorial is not typical of the others on my site. Before it gets to the database "how to" material, it spends quite a bit of time discussing an area of stock market investing, leading to a "real world" database application from that industry. I hope you will find the information on stock option trading interesting in its own right, along the way.
The tutorial then takes you through creating two tables, with the sample data which we will need in order to test that our query with data from multiple tables on it works. There is some material on why certain choices were made in the design of the tables. I cannot stress too much that if you don't choose the right tables with the right fields, any database project is severely compromised from its outset.
And then, at last, the tutorial does go into setting up the query! Some discussion of what is going on as the query does its work finished the "main course" of the tutorial. It concludes with a little item on creating a calculated field, as "dessert".
A cruel critic might call the tutorial "rambling". I prefer to say that it is more "in depth" than some of my other attempts to help you become more fluent with ooBase.
You can download a copy of final form of the database. Details at end of tutorial.
First a little exposition of some stock market things, which I hope you will find fun and interesting, even if you aren't an investor.
If you make the right choices, buying and selling shares you make money. If you fail to do your homework, or misjudge situations, then you give your money to the people who made the right choices. I bought some shares of Google from someone in September 2005 for $300 per share. In June 2007, as I am writing this, there are people willing to pay me $500 for each of those shares. I did well... so far. Should I sell now? Who knows. In six months time we will know what I should have done.
On the other hand, there are the shares of ESST which I sold to someone else about a year ago for $800. If I had kept them, I could have sold them for $1300 today.
I was right when I said that Google shares were worth more than $300; I was wrong when I said that the ESST share price was going to fall.
So far, so easy.
There's a more arcane way to participate in the stock market. For not very much more nuisance than getting yourself in a position to buy and sell shares as described above, you can get yourself set up to buy and sell "options". There are two types that interest me: "calls" and "puts". They are similar, and I'm about to describe both.
What follows is the imperfect understanding of an amateur investor. It should not prompt you to buy or sell anything. If you are inspired to go consult an informed teacher, great. If the explanation helps you see why I made the database I am going to describe, it will have accomplished its purpose.
If I buy a call from someone, I have bought the right (we've signed a contract) to demand from him, at a time of my choosing, a certain number of shares in a particular company, at an agreed-now price. The only other "twist" is that options "expire". That right which I've purchased only lasts for a fixed period of time.
The nice people at Muriel Siebert & Co , who know far more about this than I ever will, have put together more information how to trade options, by the way, but they changed the link to the right page, so I've only given you their home page. It is probably worth digging for the options guide.
But, going back to a basic explanation, just so that you can understand the database work that I did....
So. When you buy a call, you and someone else have entered into an agreement. Let's say you "bought a call" on Google, with a "strike price" of $530, for September 07. On 14 June, 2007, that would have cost you about $1870 for "one contract". Google was trading at about $505 per share back then.
Let me expand on a few things....
"One contract" covers 100 shares. The call described entitles you to demand 100 shares at a price of $530 per share. (If you choose to make that demand... and it is your choice... then you will also have to find the $53,000 ($530 x 100), in addition to the $1870 you paid for the contract.)
Hopefully, that makes clear what "strike price" means? ($530, in this instance.)
In the circumstances I'm discussing, calls (and puts) expire at the end of the third Friday in the month, so those September '07 calls expire on September 21st.
Why would anyone buy or sell a call? The seller of the contract has decided that the price of Google should not rise as far as $530 before then. You think it will go over $530... at least roughly $18 over the $530.
If he is right, you will never execute the option, i.e. you will never tell him to cough up the shares. (Why would you pay him $530 for shares you can buy "in the open market" for less?) And you will have wasted your $1870.
If you are right... let's say Google soars to $560 per share... then you will demand that the seller deliver 100 shares to you at $530 each. With what you paid for the option, they effectively cost you $548.70... but as soon as he delivers them to you for the additional $530 per share at the time you exercise the option, you can sell them on at $560 each. $11.30 profit per share! :-)
So... that's a call. Remember: you can be a buyer or a seller of a call.
Puts are similar, and not essential to the database work, which we will come to in a moment, but let's just have a quick look at puts first.
A put is like a call, except that the direction of the shares travel is reversed. If I buy a put, then at any time up to the expiry of the put, I can tell the seller "You will buy from me the number of shares we agreed, at the price we agreed." When I buy a put, I buy the right to "put" something in the portfolio of the put seller. (And some of his money in my pocket in exchange.)
You might buy a put if you owned some shares and believed that the price might fall sharply in the next couple of months, but for some reason didn't want to simply sell the shares now. The put is like insurance. And like insurance, you pay "a premium". (It's even called that.) (You can use calls to "insure" yourself, too.)
As I said, you can be the buyer or the seller of a put. So! Four opportunities! Buy or sell, a call or a put! I suppose that I should stress that some things you might do can cost you a lot of money. What if you were the seller of the call on the Google, described above. Suppose the price goes to $560 before the person you sold the call to decides to exercise the option, and you've done no hedging? (Don't worry what that is... if you don't know, you probably wouldn't have done it, would you?) You're going to have to buy 100 shares of Google at $560 each (You're going to need, albeit briefly, $56,000) And you're going to have to "give" them away for only $53,000. The $1870 you collected earlier is going to be scant consolation. And of course, the Google, theoretically, could go even higher. (Although the pessimist in me says that it won't, as long as I'm holding some. Oh well. The $200/ share so far isn't bad. Shouldn't complain.)
And now we're almost to the database stuff, but, hang on to your hats...
What if we were to sell some Google $530s and buy some Google $560s? (They'd cost about $10 per share today.)
On that day, we would end up with a net profit of $870, assuming we do one contract (for 100 shares) of each.
Now... if the price doesn't go above $530 before September 21st, then you've "won" and you get to keep all of the $870.
If it goes to $531, the person you sold the $530 calls to is going to exercise those calls. To find the shares to deliver will cost you, one way (buy in market) or another (take from your existing holdings, for less than you could have liquidated them elsewhere) $1 per share. You've still made a profit of $770, which is nice.... but $870 was nicer!
If the price of Google reaches $560, the bad news is that you are now losing $2730 ($3000-$870)... but that is all you will lose. No matter how high Google's price rises, the call you bought will supply shares at $560 to meet your obligation to supply shares at $530. Pity about the $2730, though. Guess you shouldn't have been so pessimistic about Google's price. Guess the person who paid you to take the risk that it wouldn't go above $530 judged Google's prospects more skillfully.
You find out what puts and calls are available, and what they would cost, from Yahoo's Finance site. Use the "Get quotes" button, and once you have a chart on the screen, select the "options" item in the menu bar at the left. Once you have some call and put prices on the screen, for different strike prices, to notice that they are all for a given expiration date. At the top of the table there are links to pages for prices for other expiration dates.
Well... that's it. We can move on to the database now. Remember that the comments on buying and selling puts and calls could contain errors. You should not do any buying or selling based on those comments.
In this tutorial it will be obvious that there are many things that I don't know. One of the things that I do know is that your tables are the foundation of your database. Get them right, and everything else is a lot easier. One of the signs that you have them right is that no datum needs to be entered twice in a table. It may appear twice in the database if it is needed to link a record in that table to one or more records in another table. You will get things entered "twice" so that you can link records from different tables, but even there, the datum will usually be entered only once in at least one of the tables. Take a database listing my sightings of birds. One table lists all the birds I might see... each bird being listed once, and in the table, an abbreviation for that bird is defined.
The "sightings" table would have records for date, time, place... and bird seen. Each sighting would create one record.
With those tables, I could list the birds seen on a given date, or I could list the dates a give type of bird was seen.
What that database would NOT have is the birds' names written out again and again. It would be too much typing, and too easy, say, to call something "mallard" in one record but "mallard duck" in another. Not to mention typos, e.g. "mallrad". As I said, in the sightings table, the bird would be identified by a code.
All of that is by way of preparing you for something that I'm going to describe in a moment, which may seem odd at first.
It is always a good idea to know where you're going!
So,... what do I want from my database about options trading?
I want to be able to pull up information on "deals" I could have done, deals of the "sell some low-strike-price calls and buy some high-strike-price calls" type.
To make data entry easy for this discussion, pretend that you would want to specify dates with mere numbers... Instead of saying "option deal done on 18 June 07", we'll say "deal done on day 4". In the hypothetical situation the sample data postulates, we are already in, say, day 6. Something beyond 4, anyway.
If you have your browser window adjusted to give you a narrow column for reading this, in general a good idea, I have to ask you to maximize the width for a moment.
I want to see things like.....
Day Company Expires Strike1 Proceeds Strike2 Cost 2 IBM Oct07 110 2.70 120 0.55 4 GOOG Jan08 510 47.20 550 29.15 2 IBM Oct07 110 2.70 130 0.10 3 IBM Jan08 110 4.60 120 1.52
(Those are roughly right for 14 June 2007, by the way, although the table says they came from various days because my tables would typically have information on different days' trading.)
I'm going to go through the first line, to clarify the column headings' meanings:
The first line says that on day 4, I sold a contract for the Oct07 IBM call at $110, which put $270 in my pocket. On the same day, I sold a call for the Oct07 IBM call at $120, which cost me $55. (Eventually, but it is beyond the scope of this, I want the report (form? query?) to tell me that my net would be $215 ($270-$55)... should be easy.)
Now... remember I said I think I'm getting the hang of designing tables?
I think what we want for this exercise is the following two tables:
First table: OptionsPrices.
Day, type Integer [INTEGER] Co, (for "COmpany"), type Text[VARCHAR], length 8 Strike, (for strike price), type Decimal[DECIMAL], places 2 Expires, type Text[VARCHAR], length 5 Price, type Decimal[DECIMAL], places 2
A few comments arising:
"Expires": In a real application to do this job, you might use the "Date" datatype, so that you could ask questions like "How many days are left before this option expires". For this tutorial, the extra data entry chore wasn't seen as worth the effort.
The two price fields were made type Decimal because this is the type the ooBase wizard uses for such things.
The primary key is made from the combination of the Day, Co, Strike and Expiry fields
Sample data for this table:
2 IBM 110 Oct07 2.70 2 IBM 120 Oct07 0.55 4 GOOG 510 Jan08 47.20 4 GOOG 550 Jan08 29.15 2 IBM 130 Oct07 0.10 3 IBM 110 Jan08 4.60 3 IBM 120 Jan08 1.52
Notice the following:
The data in the first line of the output we want was drawn from the first two records in the OptionsPrices table.
Even though we have the Oct07/ 110 option, at 2.70, in the output twice, it is in the OptionsPrices table only once.
Here and elsewhere: CaSe MATters!!!... You can use GOOG or goog, or Goog... but stick with one of them. The same applies to the dates. "Oct07" or "oct07", etc... but not a mixture. ("Goog" and "goog" will be seen as different as, say, "xom" and "ibm".) The case matters in respect of the data you enter, and also in respect of the names you give fields.
To create what I want, the way I want, we need a second table. It list the deals we have done (or considered)....
Second table: OptionsPlays
Index, type Integer[INTEGER], set AutoValue true Day, type Integer [INTEGER] Co, (for "COmpany"), type Text[VARCHAR], length 8 Expires, type Text[VARCHAR], length 5 LoStrike, type Decimal[DECIMAL], places 2 HiStrike, type Decimal[DECIMAL], places 2
The Index field is to provide a primary key.
Sample data for this table... the data that would give rise to our sample "desired output":
2 IBM Oct07 110 120 4 GOOG Jan08 510 550 2 IBM Oct07 110 130 3 IBM Jan08 110 120
Don't miss the differences between these tables. The first just tells you what different things would have cost you on different days. The second is more complicated. Each record describes a deal you either did or considered. To keep the example simple, we are assuming that each record describes the purchase of just one contract and the same of one contract, and that both will be for the same expiry date... although they will be for different strike prices.
I hope that's clear, because I'm going on to describe how you can create a query to produce the output we wanted. Just one little thing: Notice how we've made two, separate references to the Oct07 IBM at 110? Because of the way we've set up the tables, there's no danger of that being shown as costing , say, 2.70 in one place, but maybe 7.20 someplace else.
Start a database called OptionPlaysDemo
Build the two tables described, and put the sample data in them.
Now we're going to build the thing that pulls together the fields we want into a single result table. Once we have that, we can look at working with it. Changing the data in the result from the query does alter the entry in the underlying table. The query is a way to look at what's in the table. It is more than something copied from the tables. A report, on the other hand, even a dynamic report doesn't have this "backward facing" connection to the underlying tables. Data comes out of tables, directly or through a query, to populate a report, but once created it is an "unattached" ooWriter full of data from the database.
Even if you are accessing the table through a query through a form, you still make changes in the table is you change what you see via the form.
So... moving towards the output we wanted:
Start a query, using the wizard.
Step 1: From the OptionsPlays table, select the everything except the index field. (You may see "OptionsPlays." in front of each name.)
Still in Step 1, (and you can use the Back button if you clicked "Next" too soon!) select the Price field from the OptionsPrices table for inclusion in the fields in the query. Now click Next.
Step 2: We don't need to set a sorting order. Click Next.
Step 3: We don't need to set search conditions. Click Next.
Step 4: Again, just click Next, which will skip us over steps 5 and 6.
Step 7: We don't need to, but let's set make "Company" for OptionsPlays.Co (instead of "Co"), just to watch what happens. (It will give us a more meaningful column heading for the "Co" data in our final result.) Click Next.
Step 8: Assign a name to the query. I used OptionsPlaysAndPricesQuery. Make sure "display query" is the selected option, take a deep breath (don't be alarmed by what comes out) and click Finish.
Yikes! Twenty eight records! This isn't what we wanted!
What has happened is that we've "sliced and diced" the records, and listed every combination of the pieces. Don't worry about it too much. We're going to "fix" things next. The "sliced and diced" phenomenon is discussed in more detail in my tutorial about using relationships.
Close the displayed query. Re-open it, but for edit.
You should see a table at the bottom of the resulting window, with rows for Field, Alias, Table, etc.
Above the table, there should be an area for a GUI linkage management tool. There should be two objects on the "page"... on labelled "OptionsPlays", one labelled "Options". (If you don't have that, but maybe have some text like "SELECT "OptionsPlays"."Day" AS "Day"...", then click View | Switch Design View...)
We're beginning to get into the area that is "just magic" for me. But what I describe works! But try to think about WHAT is happening, WHY it works. I hope you make more progress than I have so far, and that I "get it" soon! (I do have (Decmeber 2009) the beginnings of a grasp!)
Part of the reason that it was (and up to a point (at December 2012) still is)) "just magic" is that I was not clear about Relationships and Joins. They are important, and are not the same thing, but the differences blur in my mind, and I hope to help you fight that problem. Any database with multiple tables should have relationships set up in it, and, thankfully, this isn't so very difficult. Joins become important when you start doing, as we are about to, queries pulling data out of multiple tables....
We're in the query design window. To make our query better, we need to work on its joins.
Click "Insert | Add Table", and use the resulting dialog to add another instance of the OptionsPrices table to the GUI area. (The system will have put the first instance of it there.) The new instance will be labelled "OptinosPrices_1". (You can then close the "Add Table or Query" dialog window.)
Arrange the three boxes in the order, left to right, OptionsPrices, OptionsPlays, OptionsPrices_1. Also make the boxes bigger, if necessary to get rid of scroll bars. (And you can change the height of the panel they appear in, if it would be helpful, just by getting the mouse on the right spot (mouse pointer will change) and dragging gently.))
Now we're going to "draw some lines" between things. The lines will define some "joins". I'm not sure that it matters here, but in some similar places it does matter, so just be on the safe side, and draw the lines in the direction given. When done, you should have...
Drawing a line is done the way you would drag something from a to b....
Use that technique to draw lines (create JOINS) between....
OptionsPlays.Day - > OptionsPrices.Day OptionsPlays.Co - > OptionsPrices.Co OptionsPlays.Expires - > OptionsPrices.Expires OptionsPlays.LoStrike - > OptionsPrices.Strike
OptionsPlays.Day - > OptionsPrices_1.Day OptionsPlays.Co - > OptionsPrices_1.Co OptionsPlays.Expires - > OptionsPrices_1.Expires OptionsPlays.HiStrike - > OptionsPrices_1.Strike
Run the query again... OR use the "run query" button on the Query Design window's toolbar. (You're in the Query Design window. The "run query" button is marked with two forms with a green tick in front of them. Get the right one, and a new window opens in the Query Design window, showing you what the query returns. (Don't click the one with the red x without taking your heart medicine first... it deletes most of your work. "Edit | Undo" from the menu will restore what you have, if you haven't panicked and done to many other things. (Ctrl-Z doesn't work, by the way.)
"Ah!..." (I hope you're saying) "... that's more like it." Look closely... it's not EXACTLY what we want... a column is missing, and the order is wrong... but it's close.
(Do click the "save" button from time to time, as with any computing work.)
We need to add a column to the query by supplying our requirements in a column of the table at the bottom of the window. Click on the first empty "Table" cell to the right of a used cell. Select the OptionsPrices_1 table.
In the Field cell above this, select "Price" (you may need to use the listbox's scrollbar to see it.)
The "visible" box should have ticked itself.
Run the query again. The first line should read...
2 IBM Oct07 110 120 2.70 0.55
This is pretty good! The query picked up....
From a record of the OptionsPlays table:
The play we made on day 2, with IBM, when we sold a call at 110, and bought one at 120. Look in the OptionsPlayed table and find that record. Okay? But! We also used the OptionsPrices table to look up the fact that the "Oct07 IBM for 110" sold at 2.70 on day three, and the "Oct07 IBM for 120" sold at 0.55 on day three. Cool!
Make the alias for the OptionsPrices.Price field "Price of LoStrike", and make the alias for the OptionsPrices_1.Price field "Price of HiStrike".
Relationships and Joins: As soon as your databases have more than one table... which shouldn't take long... you really, really ought to start setting "relationships" in the database. And before long, you will need "joins". They are not the same thing, but the differences constantly blur in my mind, and I hope to help you fight that problem.
It would make better sense, I think, if the fields were in the order.....
Day, Co, Expires, LoStrike, PriceOfLo, HiStrike, PriceOfHi.
Nothing could be simpler... when you know how.
In the Query Design Window, just put your mouse pointer in the grey rectangle in the unlabeled row above the "Field" row, and drag the columns left or right. You even get a helpful little arrow to give you confidence!
But some of it was more by luck than design.
It seems that something made the OptionsPlays table "king". Our final table had one row for each row in the OptionsPlays table. Why not one row for each row in the OptionsPrices table? Was it because it was an OptionsPlays field occupied the first column of the design grid?
Within each row, there were two prices picked from records in the OptionsPrices table. I think what's going on is the following. I know it is the links which are responsible, but I also know I have a lot to learn about links. More anon. But first:
The generation of the query proceeds a line at a time.
The system picks up one record from the OptionsPlays table, the "king" table. Then, because of the links, it looks in OptionsPrices a first time, looking for records there with the same values in OptionsPrices.Day, Co, Expires, and Strike as in, respectively, OptionsPlays.Day, Co, Expires, LoStrike. It finds just one, because of the nature of our data. (I believe that in other circumstances, it might find several, but that would pose no problem... it would just result in several rows in the query results.) The "magic" bit is that the row found becomes available as the source of a field (or fields) in the answer query. We only used one of them... but note: It was a field NOT involved in any link. The link seems to act as a filter, but a filter that returns a different set of results with each line from the "king" table.
The system further looked to see if it could find records, again in OptionsPrices, where OptionsPrices.Day, Co, Expires, and Strike matched, respectively, OptionsPlays.Day, Co, Expires, HiStrike. And again we had a record... a different record... from which we could use fields... any fields.
Think you half understand? Right-click on one of the lines in the GUI link designer. Click on "Edit". We may half understand the inner join that we've made. Anyone want to try to add right joins and left joins to what we understand. And how, exactly, are links and relationships connected. (They are... but how?) And did you know that you can define relationships either at the level of the database tables, or within the queries? And within, for all I know, reports and maybe even forms?
Oh... and if that isn't enough fun, use the menu for "View | Switch Design View On/ Off", and you'll see the SQL for what your query. Just think! If we knew SQL, we wouldn't be at the mercy of the GUI! (Do "View | Switch Design View On/ Off" again to get the GUI back.)
It's all such a lot of fun! But hey! We got something working.
One last little frill I couldn't resist.
The difference between the proceeds you get for selling a low-strike-price call, and what you spend on the high-strike-price call you buy at the same time is important. Let's get the query to display it!
In the first as-yet-unused column to the right of the used columns in the query design grid, put the following in the "Field" row...
(I selected "OptionsPrices_1.Price" with the listbox, did Ctrl-A to select all of it, then Ctrl-C twice, to get it twice, then added the minus sign and took out the extra "_1" "by hand".)
Put "Difference" in the Alias row. Save query. Run query. You should get an extra column, with the difference between the two calculated and displayed for you.
By the way... something like this, a value you can calculate from already present fields, is NOT a good thing to add to a table as an additional field, usually. In effect, it breaks the "don't enter anything twice" rule. Let's look briefly at another example of NOT entering anything twice. Suppose you owned shares in some companies outright, not mere options.
You could have a "holdings" table with company name, number of shares, and what you paid for the shares. (You should always track that last item carefully... and it is not as easy as you might hope.)
Now... "what you paid for the shares". You can either record the price per share, or the price for all of them together, i.e. the price per share times the number of shares... but don't record both figures in the table. As long as you have one of them, and know the number of shares, you can always calculate the missing number. Entering both numbers in the table would be an instance of "entering data twice"... which often ends in tears.
If you would like, you can download a copy of the database, but it would be better to work through the tutorial, to be sure you really understand the relevant skills.
I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.
I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.
Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....
Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)
Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)
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 . . . . .