What info does colleges have access to?

Automatically Importing Stock info from the web into MS Access

• I would like to have MS Access 2002 automatically pull up to date stock prices from the web based on a table of stock tickers in Access. I think that this can easily be done with yahoo or a similar website. Below is what appears to be the process Step One: Create web query on yahoo through Access and from an Access table of stock tickers. For example: Access TABLE 1 1 GTW 2 DELL 3 MSFT From this table, I want to create a query on yahoo that generates a spreadsheet or text file seperated by commas. STEP Two: Create a table (TABLE 2) in Access from the Step One data. I would like for the data to be active and refreshable. For example, if I add a new ticker to my MS Access TABLE 1, I would like the corresponding data to be added to TABLE 2 if I click refresh or if I re-open the database. I can do this manually now by: 1. URL http://finance.yahoo.com/q/cq?d=v1&s= 2. Enter stock tickers individually from TABLE 1 separated by %2c+ For example: http://finance.yahoo.com/q/cq?s=GTW%2c+DELL%2c+MSFT 3. download the spreadsheet of these tickers ("download to spreadsheet") and save the spreadsheet to my computer. 4. import the spreadsheet into access. When doing the above manual process, somewhat obviously, if I want to add a Ticker?s data, I must re run pretty much the whole process? Please only answer if this can be done. If on a glance this or something very similar cannot be done please dont answer. Thanks for the info!

• Answer:

  hankthetank-ga: I have completed my investigation of your requested problem, and have a working solution for you. Before I provide the link to the finished file, though, I must first provide you with some instructions on how to make the software work. The solution, implemented within Access as a Visual Basic for Applications module, requires that you have Visual Basic for Applications available to you in Access, and it requires that you slightly alter from the default configuration the external libraries that MS Access VBA references. This last change is actually 'standard' for most Access VBA modules that need to manipulate external data. To confirm that you have VBA installed, simply open up the MDB file from the link that I will provide below, then in the Objects list, go to Modules and highlight the only module present. If 'Design' in the toolbar is clickable, then you have VBA installed. If not, you will need to install it from your original Office or Access installation CD; by default, basic VBA support is installed so you should be OK. After clicking on 'Design', you will be in the VBA Editor window. Click on the Tools menu, and select 'References...'. At the top of the resulting dialog box, you will see all of the currently-selected external libraries. Look at the checked items carefully, and uncheck any that mention "ActiveX Data Objects", "ADO", and "DAO". Then, scroll down to "Microsoft DAO 3.6 Object Library" and check it to include it in the list of selected external references, and click OK. At this point, you can close the Microsoft Visual Basic window (saving any changes if prompted by the system) and return to MS Access. Now, you can open up the TickerRef form, and you will see that I have added a button called Update Info. This button calls the 'RunCSV' macro, which in turn runs the necessary function in the module. Note that I have not edited the TickerRef form beyond adding this button. Please do not alter the layout of the 'Quotes' table; I have already included more fields in the download than you are currently using. If you wish to add fields that are not already in the download, it will be necessary to tweak the Quotes table layout first, then alter the format list in the module driving the CSV download. The file can be downloaded from this location: http://aht_ga.tripod.com/hankthetank.htm Please let me know if you run into any problems with this, I can help you through them. I hope that you like the program! It was a fun challenge. Regards, aht-ga Google Answers Researcher