|
|
2/27/2006 | ||||||||||
| Importing data and building a macro. | 2/27/2007 | ||||||||||
| MDDA800 | |||||||||||
| Task @ hand. | DATE | CXBARCHEEZ | |||||||||
| 1. Import National Cheese Exchange barrel price | Date | Chedder Cheese Price Barrels | |||||||||
| 3. Import data for the last 12 months | 2/27/2006 | 1.105 | |||||||||
| 4. Make this spreadsheet macro driven to update | 2/28/2006 | 1.105 | |||||||||
| 3/1/2006 | 1.105 | ||||||||||
| 3/2/2006 | 1.105 | ||||||||||
| 3/3/2006 | 1.105 | ||||||||||
| Steps to accomplish this. | 3/6/2006 | 1.105 | |||||||||
| I. Do the imports by hand | 3/7/2006 | 1.105 | |||||||||
| a) Open the PDQData table for National Cheese exchange | 3/8/2006 | 1.105 | |||||||||
| 1) Click PDQData.com | 3/9/2006 | 1.13 | |||||||||
| 2) Click Select Table | 3/10/2006 | 1.13 | |||||||||
| 3) Click USDA, AMS, Dairy | 3/13/2006 | 1.13 | |||||||||
| 4) Click National Cheese Exchange (in box on right) | 3/14/2006 | 1.13 | |||||||||
| b) Select the date from the table | 3/15/2006 | 1.13 | |||||||||
| 1) Click PDQData.com | 3/16/2006 | 1.13 | |||||||||
| 2) Click Variables | 3/17/2006 | 1.135 | |||||||||
| 3) Click Date, and Cheeder cheese barrel (in left box) | 3/20/2006 | 1.1275 | |||||||||
| 4) Click Ok | 3/21/2006 | 1.1275 | |||||||||
| c) Compute the date range | 3/22/2006 | 1.1275 | |||||||||
| 1) First Date is today - 365 | 3/23/2006 | 1.13 | |||||||||
| 2) in cell G1 enter "=today()-365" (no quotes) | 3/24/2006 | 1.13 | |||||||||
| 3) in cell G2 enter "=today()" (no quotes) | 3/27/2006 | 1.13 | |||||||||
| 4) Click PDQData.com, Set date range | 3/28/2006 | 1.13 | |||||||||
| 5) Hightlight cells G1 - G2, click OK | 3/29/2006 | 1.13 | |||||||||
| d) Save our selections for the macro later | 3/30/2006 | 1.1325 | |||||||||
| 1) Click PDQData.com | 3/31/2006 | 1.135 | |||||||||
| 2) Click Import table name | 4/3/2006 | 1.1375 | |||||||||
| 3) Click cell G3 then click OK | 4/4/2006 | 1.1375 | |||||||||
| 4) Click PDQData.com, Import Variables | 4/5/2006 | 1.1375 | |||||||||
| 5) Click cell G4, then click OK | 4/6/2006 | 1.1275 | |||||||||
| 6) Click PDQData.com, Import Descriptions | 4/7/2006 | 1.1275 | |||||||||
| 7) Click cell G5, then click OK | 4/10/2006 | 1.1275 | |||||||||
| e) Import the data | 4/11/2006 | 1.1275 | |||||||||
| 1) Click PDQData.com, Import Data | 4/12/2006 | 1.1225 | |||||||||
| 2) Click cell G6, then click OK | 4/13/2006 | 1.1225 | |||||||||
| 2) Build a macro and add a button | 4/17/2006 | 1.1225 | |||||||||
| a) Click Tools Macro, Visual Basic Editor | 4/18/2006 | 1.1225 | |||||||||
| b) Click Insert, Module | 4/19/2006 | 1.1225 | |||||||||
| c) Start a routine by typing SUB UPDATE | 4/20/2006 | 1.13 | |||||||||
| (vb adds some "()" and an end sub | 4/21/2006 | 1.1325 | |||||||||
| d) Click Tools, References, | 4/24/2006 | 1.1325 | |||||||||
| e) Check PDQData2 | 4/25/2006 | 1.1325 | |||||||||
| The table names are in row 3, and the variable in row 4 | 4/26/2006 | 1.1325 | |||||||||
| The same steps are used in the macro: | 4/27/2006 | 1.1325 | |||||||||
| Select a table | 4/28/2006 | 1.1325 | |||||||||
| Select Variables | 5/1/2006 | 1.1325 | |||||||||
| Set Dates | 5/2/2006 | 1.1325 | |||||||||
| Import Data | 5/3/2006 | 1.1275 | |||||||||
| (the complete macro is viewable in the VB Editor) | 5/4/2006 | 1.1275 | |||||||||
| f) Make a button | 5/5/2006 | 1.125 | |||||||||
| 1) Right click on the tool bar | 5/8/2006 | 1.125 | |||||||||
| 2) Check the forms box | 5/9/2006 | 1.135 | |||||||||
| 3) Click on the button icon, then draw a rectangle in the spreadsheet | 5/10/2006 | 1.14 | |||||||||
| 4) When prompted for the macro, click "UPDATE" | 5/11/2006 | 1.1675 | |||||||||
| 5) If desired change the button text | 5/12/2006 | 1.18 | |||||||||
| 6) Click in the spreadsheet | 5/15/2006 | 1.18 | |||||||||
| 5/16/2006 | 1.18 | ||||||||||
| THAT's it…. Click the button | 5/17/2006 | 1.19 | |||||||||
| 5/18/2006 | 1.19 | ||||||||||