Importing Data

[molsen]

Hi All

I am currently in the process of automating a stock & share reconciliation process, where there are three different sources of data. Two are emailed in to me, and which I have written macro's to import. The third source comes from a spreadsheet, which I want to be able to write a macro to go and grab the data live, without having to save a copy of the spreadsheet and separately import it. Does anyone know if this is possible?

Many thanks

 

[molsen]

Forgot to mention - using Access 2010

 

[vbaInet]

Can't all be sent to a shared drive where the files can be picked up from?

 

[GinaWhipp]

The third source comes from a spreadsheet, which I want to be able to write a macro to go and grab the data live, without having to save a copy of the spreadsheet and separately import it.

Grab it from where?

 

[molsen]

Thank you for your responses.

I think I might be on the right track. I have to pull a total of 21 data sources, either in CSV or Excel format, from emails and an external system download. At the moment someone sits down once per week and does a three way manual tick-back from each source (there are 7 distinct sets of reports). It takes around 4 hours per week for a human to do this, who then picks up on every difference he/she finds and attempts to resolve them.

I wont be able to avoid having to manually drag the CSV and external system downloads into the database, but I can write a macro to import these. The third data source is what I am more interested in at this point. It is derived from a fund manager who has listed within a spreadsheet the values of various stocks he has invested in. All I think I need to do is put a link to the spreadsheet and pull two columns of data: the first column contains a unique code to identify the stock, the second contains the number of shares in that stock. This would then be matched against each of the other two data sources, both of which contain the same unique code and the quantity of shares. If any of the three sources holds a code, for example, which is not contained within any one or both of the other sources, this would pick up and 'exception' and return it in a query. The same goes for differences in quantity.

Do you think I am on the right track?

Many thanks!

 

[GinaWhipp]

Sounds like that would be the way to go as long as you can link to the fund manager software.

 

[vbaInet]

... just make sure the FM doesn't move the workbook to another location.

 

[molsen]

Damn! The FM links his spreadsheets to a system called Bloomberg, so he can see updated prices & values, and which requires Excel Add-ins. This means I cant see this info on my PC. The solution is a manual one: save a copy of each spreadsheet and manually import that into Access. :banghead:

Thanks for your advice anyway!

 

[vbaInet]

Link to Bloomberg and use the same functions the FM has used to get the same data. Replicate what he's done on your end. Of course if he uses a different Bloomberg function without your knowing, your clone will be out of date.

 

[molsen]

Link to Bloomberg and use the same functions the FM has used to get the same data. Replicate what he's done on your end. Of course if he uses a different Bloomberg function without your knowing, your clone will be out of date.

I did try that, by replicating exactly the functions contained in his spreadsheet. However without the add-ins that will only work on a station with a licence, it doesnt return anything. It costs a fortune for a licence and I dont have one! The workaround is not ideal, but it does do the job.

Thanks for the advice.

 

[vbaInet]

Yes I know Bloomberg. Aren't you Front or Middle Office? You should be able to request a licence. Whatever the case, aren't you able to link to his spreadsheet (GetObject) and use ADO to copy from recordset? Or GetObject() and use Excel's copy and paste functions to grab the data as-is?

If you open the spreadsheet the functions should kick in.

 

[molsen]

Yes I know Bloomberg. Aren't you Front or Middle Office? You should be able to request a licence. Whatever the case, aren't you able to link to his spreadsheet (GetObject) and use ADO to copy from recordset? Or GetObject() and use Excel's copy and paste functions to grab the data as-is?

If you open the spreadsheet the functions should kick in.

Thanks for the response. Even though the file is on a shared drive, I can only open it read only, and when I do, the fields I need are shown as #NAME?.


An example of the fields in question would be =BDP("XS0735451022 Corp","PX_MID"). So, even with the workstation being switched on and the spreadsheet in question being up, I still get the same result. If there was anyway I could turn the column in question containing the ISIN Code into a value, that would crack it.

 

[vbaInet]

I suppose it's only the FM that can save a copy for you. If you can run it from a machine with a licence it should be fun but of course CIO won't give you access to another person's machine