Creating Databases that Retrieve Data — On-Line Course

Subsequent child pages demonstrate how to get into the technical details of creating excel databases using various methods to read data including and getdata.  Subjects that are included in the various different files are listed below.  In my advanced VBA course I have included the following subjects that are described in detail in the various webpages.


  1. Examples of Creating Databases
    1. Databases from Webpage Data (Financial Statement Data)
    2. Database Created from Downloading Excel Files (Commodity Price)
    3. Databases Created through Reading Files at Different Dates (Solar Prices)
    4. Databases Created from Working with Dates on Webpage (Exchange Rates)
    5. Databases Created from Text Files and CSV Files (Stock Price)


  1. Reading data from the Internet with VBA
    1. Magic of WORKBOOKS.OPEN
    2. Alternative Approaches to Reading Data and WORKBOOKS.OPEN
    3. Working with Flexible URLs to Efficiently Acquire Multiple Data
    4. Creating Macro to Read Element from Database
    5. Creating Macro to Read Uploaded Text and Excel Files


  1. Reading Multiple URLS and Placing Data in Separate Sheets
    1. Setting up Multiple URL’s in Alternative Formats
    2. Using INDEX Function to Read Multiple Files
    3. Simulating Multiple Scenarios with VBA Code
    4. Placing Data in Unique Sheets
    5. Creating Error Checking Routines


  1. Efficiently Working with Downloaded Data
    1. Alternatives to make database files calculate more quickly and to make the files take less space
    2. Use of MATCH/INDEX and INDIRECT to work with all kinds of database analysis and scenario management
    3. Efficient use of SUMIF, SUMIFS, AVERAGEIF with entire rows and columns of data to perform analysis
    4. Using the INDIRECT function in the match function to gather data from different time periods and different sheets
    5. Using the OFFSET function to create dynamic tables and charts from different sheets in a database
  2. Fixing Problems with MATCH function
    1. Creating Customised Match Functions for Multiple Occurrences
    2. Creating Match for Truncated Strings
    3. Finding Matches in Alternative Sheets



Link to My Youtube Channel Where You Can Look At All of the Different Videos that I have Made