I'm newb to this stuff and i wanna know if this is possible with excel and access + vba?

[imsoonewb]

Hi

Unfortunately I don't have any experience with Access and have little knowledge of what databases are capable of. Also my experience with excel is limited and i can only do the most BASIC functions.. so i was really hoping you guys could offer me some advice on whether this thing i need to do is possible

please forgive me for bad terminology

I just started work for a financial institution and need to help them with some IT issues.. basically every time the department (ill just say 'they' from now on) buy or sell any assets (fixed-income bonds mainly) they manually fill a word document and file it in a folder and put it in a cabinet (the old way) to keep a record. The system is pretty outdated..

Because they have no database to record assets in, they edit their excel sheets manually (yes, humans) every time to reflect the change in their assets holdings which is multiple times a day. The excel sheets show current performance of the assets by using functions that connect to Bloomberg for live data alongside some financial formulas that really aren't that complicated, mostly stuff like Daily gain/loss or Cap gain/loss

The issue is that there is too much human-error that comes with manually editing their assets in Excel models. So I'm wondering whether..

1. They could record their assets in Access database by filling in a data entry object like a form for every bond they buy (alongside relevant values like price, discount rate, coupon rate, maturity date that are used in Excel). And when selling a bond if they could look up the Bond (Reference ID?) in the DB and edit some parameters like perhaps Quantity Owned (new) to be = Quantity Owned - Quantity Sold. I.e to keep an updated database of the all current assets with price and quantity etc.
2. It would be possible to make the excel sheets reflect the database current assets always. So that the excel sheets can maintain its exact formatting, styling, functions and formulae etc but still change in accordance to changes in the Database. Like if a new Bond is bought and placed in the Access database could you sort of 'refresh' the excel sheet so that it adds the new asset to the list and be formatted the same as other assets in the list. Similarly if a bond in the Database changed from Quantity 20 to 10 (i.e they sold 50%) whether excel could simply change its Quantity value to reflect the change without having a human intervene.

I also wonder whether i could use VBA or .Net or another language to harness the power of both applications to aid in finding a solution. Also are there other databases / language technologies I could consider?

Sorry If i explained anything badly..

would really appreciate any responses

thanks for reading

imsoonewb.

 

[Smitty]

Welcome to the Board!

It shouldn't be too difficult to build an Assets db in Access, although if you're just starting you will have a bit of a learning curve. I think there might even be an Asset Tracking template that you can use (File-->New).

As for linking the data in Excel, you'd first build a query in Access that gives you the data that you want. On the Excel side you can use the Data-->Get External Data-->From Access Wizard to help you link up to and set refresh periods on the Access query, which will dump it right into Excel for you.

HTH,