Best software to build a database

[andyfuller]

Hi Ethanol - many thanks for taking the time to look through my posts.

I'd definitely consider amalgamating the OpeningTrades and ClosingTrades tables into a new Trades table. Both tables perform the same function.

I can see the logic in what both you and Xitian are saying but the reason I thought about separating them was so I could end up with a clear distinction when I look at a transaction as to what were the opening trades and what were the closing trades. I have attached two screen shots showing two different transactions in Betfair, one is opened by a Buy and closed by a Sell and the other is opened by a Sell and closed with a Buy. (BTW I didn't create the form, someone else did it for me on an Access help forum but they didn't give any explanation so I am still getting my head around what they did and if it is what I want)

Form v1 Image 1.png

Form v1 Image 2.png

If all the trades were all in the one TradesTable I don't see how I could differentiate between what trade opened a transaction and what closed it (perhaps the date?). The end result would be 0 shares held in both transactions but it wouldn't be clear if I Bought first and then Sold or vice versa. Or am I missing something?

I'd also personally remove the BuyOrSell field, as it's redundant. To record this information, you could instead use a positive or negative value in the Stake field (which I presume is the quantity of shares purchased, so I'd probably name it Quantity). This makes general queries more natural; e.g. if you buy 50 shares, then sell 50 shares, then you own 0 shares. No "IF" queries on the BuyOrSell field, just a quick "SUM".

Be careful with the TransactionClosed field from the IndividualTransaction (Transactions) table. If my thoughts are correct, then this should only ever be flagged when the sum of Stake (or Quantity) from the new Trades table is zero. Presently, you could flag a trade as closed which still has an open position. There are checks that can be put in place to prevent this, but it all depends on how fastidious you wish to be!

I agree with these two interlinked points. I had thought about your second point previously, I accidentally said a trade was closed when in fact it wasn't when messing about with the database. Your idea about entering a Sell of shares as a Negative value and a Buy as a Positive value and then summing the position would avoid this as you say.

So I will do away with the BuyOrSell Field and replace it as you suggest with a negative or positive value in the newly named Quantity Field, replacing the Stake Field.

One thing I would like to be able to do though is still include the word Buy or Sell when showing the trades as I find this helps my brain when looking at the transactions (though this isn't essential and something I can add later when I have the bare bones of the Database sorted).

The TransactionsInACompany seems redundant, as it holds the same level of data as IndividualTransaction. I'd be amalgamating these into a new Transactions table.

I have now removed the IndividualTransaction Table but Kept the TransactionsInACompany as I need to be able to link the trades to each transaction eg. When doing a long term transaction in a company I want to keep this separate to any short term transactions I may do in the same company whilst the long term transaction is still open. So I think it correct to keep it. I have attached a new Relationship Table screen grab (the other changes discussed in this post have not been made yet though).

Relationship Table 22_05_13.png

I hope this info helps.

Massively

[Marksi]

Hi Andy,

I've only skimmed this thread (so I may have got the wrong end of the stick), but I've had a quick look at your schema and your example data.

I've modified your schema (see attached)and created it in SQL Server (with your example data: Apple, Betfair, Facebook).

The following is a simple query (followed by the result set)that gets all transactions for a company:

select c.company_name,t.transaction_id,t.trade_id, tt.title, ts.title, t.stake, t.price
from [transaction] tx, trade t, company c, trade_type tt, trade_status ts
where tx.transaction_id = t.transaction_id
and c.company_id = tx.company_id
and tt.trade_type_id = t.trade_type_id
and ts.trade_status_id = t.trade_status_id
and company_name = 'Apple'
order by t.transaction_id, t.trade_id;

RESULT SET (excuse the poor formatting):
Apple 4 1 Buy Open 100.00000000 200.000
Apple 4 2 Buy Open 100.00000000 250.000
Apple 4 3 Sell Close 200.00000000 300.000
Apple 5 1 Buy Open 100.00000000 235.000
Apple 5 2 Sell Close 100.00000000 235.000
Apple 6 1 Sell Open 150.00000000 200.000
Apple 6 2 Buy Close 50.00000000 250.000
Apple 6 3 Buy Close 50.00000000 220.000
Apple 6 4 Buy Close 50.00000000 230.000

N.B. The tx.transaction_id (col2) represents the transaction and trade_id (col3) the trades associated with the transaction. As you can see the trade_id increments everytime a trade is made for a transaction and can be used to determine the order of trades in the transaction.

If you let me know the types of reports you want to get out of the database I can write the SQL/modify the design to get the required results. Hopefully, running queries against a prototype database may help formulate the design and flesh out some ideas.

Regards

Mark

[Ethanol]

If all the trades were all in the one TradesTable I don't see how I could differentiate between what trade opened a transaction and what closed it (perhaps the date?). The end result would be 0 shares held in both transactions but it wouldn't be clear if I Bought first and then Sold or vice versa. Or am I missing something?

Using the date would be the second best solution; however, problems could arise with different systems not having entirely accurate date/times, or with systems using different timezones. The optimal solution would be to set the TradesID primary key as an AutoNumber. Have it set to start at 1, and increment by 1. The minimum value for TradesID will always be the opening trade. Once Quantity equals zero, the maximum value will be the closing trade.

Instead of duplicating data (e.g. with the BuyOrSell and TransactionClosed fields), consider using views to view your data, instead of looking at the underlying tables. This particularly applies to the TransactionsInACompany table. A view for this would allow you to set dynamic fields, such as a textual "BUY"/"SELL" (probably "LONG"/"SHORT" for the Transactions view - "BUY"/"SELL" would be on the view for Trades), the date you opened a transaction (from the Trades table), whether a transaction is still open, the date you closed a transaction, profit/loss, net quantity of shares owned, and whatever else you want really! And all of this would be displayed in one neat row per transaction.

The above point about data duplication also applies to the TransactionDate in the TransactionsInACompany table, as this can simply be derived from the Trades tables, using the opening trade's date; unless, of course, you have a reason for that date to be different.

[andyfuller]

The optimal solution would be to set the TradesID primary key as an AutoNumber. Have it set to start at 1, and increment by 1. The minimum value for TradesID will always be the opening trade. Once Quantity equals zero, the maximum value will be the closing trade.

I get what you are saying but what about the following:

I place a Buy trade to open the transaction, say Buy 100 Betfair shares, this would be the minimum value for TradesID, thus showing it was the opening trade.

I then Sell 50 Betfair shares the next day. This would have the maximum value for TradesID, but the quantity equals +50 so how could I tell if it was a closing trade or an opening trade?

Then later that day I Buy another 100 Betfair shares. This would have the maximum value for TradesID but the Quantity now equals +150 so how do I know if this is a closing or Opening trade?

The next day I Sell 150 Betfair shares. This last trade would have the maximum value for TradesID now and Quantity would equal 0 so it would be a Closing trade.


I am just a bit confused how your idea would work when you add or reduce to a Transaction Quantity, but don't close the transaction, if that makes sense.

[andyfuller]

The above point about data duplication also applies to the TransactionDate in the TransactionsInACompany table, as this can simply be derived from the Trades tables, using the opening trade's date; unless, of course, you have a reason for that date to be different.

I agree with this and hadn't noticed that the person who built the form had added the Field; TransactionDate in the TransactionsInACompany table and removed it from the Opening and Closing Trade Tables.

The Transaction Date should be made up of the date of the first Opening Trade and the Last Closing Trade so as to give the period the transaction was open for.

So I need to include it, either in a single TradesTable or the separate Opening and Closing Trade Tables which ever is the right ones to have

[andyfuller]

Instead of duplicating data (e.g. with the BuyOrSell and TransactionClosed fields), consider using views to view your data, instead of looking at the underlying tables.

Yes I need to get building some forms rather than looking at the data through the Tables.

[Ethanol]

I am just a bit confused how your idea would work when you add or reduce to a Transaction Quantity, but don't close the transaction, if that makes sense.

As we know, we can easily deduce the first opening trade - the record with the lowest index. The final closing trade can be deduced by the record with the highest index and when Quantity is equal to 0. Based on what you've just said, am I right in thinking there may be a case where you'd buy 100 shares, sell 100 shares, and may wish to purchase further shares under the same transaction? If so, then the trade would have been temporarily marked as closed (as technically - it is). You need to decide if this is what you want; otherwise, an alternate plan needs to be developed.

I understand what you're saying above - you want to know what type of trade the intermediate ones are. Am I correct in assuming that if the first trade is a "buy" then all opening trades thereafter will be buys? With some clever coding, you could deduce that quantities with the same type as the first record are opening transactions, and the opposite are closing transactions. Alternatively, if you don't want to go this route, the approach would be to set a boolean field called Open (or similar) within the Trades table; a value of true indicates it's an opening trade, and a value of false indicates it's a closing trade). I personally feel this would be an unnecessary field, as this could be deduced perfectly with logic without the chance for the field to ever be mis-entered, but it does make the initial database implementation simpler.

Yes I need to get building some forms rather than looking at the data through the Tables.

Views work like database tables, so you interact and query these instead of the underlying database tables. The difference being that you can apply logic to them. So above, where I said about "clever coding" and "with logic", and when I mentioned being able to have all sorts of fields, I meant you could create "virtual" fields within a view, and their value would always be correct as they are simply deduced by querying the underlying data tables. By instead hard-coding these fields, there's always the chance that one of the values could be incorrect, thus creating bugs.

If you need me to better explain the views, then I'll try to knock up a simple schema with the views how I would expect to see them.

[andyfuller]

am I right in thinking there may be a case where you'd buy 100 shares, sell 100 shares, and may wish to purchase further shares under the same transaction? If so, then the trade would have been temporarily marked as closed (as technically - it is). You need to decide if this is what you want; otherwise, an alternate plan needs to be developed

Hi mate, no this is not something I wish to do. It would count as a new transaction and not a continuation of a previous transaction. As you say, the original transaction will have been closed.

As I said in a previous post though, I am likely to want to add to an Open transaction, say a Buy of 100 shares, by Buying a further 100 shares, giving a Quantity of +200, I may then partially Close the transaction by Selling 50 shares, giving a Quantity of +150, then I may add to the transaction by Buying a further 150, giving a Quantity of +300 and then partially Close the Transaction by Selling 100 giving a Quantity of +200 and then finally Close the Transaction by Selling 200, giving a Quantity of 0.

Am I correct in assuming that if the first trade is a "buy" then all opening trades thereafter will be buys?

Yes and all Closing trades would be Sells, but only in that transaction, it could differ in subsequent separate transactions. Another transaction open at the same time but in a different company could have been Opened with a Sell, all subsequent opening trades in that transaction would also be Sells and all the closing trades would be Buys.

With some clever coding, you could deduce that quantities with the same type as the first record are opening transactions, and the opposite are closing transactions. Alternatively, if you don't want to go this route, the approach would be to set a boolean field called Open (or similar) within the Trades table; a value of true indicates it's an opening trade, and a value of false indicates it's a closing trade). I personally feel this would be an unnecessary field, as this could be deduced perfectly with logic without the chance for the field to ever be mis-entered, but it does make the initial database implementation simpler.

I agree adding a field called Open (or similar) would be a poor choice for the reason you state, that it could be mis-entered. A bit like the field I had previously to say if a trade was Active or not. So creating some Logic would be a much better option though for now I think that may be a bit out of my scope

If you need me to better explain the views, then I'll try to knock up a simple schema with the views how I would expect to see them.

When you are saying Views are you talking about Forms? I am a little confused.

If you could knock up an example that would be awesome.

Many thanks for all your help it is very much appreciated and is helping me out massively!

[andyfuller]

If you let me know the types of reports you want to get out of the database I can write the SQL/modify the design to get the required results. Hopefully, running queries against a prototype database may help formulate the design and flesh out some ideas.

Hi Mark,

Sorry but some how I missed your post yesterday and have only just noticed it! Thanks for taking the time to have a look through it.

Here is a list of some of the reports/information I would be after, in no particular order:

1. What Companies do I currently have open transactions in (lets say Betfair is one)
2. What trades make up the Open Transactions in Betfair
3. When was the Transaction in Betfair opened
4. What is the value of my open Transactions based on their Opening Trade price's
5. (To be added once I have things sorted on the basics) Be able to input the current share price on Open Transactions and see the P&L on individual Transactions I have selected from point 1 above
6. As above but on all Open transactions
7. P&L on closed transactions that were opened between 2 dates eg. 1st Jan to 31st Jan
8. P&L on trades that were Opened with a Buy (or Sell) and as another report between 2 dates eg. Start of time and today)
9. P&L for a particular Company
10. P&L on certain days of the week, eg. Friday
11. P&L on transactions that were open for x number of days, eg, 1 day or 30 days or more

Hopefully that gives some ideas of what I am after. There are other things (for example relating to notes I will have made but for now I am leaving that out of the Database design) and I am sure there will be new ideas that come into my head.

[andyfuller]

Instead of duplicating data (e.g. with the BuyOrSell and TransactionClosed fields), consider using views to view your data, instead of looking at the underlying tables. This particularly applies to the TransactionsInACompany table. A view for this would allow you to set dynamic fields, such as a textual "BUY"/"SELL" (probably "LONG"/"SHORT" for the Transactions view - "BUY"/"SELL" would be on the view for Trades)

Hopefully I now have this little bit done correctly having spent ages learning about all the different bits.

So what I have now done is still kept the separate OpeningTrades table and ClosingTrades Table (as still not sure how to differentiate between the Opening and Closing Trades so for now have kept 2 tables) but have removed the Field BuyOrSell from them.

---------------------

I have created a Query_OpeningTradeType which pulls from the OpeningTrades Table the following 2 fields:

OpeningTradesID
Quantity

I have then added a field to the Query:

TradeType: IIf([Quantity]>=0,"Long","Short")

So if the value is greater than Zero it displays "Long", if less than Zero it displays "Short"

---------------------

Then on the OpeningTradesSubForm I have added a Text Box next to the detail of the trade and set the Control Source to:

=IIf(IsNull([OpeningTradesID]),"",DLookUp("TradeType","Query_OpeningTradeType","OpeningTradesID=" & [OpeningTradesID]))

Which returns the result from the Query for each OpeningTrade as to if it is a Long or a Short. Then for the space for entering a new record at the bottom of the OpeningTradesSubForm it return "" (i.e. leaves the space blank) because the OpeningTradesID field is Null. I added the IIF and ISNull bit at the end because I was getting #Error appearing in the new record space and after searching on the net this was suggested as the best solution as it means the Query is not run on the last record.

---------------------

It seems to work so hopefully I have done this correctly and I am starting to get my head around Access but still have a long way to go

[Ethanol]

Hi Mark,

Sorry but some how I missed your post yesterday and have only just noticed it! Thanks for taking the time to have a look through it.

I also missed his post when I posted mine... Maybe it was because it's his first post so needed to be approved. Mark and I have slightly different ideas about implementation, so it'll be up to you to choose which way to go.

When you are saying Views are you talking about Forms? I am a little confused.

Sorry - the confusion is down to me. I was using SQL Server terminology. The equivalent terminology in Access would simply be a "query". These queries can then be used almost like any other table. They can be joined and/or queried themselves, they can be viewed in a grid format, etc. It seems in your latter post that you may have latched on to this. I'll just give a couple of quick examples of queries that I'd expect.

Using the tables which I mentioned above (Trades, TransactionsInACompany, Company), I'd be looking to create two queries: qryTrades, and qryTransactions, which can ultimately be used in place of the Trades and TransactionsInACompany tables.

qryTrades:

TradeID*
TransactionsInACompanyID*
Quantity*
Price*
TradeDate*
Long (1)
OpeningTrade (2)

*These are taken straight from the underlying Trades table.

(1) A boolean value for whether the trade is a long or short trade. This is deduced from Quantity. A positive value indicates a "long", and negative a "short".

It seems you have achieved this part of the query from your latter post. To further this, I'd also be wanting a validation check within the actual table, on the Quantity field, to ensure that it can never be set to zero.

(2) A boolean value for whether the trade is an opening or closing trade. The wizardry for this comes from self-linking this table back to itself in the query, by matching (joining by) the TransactionInACompanyID, and taking the minimum TradeID (the original opening trade). If the signs of the two Quantity fields match, it is an opening trade. Else, if the signs differ, it's a closing trade.

For both of the above, you could also display textual information instead of, or in addition to, boolean data. Mark demonstrated that by using the lookup tables trade_type and trade_status you can display this textual data in a user-friendly format from within the raw database, rather than hardcoding the words "LONG", "SHORT", "OPENING", "CLOSING" into your queries.

qryTransactions:

TransactionsInACompanyID*
CompanyID*
CompanyName (1)
TransactionActive (2)
OpeningTradeID (3)
ClosingTradeID (4)
OpeningTransactionDate (5)
ClosingTransactionDate (6)
TransactionLength (7)
Quantity (8)
Profit (9)

*Again, these are taken straight from the underlying TransactionsInACompany table.

(1) Joined from Company
(2) If the sum of Quantity is not zero.
(3) & (5) Taken from the trade with the minimum ID.
(4) & (6) Taken from the trade with the maximum ID, and when the sum of Quantity is zero (otherwise, this is set to null).
(7) Length of time the transaction was opened. Deduced like (5) and (6), then with the value of (5) taken from (6). Null if the transaction is still open (or alternatively, use the current date/time in place of the closing date).
(8) Total number of shares owned for this transaction. Sum of Quantity.
(9) Total profit for this transaction. Sum of all Quantity x Price.

Once these two queries have been implemented, you can then use these to interrogate your data, rather than touching the raw tables themselves.

I appreciate that my approach may seem somewhat convoluted to some (particularly for a beginner), so it's entirely up to you if you choose to follow my approach. I am however a firm believer in reducing any potential bugs from the outset with the intent on creating robust systems.

Let me know if you do need a hand with the queries (although learning how to do these will stand you in good stead for further modifications), as I will have to knock up a quick database in SQL Server to test on!

Enjoy.

[andyfuller]

Cheers for your help again!

I can see the logic of what you are saying about using queries rather than the raw data in the tables for further data analysis.

Unfortunately though my PC died on me yesterday, well actually it just didn't wake up in the morning

I am guessing the power supply (Corsair AX750) has died as there is no light showing on the motherboard when usually there is when it is turned off but not at the plug. I was reading this morning that it may have a 7 year warranty so hopefully it can be repaired/replaced rather than having to buy a new one.

However I am off to run the 56 mile Comrades Marathon in South Africa on Sunday so don't have time to get it fixed before I leave so will have to do it on my return.

As such I can't do anything on the database as all I have is my iPad for now so when I get the PC back up and running in a few weeks I will implement your ideas and update the thread.

Cheers again for your help.

[Ethanol]

No problem.

Good luck with both the computer repairs, and of course the marathon - that sounds like it will be quite a feat!

[andyfuller]

I am back from my marathon and back up and running on a PC, unfortunately still waiting on my new PSU so for now I am on an old machine.

I have been trying to do the following but have hit the blocks:

Using the tables which I mentioned above (Trades, TransactionsInACompany, Company), I'd be looking to create two queries: qryTrades, and qryTransactions, which can ultimately be used in place of the Trades and TransactionsInACompany tables.

qryTrades:

TradeID*
TransactionsInACompanyID*
Quantity*
Price*
TradeDate*
Long (1) - EDIT: I have renamed this "TradeType"
OpeningTrade (2) - EDIT: I have renamed this "OpeningOrClosing"

*These are taken straight from the underlying Trades table.

*Note I have changed the names of the two final fields

(1) A boolean value for whether the trade is a long or short trade. This is deduced from Quantity. A positive value indicates a "long", and negative a "short".

It seems you have achieved this part of the query from your latter post. To further this, I'd also be wanting a validation check within the actual table, on the Quantity field, to ensure that it can never be set to zero.

I have included the validation check on the table so that Quantity can never be set to Zero.

I then began to create the Query, qryTrades with all bar the final field, "OpeningOrClosing":

qryTrades_Image_1.png

qryTrades_Image_2.png

For now I have returned textual information i.e. "Long" and "Short" rather than boolean data and then using a lookup table, tblTradeType, as I find it easier to understand the textual information and am not sure of the advantage of returning boolean data as opposed to textual information.

[andyfuller]

(2) A boolean value for whether the trade is an opening or closing trade. The wizardry for this comes from self-linking this table back to itself in the query, by matching (joining by) the TransactionInACompanyID, and taking the minimum TradeID (the original opening trade). If the signs of the two Quantity fields match, it is an opening trade. Else, if the signs differ, it's a closing trade.

This is where I am stuck for now, with the field OpeningOrClosing in the Query qryTrades

The first step I did was to establish the minimum TradeID for each transaction.

To do this I created a separate Query, qryTransactionMinTradeID:

qryTransactionMinTradeID_Image_1.png

I took the TransactionInACompanyID from the Table TransactionsInACompany rather than the Trades Table. Though I am not sure if this is the right thing to do or not, or if it matters doing this as opposed to taking TransactionInACompanyID from the Trades Table as doing that also seems to return the same results.?

qryTransactionMinTradeID_Image_2.png

I can't figure out how to do the next bit:

...The wizardry for this comes from self-linking this table back to itself in the query, by matching (joining by) the TransactionInACompanyID, and taking the minimum TradeID (the original opening trade). If the signs of the two Quantity fields match, it is an opening trade. Else, if the signs differ, it's a closing trade.

I just seem to be going round and round in circles but not getting any further. Any help on this would be great as once I have this cracked I can move on to the next bits you posted about.