Best software to build a database

[andyfuller]

Looking for some advice.

I am looking at building a database that would allow me to record notes for different selections and give them a time stamp which would then allow me to in the future pull up the notes for a specific selection, perhaps all the notes or sometimes just the notes made between 2 dates.

Also I would like to be able to record the price at which I open and close a trade at. I am happy to do this manually, it doesn't need to be automated.

Finally I would like to be able to pull up a selection and it to show all the info I have stored on that selection.

I was thinking the best thing would be to use MS Access. It has been a long time since I last used it or done anything like this, probably 10 years so wondered if people know of a better option.

I will have to relearn Access so learning something new isn't an issue as long as it isn't too difficult.

TIA.

[Euler]

I think some of it depends on what you want to do. Modern versions of Excel can have 1m rows so that is often enough for some serious data crunching. I tend to use Excel for most of my requirements.

[andyfuller]

Cheers for the reply Peter.

It is more for recording my thoughts in written form rather than for crunching data. So there would be paragraphs of written info and just a way of recording my entry and exit prices.

So say in September I want to pull up my record on selection a to see what my written thoughts had been on it back then. Kind of like a digital diary.

Does that help make it clearer? I did think about excel but thought because it is going to be mainly written info something like access would be better so I could get a print out of the report I ran which could then list by date my thoughts.

Thanks.

[PeterLe]

Hi Andy
I recently had a months trial of filemaker pro (for the iMac though) as it was highly recommended. I wanted to download the data from betfair.data for certain events and then concatenate them into large files. If you have ever played around with these files you would know they can become huge and difficult to manage.
Although I gave it a fair test, in the end I decided to stick with excel as I love using the pivot tables and find it displayed the info exactly as I wanted.
I realise that you are looking to do something slightly different and maybe MS Access would fit the bill? (Sorry not used it before), maybe you can run a trial on it??
Regards
Peter

[gutuami]

If you need a simple and powerful database use access. Running a fairly complex query in Access is very easy. Excel is a spreadsheet software designed to run primarily calculations it is not a database management system. You can very easy import/export files, queries, reports between access and excel. So for specific tasks that gives you even more power.

[xitian]

MS Access is also all I can suggest. Because your needs will be quite specific, there's unlikely to be any specialist software for what you want to do, and therefore you need to go with something generic like Access and tailor it yourself.

The biggest time sink will be designing suitable tables, forms for inputting data, and reports for viewing the information. Because of that I'd recommend starting out using Excel as a quick way to do the "designing phase". You can move things round and reshape things really easily in Excel, even though as other people mentioned, it's not really designed for holding things like long text. So I'd recommend using Excel seriously for a little while. But once your layout in Excel isn't changing anymore and you haven't thought of any new features/views, you can create a new project to store everything properly in Access.

I think it's a great idea though, Andy. And since there isn't already any specialist software, it'd actually be a great feature to add to BetAngel! Pretty simple to implement I'd have thought, so it's mainly about getting the functionality right.

[jimrobo]

Not used it but you could try something like bento. I think you can use it on your iPad too natively??

[andyfuller]

Many thanks for all the input guys, much appreciated.

I think the suggestion of using excel to begin with while I nail down exactly what I want is a good idea and then switch it over to Access once I have it nailed down.

I spoke with a friend who does some data base stuff and they seemed to think access was probably the best as it is really just for written information.

Many thanks again but any other comment is welcome.

[andyfuller]

Not used it but you could try something like bento. I think you can use it on your iPad too natively??

Cheers for the suggestion, I haven't heard of that but will look into it. I was thinking of sticking to something mainstream as it is easier to get help with it. For example there are plenty of teach yourself access videos on YouTube.

[jimrobo]

sure, just thought I'd throw it into the mix!!

[oscar123]

If it was specific information on horses in certain races, then you can keep notes on the racing post website.

You cant crunch any data, but you can recall all your notes on a horse from a specific race.Im not sure if that was what you were after?

[superfrank]

I use Microsoft SQL Server 2008 R2 when i need something other than Excel.

It's free for a single user personal install.

[dogform]

For ease of use and your intended use I would recommend access rather than MySQL or SQL Server. For serious database work on large numbers of records then my recommendation would reverse.

Access has a *.mdb file size limit of 2gb whereas no such limits exist in MySQL or SQL Server. For your use it is likely that you will never get near to the 2gb limit.

Access is a handy solution for home and small office use, once it gets serious SQL Server or the ultra heavyweight Oracle come onto the scene.

[andyfuller]

I don't suppose there are any Access users around that I could ask a question.

I finally got around to building a database and I have myself going round in circles as to how I should set up a set of relationships.

I want to link a Position to a Company:

1. Each Position can only be linked to a single company but each company can have many Positions associated with it - so a 1 to many relationship. (This I think is correct)

2. Each position can be made up of a number of opening trades and closing trades

For example I may Buy 100 and then sell 20, then sell another 20 and then sell the final 60. Or I may Buy 60 then another 20 and then another 20 and then sell 100.

So I am not really sure how to set this up.

A position can have many Opening trades and many Closing trades but an Opening trade can only be associated with a single Position, likewise a closing trade can only be linked to a single Position.

So I have (I think) a Many to Many relationship between Opening trades and Closing trades and One to many relationship between (Positions and Opening Trades) and (Positions and Closing Trades). The brackets are just to make things clearer.

So I assume I need a junction table?

Any help is very much appreciated!

[xitian]

Hi Andy,

If I were doing it, I would probably keep it as simple as possible at first and extend it later if I found it didn't do what I wanted. 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?

My first thoughts:
1. A table for company static data. e.g. Ticker, Name, Sector, etc.. with the Ticker being the primary key.
2. A table for trades with columns something like: TradeId, Date, Time, Ticker (foreign key reference to company table), Side (buy or sell), Price, Shares, Comment, etc..
3. Optionally have a table for company non-static data. e.g. Date, Time, Ticker (foreign key reference to company table), Bid, Ask, Volume, MarketCap, PE, DivYield, News, Comment, etc..

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.

My knowledge of Access specifically is a bit limited though, so I can't offer any advice on how to actually do things!