Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Building MS Access Portfolio Management Database

Discussions regarding financial software
shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Building MS Access Portfolio Management Database

#272922

Postby shetland » December 22nd, 2019, 7:51 pm

I have given up trying to find some portfolio management software or online package that does what I want so I have decided to try and write my own using Microsoft Access. I wondered if anyone had sone anything similar

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3591 times
Been thanked: 1509 times

Re: Building MS Access Portfolio Management Database

#272925

Postby ReformedCharacter » December 22nd, 2019, 8:06 pm

shetland wrote:I have given up trying to find some portfolio management software or online package that does what I want so I have decided to try and write my own using Microsoft Access. I wondered if anyone had sone anything similar


Yes, many years ago. Not worth the effort IMO. What are you hoping to achieve though?

RC

AsleepInYorkshire
Lemon Half
Posts: 7383
Joined: February 7th, 2017, 9:36 pm
Has thanked: 10514 times
Been thanked: 4659 times

Re: Building MS Access Portfolio Management Database

#272929

Postby AsleepInYorkshire » December 22nd, 2019, 8:16 pm

shetland wrote:I have given up trying to find some portfolio management software or online package that does what I want so I have decided to try and write my own using Microsoft Access. I wondered if anyone had sone anything similar

I've no knowledge of Access. I can, however, use Excel to do most of what a database can. I'm not entirely sure anything too fancy would help me with stocks and shares though. What are you trying to achieve please?

AiYn'U

shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Re: Building MS Access Portfolio Management Database

#272931

Postby shetland » December 22nd, 2019, 8:18 pm

I simply want something which will
Record prices
Manage Purchases and sales
Record dividends
Manage dividend reinvestment
Produce valuation reports

AsleepInYorkshire
Lemon Half
Posts: 7383
Joined: February 7th, 2017, 9:36 pm
Has thanked: 10514 times
Been thanked: 4659 times

Re: Building MS Access Portfolio Management Database

#272933

Postby AsleepInYorkshire » December 22nd, 2019, 8:22 pm

shetland wrote:I simply want something which will
Record prices
Manage Purchases and sales
Record dividends
Manage dividend reinvestment
Produce valuation reports

Yes my little knowledge of Access tells me you can do this with this software, but I'd prefer to use a spreadsheet. I'm not "under-thinking" this am I?

AiYn'U

shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Re: Building MS Access Portfolio Management Database

#272941

Postby shetland » December 22nd, 2019, 8:55 pm

I am not sure how all this can be done with a spreadsheet.

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3591 times
Been thanked: 1509 times

Re: Building MS Access Portfolio Management Database

#272951

Postby ReformedCharacter » December 22nd, 2019, 9:50 pm

shetland wrote:I am not sure how all this can be done with a spreadsheet.

You might like to have a look here:

http://lemonfoolfinancialsoftware.weebly.com/

RC

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Building MS Access Portfolio Management Database

#272952

Postby staffordian » December 22nd, 2019, 9:52 pm

shetland wrote:I simply want something which will
Record prices
Manage Purchases and sales
Record dividends
Manage dividend reinvestment
Produce valuation reports

Have you looked at Moneydance?

http://moneydance.com/

In order to produce valuation reports, you would have to install a (free) third party extension which will import prices. The only limitation I'm aware of is that it only imports current prices, not historic, so you'd have to open the program for each day you want prices, or you'd have to manually import a price history for each share via a yahoo csv file of historic prices.

It has a selection of valuation reports, including a net worth, which can optionally include individual stock positions or just a total for each account, and it can produce performance reports.

No connection, just a reasonably satisfied user over the last decade or so since Quicken ceased to be an option.

uspaul666
2 Lemon pips
Posts: 232
Joined: November 4th, 2016, 6:35 am
Has thanked: 195 times
Been thanked: 111 times

Re: Building MS Access Portfolio Management Database

#272962

Postby uspaul666 » December 22nd, 2019, 10:36 pm

I’ve written a portfolio tool that processes downloaded youinvest and HL transaction and cash lists and produces a portfolio with costs and XIRR figures. I plan to open source it at some point. I want to improve it to automatically produce unitizations and graphs etc but the awfulness of obtaining historical prices for stocks that have changed ticker, or been cancelled has put me off for a while. It’s all written in Java. I’d imagine writing something similar in msaccess would be very tricky. There’s quite a bit of pattern matching and parsing for corp actions etc. I also wouldn’t trust Microsoft to look after you since I think msaccess upgrades have proved to be difficult to move data and vb over. I’d suggest trying to use python which is what I’d use if I didn’t already know java. Good luck with whatever you decide.

TUK020
Lemon Quarter
Posts: 2039
Joined: November 5th, 2016, 7:41 am
Has thanked: 762 times
Been thanked: 1175 times

Re: Building MS Access Portfolio Management Database

#273007

Postby TUK020 » December 23rd, 2019, 9:10 am

uspaul666 wrote:I’ve written a portfolio tool that processes downloaded youinvest and HL transaction and cash lists and produces a portfolio with costs and XIRR figures. I plan to open source it at some point. I want to improve it to automatically produce unitizations and graphs etc but the awfulness of obtaining historical prices for stocks that have changed ticker, or been cancelled has put me off for a while. It’s all written in Java. I’d imagine writing something similar in msaccess would be very tricky. There’s quite a bit of pattern matching and parsing for corp actions etc. I also wouldn’t trust Microsoft to look after you since I think msaccess upgrades have proved to be difficult to move data and vb over. I’d suggest trying to use python which is what I’d use if I didn’t already know java. Good luck with whatever you decide.


uspaul,
would it be possible for you to have a chat with Kiloran and IAAG about this?
The HYPTUSS portfolio analysis tool is really useful. The things that would make it even more so are some form of transaction logging, and combining multiple source accounts (e.g a SIPP and an ISA).
Recorded cost of transactions would then make it possible to do income unit unitisation.
Recording dividend payments would then allow XIRR calculations.
Even if previous history w2as not accessible, it would then enable performance analysis from a starting date, when you capture market prices as base cost.
tuk

scrumpyjack
Lemon Quarter
Posts: 4813
Joined: November 4th, 2016, 10:15 am
Has thanked: 606 times
Been thanked: 2675 times

Re: Building MS Access Portfolio Management Database

#273011

Postby scrumpyjack » December 23rd, 2019, 9:22 am

shetland wrote:I have given up trying to find some portfolio management software or online package that does what I want so I have decided to try and write my own using Microsoft Access. I wondered if anyone had sone anything similar


Yes I set up my own system many years ago and have added facilities every time I needed to so that now it does pretty much all I require.

I have looked at various commercial systems but none of them focused on recording and reporting investment portfolios which is what I wanted.

Mine records all investment transactions, produces all tax reports, including CGT, full dividend reporting by tax year, reporting of ex divs, cash statements for each account, handles multiple portfolios (20) and multiple brokers for all my family, grabs share prices from an Excel spreadsheet that loads them automatically, reports sector analysis of the portfolios, consolidated reports as required etc etc.

I never liked MS Access and used MS Visual Foxpro which I had been very familiar with since the '90s.

shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Re: Building MS Access Portfolio Management Database

#275070

Postby shetland » January 4th, 2020, 10:06 am

scrumpyjack wrote:
shetland wrote:I have given up trying to find some portfolio management software or online package that does what I want so I have decided to try and write my own using Microsoft Access. I wondered if anyone had sone anything similar


Yes I set up my own system many years ago and have added facilities every time I needed to so that now it does pretty much all I require.

I have looked at various commercial systems but none of them focused on recording and reporting investment portfolios which is what I wanted.

Mine records all investment transactions, produces all tax reports, including CGT, full dividend reporting by tax year, reporting of ex divs, cash statements for each account, handles multiple portfolios (20) and multiple brokers for all my family, grabs share prices from an Excel spreadsheet that loads them automatically, reports sector analysis of the portfolios, consolidated reports as required etc etc.

I never liked MS Access and used MS Visual Foxpro which I had been very familiar with since the '90s.



Is Foxpro a relational database program ? If so the principles would be similar to Access.

I am simply looking for a few tips in how to structure the system

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3591 times
Been thanked: 1509 times

Re: Building MS Access Portfolio Management Database

#275092

Postby ReformedCharacter » January 4th, 2020, 11:41 am

shetland wrote:
Is Foxpro a relational database program ? If so the principles would be similar to Access.

I am simply looking for a few tips in how to structure the system


Nothing runs like the Fox, as the advertising used to say.

It is (or was) a relational database. Not sure if you can get it any more. The History of FoxPro:

http://www.foxprohistory.org/foxprotimeline.htm

has mostly dead links.

RC

shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Re: Building MS Access Portfolio Management Database

#275100

Postby shetland » January 4th, 2020, 12:07 pm

ReformedCharacter wrote:
shetland wrote:
Is Foxpro a relational database program ? If so the principles would be similar to Access.

I am simply looking for a few tips in how to structure the system


Nothing runs like the Fox, as the advertising used to say.

It is (or was) a relational database. Not sure if you can get it any more. The History of FoxPro:

http://www.foxprohistory.org/foxprotimeline.htm

has mostly dead links.

RC


I am stuck with Access as I already have it.

I would be interested in understanding a little of the structure of the datases you set up.

please PM if you can

Thanks

ReformedCharacter
Lemon Quarter
Posts: 3120
Joined: November 4th, 2016, 11:12 am
Has thanked: 3591 times
Been thanked: 1509 times

Re: Building MS Access Portfolio Management Database

#275112

Postby ReformedCharacter » January 4th, 2020, 12:34 pm

shetland wrote:
ReformedCharacter wrote:
shetland wrote:
Is Foxpro a relational database program ? If so the principles would be similar to Access.

I am simply looking for a few tips in how to structure the system


Nothing runs like the Fox, as the advertising used to say.

It is (or was) a relational database. Not sure if you can get it any more. The History of FoxPro:

http://www.foxprohistory.org/foxprotimeline.htm

has mostly dead links.

RC


I am stuck with Access as I already have it.

I would be interested in understanding a little of the structure of the datases you set up.

please PM if you can

Thanks

Much as I would like to help, I cannot. My own efforts with MS Access were c. 25 years ago and at one point used a hardware card to download share prices from CEEFAX. Personally I think your time would be better spent with a spreadsheet and the HYPTUSS would be a good place to start. You could use MS Access but I think you will find that there's quite a lot to learn before you can make good use of it. I developed tools with MS Access for about 5 years and frankly I'd be happy to never see it again. Another option worth considering if you want to use a DB might be Visual Studio \ VB.Net with sqlite (all available at no cost). But again, quite a lot to learn before you become useful with it.

https://www.sqlite.org/index.html

RC

shetland
Lemon Pip
Posts: 55
Joined: May 16th, 2017, 7:26 am
Has thanked: 11 times
Been thanked: 19 times

Re: Building MS Access Portfolio Management Database

#275126

Postby shetland » January 4th, 2020, 1:07 pm

I wasnt asking about Access, I was really tried to understand how yours was structured asthevprinciples would be the same. What tables do you have, do you have separate tables for dividend repurchases etc

Lanark
Lemon Quarter
Posts: 1321
Joined: March 27th, 2017, 11:41 am
Has thanked: 595 times
Been thanked: 582 times

Re: Building MS Access Portfolio Management Database

#275131

Postby Lanark » January 4th, 2020, 1:24 pm

Access has a very good front end (forms and reports) and a very poor back end (database tables)

The best way to use it is as a front-end for a real database like SQL Server, but that gets expensive.
I would not use an Access back end (tables) for more than one user at a time, just like shared Excel spreadsheets, if you have multiple people concurrently accessing the same data, it WILL get corrupt.

If you want to learn more, this is a good book - its old but it covers all of the basic concepts really well
https://www.amazon.co.uk/Access-2000-De ... 782123708/

One note of caution is that Microsoft are not really developing Access any more, I don't think it fits in with their 'everything in the cloud' strategy. It still works today but I wonder if they will eventually kill it off the way they did foxpro.

scrumpyjack
Lemon Quarter
Posts: 4813
Joined: November 4th, 2016, 10:15 am
Has thanked: 606 times
Been thanked: 2675 times

Re: Building MS Access Portfolio Management Database

#275152

Postby scrumpyjack » January 4th, 2020, 2:41 pm

shetland wrote:I wasnt asking about Access, I was really tried to understand how yours was structured asthevprinciples would be the same. What tables do you have, do you have separate tables for dividend repurchases etc


I'll PM you with an outline of the tables when I get a chance.

Foxpro has its roots in dbase, a relational database language predating Windows. It was gradually developed further and further, firstly for Windows then for OOP (Visual Foxpro). It was always extremely fast. It was bought out by Microsoft, possibly to remove the competition and eventually deliberately let die because it did not fit in with their mainstream developments.

So yes it is a very powerful OOP relational database development environment.

gbjbaanb
Lemon Slice
Posts: 582
Joined: November 4th, 2016, 1:17 pm
Has thanked: 192 times
Been thanked: 126 times

Re: Building MS Access Portfolio Management Database

#275482

Postby gbjbaanb » January 6th, 2020, 11:44 am

Lanark wrote:The best way to use it is as a front-end for a real database like SQL Server, but that gets expensive.


Not so, SQLServer Express is almost fully featured as the expensive versiosn, but is free. There's also the LocalDB option which is basically the same DB but in a "bundled with your app" format.

scotia
Lemon Quarter
Posts: 3561
Joined: November 4th, 2016, 8:43 pm
Has thanked: 2371 times
Been thanked: 1943 times

Re: Building MS Access Portfolio Management Database

#280724

Postby scotia » January 28th, 2020, 10:30 pm

For many years I have used a self-generated Access database to keep a note of all of my investment transactions. This has expanded over the years to download on-line current fund and IT prices from the FT to provide current valuations of these investments. I also store valuations periodically - either entered on an add-hoc basis from my computed valuations, or from the valuations I receive periodically (now quarterly) from Hargreaves Lansdown. I can calculate the XIRR for each of the investments, and for the overall portfolios. I maintain a table of monthly RPI, so can also compute XIRR corrected for RPI. You are correct in thinking that you need a database for this purpose - i.e. storing and processing volumes of historical data. And when you want summarised reports of various forms, then you can simply output the processed data to an Excel spreadsheet or a Word Document - Access, Excel and Word all happily work together with Visual Basic (VBA) as the common working language. With all of my investments now in tax shelters, I have no need to annually compute Dividends and Capital Gains for tax purposes - but that would also be a simple task.
So how would you start? Think about what data you want to store, and interactively construct appropriate tables - and hand-populate them with a small amount of data. Alternatively, If you have existing data in another form (E.G. Excel or CSV or XML etc), it is reasonably simple to import it, without the need for VBA code, into Access Tables. Then think about a small task you would like to perform on the data - and construct an Access form with a button on it. Behind the button create a small VBA subroutine to carry out the task and report back the result. That's how I started - and then you get the bug!
On the Lemon Fool you will find VBA code, usually associated with Excel, to carry out useful tasks - e.g. for online downloading current fund and share prices from the FT - and you will find it easy to adapt these for your Access Database.
So when I have other tasks that I'm not looking forward to doing, I can always think up another bell or whistle I can add to my Access Database, and get busy with that - in the hope that the less attractive tasks go away (not that they usually do).


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 10 guests