Best software to build a database

[andyfuller]

Hi mate, many thanks for the reply. I am not at my PC with the database on now but will pop up some images of what I have come up with so far in the morning and would appreciate your feedback/advice.

Cheers.

[andyfuller]

I'm unsure what you mean by "position", since to me your position is really just a list of trades. Why not just have a "Trades" table?

What I mean by Position is a transaction. I will stop using position as I think that confuses things and use transaction instead.

A transaction I am saying consists of two things:

1. Opening Trades eg. Buy 100 XYZ
2. Closing Trades eg. Sell 100 XYZ

These combine to form a transaction.

To add to that a transaction can be made up of several Opening Trades and/or several Closing Trades:

Eg.

Transaction a)
1. Opening Trade - Buy 100 XYZ
2. Closing Trade - Sell 100 XYZ

Transaction b)
1. Opening Trade - Buy 50 XYZ
2. Opening Trade - Buy 50 XYZ
3. Closing Trade - Sell 100 XYZ

Transaction c)
1. Opening Trade - Buy 100 XYZ
2. Closing Trade - Sell 50 XYZ
3. Closing Trade - Sell 50 XYZ

Transaction d)
1. Opening Trade - Buy 50 XYZ
2. Opening Trade - Buy 50 XYZ
3. Closing Trade - Sell 50 XYZ
4. Closing Trade - Sell 50 XYZ

I want to be able to link each transaction to one company, which therefore means each Opening and Closing trade are in effect linked to just the one company (the same company as the transaction that they form).

Also I want to be able to link a Opening Trade to a Closing trade and vice versa. Eg:

I have:

1. Opening Trade - Buy 100 XYZ
2. Opening Trade - Buy 100 ABC
3. Closing Trade - Sell 50 XYZ
4. Closing Trade - Sell 100 ABC

This I want to allow me to see what Companies have Open Transactions and what these Transactions are made up of to date. So I should be able to see that I have still to Sell 50 XYZ to close that transaction and that so far I have Bought 100 XYZ and sold 50 XYZ.

Does that make any sense?

I have attached an image of my relationships that I have it set as so far, the TradeType table consists of Buy and Sell

Any help/advice most welcome!

[steven1976]

Can you not get someone on freelancer to help you out initially Andy and then later build on their work? I've never used it myself but I've got friends who have used it and been happy.

[andyfuller]

I might go down that route and was suggested RentaCoder. I would quite like to build it myself but if it is a real hassle I will try to get someone else to do it for me.

There doesn't seem to be that many people around who use Access, most people seem to just opt for Excel. Wish I had paid a bit more attention at Uni now when we took the Access course (so we could be on the computers and punt )

[andyfuller]

In order to get your current "position" or your position at any other point in history, you just run a query to sum the trades for that specific company up until that point.

I can see how that would tell me how much XYZ I still needed to sell say, so that I held no XYZ stock. But would it be able to show me at what price I bought at on this particular transaction and what prices I have sold out at on this transaction to date? I don't think it would?

This is kind of what I am aiming for (I have edited this example below a few times having thought about it more):

__-Company

________- Betfair

________________-Transactions
________________________- Transaction 1
________________________________- Opening Trades
________________________________________- Bought 100 @ 800
________________________________________- Bought 100 @ 850
________________________________- Closing Trades
________________________________________- Sold 200 @ 900

________________________- Transaction 2
________________________________- Opening Trades
________________________________________- Bought 150 @ 700
________________________________________- Bought 150 @ 710
________________________________- Closing Trades
________________________________________- Sold 200 @ 750


________- Facebook
________________- Transactions
________________________- Transaction 1
________________________________- Opening Trades
________________________________________- Bought 500 @ 100
________________________________- Closing Trades
________________________________________- Sold 500 @ 105


________- Apple
________________- Transactions
________________________- Transaction 1
________________________________- Opening Trades
________________________________________- Bought 100 @ 200
________________________________________- Bought 100 @ 250
________________________________- Closing Trades
________________________________________- Sold 200 @ 300

________________________- Transaction 2
________________________________- Opening Trades
________________________________________- Bought 100 @ 235
________________________________- Closing Trades
________________________________________- Sold 100 @ 235

________________________- Transaction 3
________________________________- Opening Trades
________________________________________- Sold 150 @ 200
________________________________- Closing Trades
________________________________________- Bought 50 @ 210
________________________________________- Bought 50 @ 220
________________________________________- Bought 40 @ 230

I then want to be able to run a Query that would tell me:

1. I have Open positions in Betfair and Apple
2. See the Transactions, so Transaction 2 in Betfair and Transaction 3 in Apple and then also see the details of each of these Transactions, so the Opening and Closing Trades

In order to do this I think I need to be able to flag each Transaction as either Active or Unactive, one way is to have a check box against the transaction, perhaps another is to have it be dependant on if the number of Shares bought = the number of Shares sold.

Does that help explain what I am after a bit better?

[fretinapaul]

Hi Andy, still looking for a solution???

[Will Sharpe]

Have you gone down the route of checking what's already available in terms of portfolio management software?

Seems like what you are trying to achieve must be incorporated in lots of commercially available packages, there might be free versions as well.

[andyfuller]

Hi Andy, still looking for a solution???

Yes.

Have you gone down the route of checking what's already available in terms of portfolio management software?

I have had a look around but I have been unable to find anything that suited my needs and also allowed me to hold onto the raw data for future use - say in 5 years time. I could just not have come across the right thing yet though.

[Zapata]

Have you had a look at Proform, there are user fields and personal note entry fields. I have never used them but have seen them there. The database would be built for you too.

[andyfuller]

Have you had a look at Proform, there are user fields and personal note entry fields. I have never used them but have seen them there. The database would be built for you too.

Thanks for the info but I am looking to end up with a DataBase that I can keep for myself and customise for my needs, for example I want to be able to use it to store screen grabs of charts.

It isn't actually for horse racing but financial trading but in theory it could be used for Betfair/Betdaq trading just as it could be for Financial Trading - the only difference would be the type of data it stores but the Tables and Relationships would be the same (I think).

If I get their I will happily provide a walk through so others can set one up if they want to track their trades be it on horses or XYZ.

[andyfuller]

Well I seem to be making some progress. I have come up with the attached image of Tables and Relationships. I have removed the TradeType Table I had in earlier on the previous Relationships Screengrab I posted and replaced it with a BuyorSell Field in the OpeningTrades and ClosingTrades tables. Though I plan to reinstate this tomorrow (I think this is the right thing to do)


I feel as though the TransactionsInACompany Table is not needed though as it is just adding a pointless extra step as each individual transaction can be recorded directly against the Company (I think) which would lead to this map:

________-Company

________________-Betfair
______________________- Transaction 1
__________________________________- Opening Trades
________________________________________________- Bought 100 @ 800
________________________________________________- Bought 100 @ 850
__________________________________- Closing Trades
________________________________________________- Sold 200 @ 900

______________________- Transaction 2
__________________________________- Opening Trades
________________________________________________- Bought 150 @ 700
________________________________________________- Bought 150 @ 710
__________________________________- Closing Trades
________________________________________________- Sold 200 @ 750



________________-Facebook
______________________- Transaction 1
__________________________________- Opening Trades
________________________________________________- Bought 500 @ 100
__________________________________- Closing Trades
________________________________________________- Sold 500 @ 105



________________-Apple
______________________- Transaction 1
__________________________________- Opening Trades
________________________________________________- Bought 100 @ 200
________________________________________________- Bought 100 @ 250
__________________________________- Closing Trades
________________________________________________- Sold 200 @ 300

______________________- Transaction 2
__________________________________- Opening Trades
________________________________________________- Bought 100 @ 235
__________________________________- Closing Trades
________________________________________________- Sold 100 @ 235

______________________- Transaction 3
__________________________________- Opening Trades
________________________________________________- Sold 150 @ 200
__________________________________- Closing Trades
________________________________________________- Bought 50 @ 210
________________________________________________- Bought 50 @ 220
________________________________________________- Bought 40 @ 230


Also I have read that I shouldn't be using "Lookup"s in the Tables and instead should be achieving the same results via a Query, but that will have to wait until tomorrow.

I feel pretty confident I have my tables right now as I have been able to run a Query that shows what trades remain Open and what trades are Closed.

I can also see the details of these trades via the Tables but hopefully tomorrow can build the query needed to get this info and then hopefully put it all together in a report

Sounds easy

Advice most definitely welcome!

[xitian]

Hi Andy,

I didn't see a screenshot of your current table design in your last post. Perhaps you forgot to attach it?

Looking at the image from the other post though, I think you're on the right lines. I'd question the need for two separate OpenTrades and CloseTrades tables though. What differentiates an open trade versus a close trade, and what is the point of the differentiation? In your trade examples it seems an open trade is always a buy, and a close trade is always a sell. This information is already captured in your TradeType so why not just put them in the same table. Otherwise there's no point for the TradeType because trades in the OpenTrades table will ALWAYS be a BUY, and vice versa with CloseTrades.

On a separate note, I agree that it's correct to have a separate TradeType table, but the added complexity of having another table isn't really worth the trouble. So personally I'd just use a text column and have values as "BUY" and "SELL". I don't see there being any practical gain in having the extra table.

So in short, if it were me, I'd just have tables for Companies, Transactions, Trades, and perhaps one for MarketInfo to store market data and personal comments (perhaps you have that already in your current version).

I'm interested to know why you like to think of your trades as a group of "transactions" though. For me, all that should matter is my current position be it long or short a number of shares, and the current market price. How I got to that position doesn't make a difference (unless I'm looking back to remember why I entered). That's why I suggested a very flat Trades table originally, without the transactions thing. But perhaps the reason is that you enter a transaction with a very specific purpose and intend to close once that purpose is gone, and you'd like to look back at each separate "transaction's" purpose to understand whether or not it worked?

When you used to trade horses with BetAngel, did you used to look at the table which showed your amounts backed and layed and think of them as groups of transactions?

[andyfuller]

Hi Andy,

I didn't see a screenshot of your current table design in your last post. Perhaps you forgot to attach it?

Yup - Doh!

Hopefully attached this time!

Will address the other points after I have had some grub. But just to say I can open a trade with a Buy or Sell and the Close trade will always be the opposite of the opening trade. So I could Buy to Open and Sell to Close or Sell to Open and Buy to Close.

This is shown in the map I posted in the previous post but I think you have missed it. See Apple -> Transaction 1 (Buy on Open, Sell on Close) v Transaction 3 (Sell on Open, Buy on Close)

[andyfuller]

I'm interested to know why you like to think of your trades as a group of "transactions" though.

Firstly, I am just using the word Transaction to distinguish a group of Opening and Closing Trades. I tried using the word Trade instead and I just started to confuse my brain when looking at the various tables etc.

The reason for seeing them as a group is that I will for example:

1. Buy Facebook as a long term transaction of say a Year for say 10,000 shares @ 10p and then close it out after 12 months at 20p. The reason for opening this trade was that I thought they had a lot of potential to grow in the year.

2. During the year I also saw several opportunities to scalp the market on Facebook doing 10 trades of 1,000 shares over the year. Unfortunately these scalps don't go so well and each time I lost 10p x 1,000 shares.

So my Long term trade worked out great but I lost all the profit on the Short Term trades. At the end of the year I think I will review the year.

If I just had a list of 11 Buys and 11 Sells and it showed I made nothing I am non the wiser.

But if I group the individual transactions I can see that the Long Term Transaction worked out and the Short Term ones all failed. This then lets me see I need to work on my Short Term trading.

I may also have attached a note to each Transaction and possibly each Opening and Closing Trade (I plan to do this but have left it out of the database for now as I think I know how to add it easily so wanted to try and just focus on the bit I have been struggling with).

By grouping each transaction I can see the reason for the overall position and I can then drill down into why I did each Opening and Closing Trade.

Does that give an example of the reason and explain a bit about the Database design?

When you used to trade horses with BetAngel, did you used to look at the table which showed your amounts backed and layed and think of them as groups of transactions?

I guess the answer is Yes. I used to group them by horse. So I knew what my position was on each individual horse.

I also used to think about my trades on each individual horse in the same kind of way I described Facebook above.

But on Betfair my self-analysis was shocking in terms of this kind of stuff as I never kept records. This is something I want to ensure I do with Financials.

Hopefully I have addressed your various questions/points. Let me know if not.

[Ethanol]

Hi Andy,

I just read through the latest posts and looked at your proposed database schema.

I'd definitely consider amalgamating the OpeningTrades and ClosingTrades tables into a new Trades table. Both tables perform the same function. 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".

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

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 hope this info helps.