The course is presented in three parts.

First, the course introduces the concept of automatically connecting to the external data sources with latest technologies, in order to saving time by avoiding manual data entry as much as possible.

Next, the course raises 4 areas where data entry can be automated. They are when:

  • Source is PDF files
  • Source is Online Tables
  • Source is Banking Statements
  • Source is Accounting Software

Last, we demonstrate how to connect to external data so as to obtain the real-time update of data – all without typing. They include:

  • Converting PDF files to Excel files
  • Connecting to online data
  • Converting data using Excel formulas
  • Integrating with accounting system data

Course Key Concepts: Data entry, Data Feed, Data Type, Big data, IoT (Internet of Things), Cloud computing, Machine learning, Artificial intelligence, OCR (Optical Character Recognition), On-premise software, Application Programming Interface.

Learning Objectives
  • Discover and understand 4 situations we can automate data entries.
  • Recognize 3 different ways to convert data in a PDF file to an Excel file.
  • Identify 2 Excel functions to connect to online tables and refresh instantly.
  • Explore and master 11 essential text converting formulas in Excel to transform poor-formatted data in banking statements to analyzable data.
  • Discover 2 methodologies in modern accounting systems that can integrate system data into Excel files.
Last updated/reviewed: August 7, 2023
5 Reviews (36 ratings)

Reviews

5
Anonymous Author
Very informative course. I appreciate all the examples given. I got one spill error that I'm still working on, but that may be caused by my version of Excel... still looking into that.
4
Anonymous Author
Interesting. This course gives some tips to make getting data easier, which really can save time when combined with methods from other courses.
5
Anonymous Author
Always great tips and tools from Lenny for Excel data. Will take Build Dynamic Formulas.
5
Member's Profile
Teaches interesting concept of organizing data from unclean data sources.
3
Member's Profile
Cobbled together and hard for me to follow in places.
Prerequisites
Course Complexity: Intermediate

Basic Excel knowledge.
Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Magic 3: Automating Data Entry with Data Feeds0:30
  Data Entry1:02
  Instructor Introduction 1:17
  Comparison with Other Similar Courses 1:20
  What you will get from this course? 0:42
HOW TO AUTOMATE DATA ENTRY WITH DATA FEEDS
  Data Entry Basics0:44
  Data Source: Scanned PDF File4:17
  Data Source: Printed PDF2:28
  Online Tables (Part 1)4:02
  Online Tables (Part 2)3:00
  Bank Statements (Part 1)8:13
  Bank Statements (Part 2)3:59
  Bank Statements (Part 3)2:25
  Bank Statements (Part 4)3:17
  Bank Statements (Part 5)2:02
  Bank Statements (Part 6)4:40
  Bank Statements (Part 7)1:26
  Accounting Software1:20
CONCLUSION
  Takeaways 1:46
  Next Course and Q&A 0:32
CONTINUOUS PLAY
  Excel Magic 3: Automating Data Entry with Data Feeds49:02
SUPPORTING MATERIAL
  Slides: Excel Magic 3: Automating Data Entry with Data FeedsPDF
  Excel Magic 3: Automating Data Entry with Data Feeds Glossary/ IndexPDF
  Workbook: Excel Magic 3: Automating Data Entry with Data FeedsXLSX
  Handouts: Excel Magic 3: Automating Data Entry with Data FeedsPDF
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam