Stock Portfolio tracking (1 Viewer)

agrant

New member
Local time
Today, 13:23
Joined
May 15, 2013
Messages
1
Hi,

I am fairly new to access and have been asked by my boss to move our current stock portfolio ranking system from excel to access so that we can conduct analysis over time of our changing views on each stock.

The data in the tables that I create will be updated on a regular basis via an export from excel. The included data will be a list of all the stock codes, current stock price, our valuation, a qualitiative quality score and our ownership of each stock.

How do I set the table up so that each time I export new updated data into the access table for each stock the old data is not deleted. The aim being that we can look at one particular stock and see how our valuation of the stock has changed over time OR our quality score for the stock has changed over time.

Thanks for your assistance in advance.
 

catalin.petrut

Never knowing cleric
Local time
Today, 04:23
Joined
May 3, 2013
Messages
118
I don't see the need of a database if you will still work in excel. To answer your question, it is possible to import data from excel without deleting the old data. But the risk of errors is huge, becouse the user will work "in the table". Maybe you should concentrate creating the database you need and "update it" with forms and reports to work only in access.
 

MSAccessRookie

AWF VIP
Local time
Yesterday, 22:23
Joined
May 2, 2008
Messages
3,428
Hi,

I am fairly new to access and have been asked by my boss to move our current stock portfolio ranking system from excel to access so that we can conduct analysis over time of our changing views on each stock.

The data in the tables that I create will be updated on a regular basis via an export from excel. The included data will be a list of all the stock codes, current stock price, our valuation, a qualitiative quality score and our ownership of each stock.

How do I set the table up so that each time I export new updated data into the access table for each stock the old data is not deleted. The aim being that we can look at one particular stock and see how our valuation of the stock has changed over time OR our quality score for the stock has changed over time.

Thanks for your assistance in advance.

I would think that the process that you need could be as simple as adding a Transaction Date to each record, and importing the data using an Append Query. This would create a Historical Table that could allow you to see multiple days worth of records for the same items.

Some things that you might want to consider:
  • Access can perform the calculations on the imported data, and calculated data does not need to be imported unless that is a requirement
  • Access does not have an endless supply of space, so the archiving of your Tables will eventually become an issue, and should be considered up front.
Is this the kind of configuration you were talking about?

-- Rookie
 

access2010

Registered User.
Local time
Yesterday, 19:23
Joined
Dec 26, 2009
Messages
1,019
A charity I volunteer at would like a database such as you are working on to keep historical data on their stock investments.

Would you be interested in allowing us to use your database?

Thank you

10finch46 at wehavemail.ca
 

Mihail

Registered User.
Local time
Today, 04:23
Joined
Jan 22, 2011
Messages
2,373
Maybe you should concentrate creating the database you need and "update it" with forms and reports to work only in access.
Fully agree with this.
Even if you are, now, more comfortable with Excel.

Another answer to your initial question can be:
Create a temporary table in Access where to import data from Excel.
This time you must ensure that the old data are removed from this table.
Use an append query to append data from this (temporary) table to the main table.
 

Steve R.

Retired
Local time
Yesterday, 22:23
Joined
Jul 5, 2006
Messages
4,617
This seems to be a backwards approach to me. Since a database is meant to store historical data, the data should be directly entered into MS Access. Technically, MS Access could replace the use of MS Excel. But if you already have good analytics in MS Excel then MS Excel could be linked to MS Access to retrieve the data you are after.
 

access2010

Registered User.
Local time
Yesterday, 19:23
Joined
Dec 26, 2009
Messages
1,019
Thank you for your suggestion.

The charity receives donations of Stocks, Bonds and other investment instruments and wants to keep a history of their value on receipt.

They would also like to keep track of their investment portfolio's value.

Regards
 

Steve R.

Retired
Local time
Yesterday, 22:23
Joined
Jul 5, 2006
Messages
4,617
The charity receives donations of Stocks, Bonds and other investment instruments and wants to keep a history of their value on receipt.
Exactly the purpose of a database. A spreadsheet is not appropriate for this purpose.

They would also like to keep track of their investment portfolio's value.
A separate table would be used to hold the historic prices of a stock and would be linked to the donation table by the stock symbol.
 
Last edited:

access2010

Registered User.
Local time
Yesterday, 19:23
Joined
Dec 26, 2009
Messages
1,019
Thank you for your suggestion and I will try and help this organization build their database.

Paul
 

Cronk

Registered User.
Local time
Today, 13:23
Joined
Jul 4, 2013
Messages
2,770
I've supported myself developing database systems for clients for many years but I would always suggest looking at off the shelf solutions in situations where a customised development is not required.

There are a number of commercial stock portfolio management systems available for reasonable prices - I use one myself to manage my investments.

This might be a more effective solution for your purpose when other aspects have to be taken into account, and depending on the tax system regime you are subject to, such as dividends, dividend re-investment, share splitting, capital return.
 

Steve R.

Retired
Local time
Yesterday, 22:23
Joined
Jul 5, 2006
Messages
4,617
How do I set the table up so that each time I export new updated data into the access table for each stock the old data is not deleted. The aim being that we can look at one particular stock and see how our valuation of the stock has changed over time OR our quality score for the stock has changed over time.
Getting back to the original post. What you are proposing is backwards. The purpose of a database is to store your data. You would then use the spreadsheet to retrieve your data and to perform the required analytical analysis.

PS: You may also discover that MS Access has sufficient analytical tools to make the use of MS Excel "irrelevant".
 

Harrymon12

New member
Local time
Yesterday, 19:23
Joined
Jul 3, 2016
Messages
6
Using MarketXLS works for me. Its great. I hope it helps you too.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:23
Joined
Feb 28, 2001
Messages
27,001
Given that many times you get spreadsheets downloaded from sites and need to work with those as your starting point, I might consider the following approach:

First, get your spreadsheets. Manually analyze it (the first time or two) to determine/verify the format and content.

Next, design a pre-formatted table that you will use as a temporary holding place for the data. No keys needed (since this isn't a permanent table).

Erase the temporary table. Could be as simple as

DoCmd.RunSQL "DELETE * FROM MyTempTable;

Import one spreadsheet to this table. Use DELETE queries with WHERE clauses to remove header lines and blank lines. You'll probably have to experiment with this for a while, but you'll get the hang of it.

When you are done, you have a temporary table with everything you wanted to keep. Now build an INSERT INTO query to append the data into your main table. The difference between the temporary and permanent tables is that the permanent table has one more field that is not part of the temp table - a date (or perhaps date/time) field. So you COULD take this approach:

Code:
INSERT INTO MyPermTable (WhenTag, field-name-1, field-name-2, ..., last-field-name)
   SELECT Now(), field-name-1, field-name-2, ..., last-field-name
      FROM MyTempTable ;

Of course, you use the correct field names, and I am assuming that the temporary and permanent tables would use the same field names for corresponding entries. Given the large but finite capacity of an Access database file, you also need to heed the advice of AccessRookie regarding archiving or otherwise retiring data.
 

Users who are viewing this thread

Top Bottom