INFORMATION COMPUTER SYSTEMS
Lecturer: A.Akinshin
Teachers: S. Belousova, I. Bessonova PhD, V. Lapshin PhD, E. Kudryavtsev
Course description
Nowadays the ability to use a computer effectively is crucial for everyone. It opens the whole world of digital technologies. It is also a basic requirement for hiring. The course of Information Computer Systems is a part of set of the mandatory courses (B2. Maths&Sciences) for the bachelor degree and is delivered during the first semester of the first year students of ICEF. The course is not a part of the External Curriculum of the London School of Economics. The knowledge gained through this course will improve student’s professional status as well as enhance the efficiency of other following courses.
The course is comprised of 72 class hours and 72 self-preparation hours. There is one intermediate test and final exam. The total intermediate test time is 4 academic hours and consists of practical tasks performed on a computer. The total exam time is 4 academic hours and consists of practical tasks performed on a computer
The course is comprised of two parts.
The first part deals with specific features of spreadsheet software MS Excel for economic and financial tasks as well as with data and output analysis. This part is teaching at advanced level.
The second part covers programming in Visual Basic for Applications (VBA), which facilitates processing spreadsheet data and increases the productivity in MS Excel. This part is teaching at a basic level.
Teaching objectives
To give students a holistic view of modern information technologies and their role in professional activities.
To reveal the possibilities of computer's hardware and software and to form the stable skills of working in computer technologies area for solving economic and financial tasks
To develop basic programming skills in using the object-oriented language, aiming on expanding the functionality of MS Excel in processing the large data volumes, in developing the user-defined functions and subroutines, in event handling and in creating a graphical user interface.
Teaching methods
Methods and forms of activity used in the course:
Computer class practice – for 4h once a week.
Teachers’ office hours - for 2h once a week.
Self-study - home assignments.
Teaching based on using practical examples and problems. Used software is Microsoft Excel 2010 Professional.
Assessment
Intra-term assessment
Class performance: home assignments (5 tasks for each of the two parts).
Mid-term assessment
Test is at the end of October and consists of practical tasks related to the topics 1 – 4 using MS Excel – 4 academic hours.
Final assessment
Exam is at the end of semester and consists of practical task related to the topics 5 – 10 using VBA (MS Excel 2010) – 4 academic hours.
Grade determination
-
Assessment form
|
Contribution to the final grade (%)
|
Home assignments and class work.
|
15
|
Mid-term test.
|
40
|
End-of-term exam.
|
45
|
Main reading
The course is based on study guides and textbooks in Russian by the course authors. These study guides and textbooks cover all the subjects taught.
For the first part: "Специальные возможности MS Excel для работы с большими массивами информации", "Экономический анализ средствами электронных таблиц", "Применение функций MS Excel для решения математических и экономических задач".
For the second part: "Создание пользовательских приложений средствами VBA" и "Основные принципы и концепции программирования на языке VBA в Excel".
Students are advised to use study guides (also in Russian), which contain sample homework, test and exam problems with detailed solutions and explanations.
-
Акиншин А.А., Белоусова С.Н., Бессонова И.А. Руководство для студентов по курсу «Информационные компьютерные системы». Москва: МИЭФ, 2012. 78 с.
Акиншин А.А., Белоусова С.Н., Бессонова И.А. Руководство для студентов по курсу «Информационные компьютерные системы». Москва: МИЭФ, 2011. 63 с.
Белоусова С.Н., Бессонова И.А. Основные принципы и концепции программирования на языке VBA в Excel. Москва: Интернет-университет Информационных технологий, БИНОМ. Лаборатория знаний, 2010. 200 с.
Акиншин А.А., Белоусова С.Н., Бессонова И.А. Специальные возможности MS Excel для работы с большими массивами информации (2-е издание исправленное и дополненное). Москва: МИЭФ, 2010. 162 с.
Акиншин А.А., Белоусова С.Н., Бессонова И.А. Экономический анализ средствами электронных таблиц. Москва: МИЭФ, 2010. 55 с.
Акиншин А.А., Белоусова С.Н., Бессонова И.А., Применение функций MS Excel для решения математических и экономических задач. Изд. дом ГУ - ВШЭ, 2009
Акиншин А.А., Белоусова С.Н., Бессонова И.А. Создание пользовательских приложений средствами VBA. Изд. дом ГУ - ВШЭ, 2007. 186 с.
Additional reading
Walkenbach J. Excel 2010 Bible. - Wiley, 2010. - 1058 p.
Walkenbach J. Excel 2010 Power Programming with VBA. - Wiley, 2010. - 1052 p.
Internet Resources
Get up to speed with Excel 2010 - http://www.excel-2010.com
Excel 2010 Foundation, Excel 2010 Intermediate, Excel 2010 Advanced, Excel 2010 Basics, Excel 2010 Expert - http://www.cheltenhamcourseware.com/
http://www.msoffice-tutorial-training.com/excel-2010-ribbon.html
http://www.intuit.ru/department/office/pvbaexcel
http://vba-help.ru/
Software
The course is based on Microsoft Excel 2010.
Free available databases from Internet web-cites used in examples and case studies: www.finam.ru, www.bigcharts.com, www.gks.ru, http://cbr.bankir.ru/, etc.
Course outline
1.Using built-in functions for data analysis
Excel Functions Syntax. Computational and financial Excel functions.
Conditional formatting for results highlighting.
[1, P.29-49, 75-74]
[4, P. 35-118]
[5, P. 11-15]
2.Graphical Data Analysis in MS Excel
Charts, graphs, and their properties. Customizing different charts. Smoothing. Managing graphic objects in MS Office.
Graphical data analysis. Sparklines for visual representation of data.
Solving system of equations by graphing. Microsoft Equation Editor.
[1, P. 49-60, 75-77]
[2, P. 24-27]
3.Working with large series of data.
Excel database. Creating a database from an Excel spreadsheet. Sorting, searching and editing. Filtering, AutoFilter. Creating custom filters using Excel Advanced Filter. Create two or more sets of Conditions. The use of computed criteria. Database functions.
[4, P. 45-91; 2, P. 52-54]
Vertical and horizontal lookup functions.
[4, P. 92-94]
Subtotalling the data... Merge spreadsheets using Data Consolidation.
[4, P. 5-112]
Pivot Tables and Charts. Creating and manipulating pivot tables. Sorting and filtering subtotals. Calculations in pivot tables: additional calculations, calculated fields and objects.
Pivot charts.
Printing pivot table reports. Printing multi-page tables.
[4, P. 113-154]
[1, P.13-29, 70-72]
4.MS Excel Add-ins for solving economic tasks.
Microsoft Excel add-ins for statistical tasks (Analysis ToolPak) and optimization (Solver).
Analysis ToolPak for Microsoft Excel: finance, statistics and engineering functions.
Solver Add-In.
[4, P. 20-22, 94, 142-143, 181-182]
What-If analysis. Using Solver for solving systems of linear and non linear equations.
Goal Seek. Solving system of equations..
[2, P. 33-38]
[5, P. 16-23]
5.Introduction to Macros and VBA. Introduction to Macros and VBA. Recording macros.
VBA and COM(Component Object Model).
Entering code manually. Recording macros. Visual Basic editor. Editing text in module.
Subroutines and functions.
Structure of subroutines, their classification. Auto subroutines. Recursion.
Manipulating programs (run, stop, restart, edit, debug).
[1,P. 61-70]
[7,P. 2-8, 11-26, 62-75, 78-95]
6.Variables and data types. Manipulating data.
Type declaration of variables. Built-in data types. Scope and lifetime of variables. Variable initialization. Arrays. Custom data types.
Dialog boxes for data input/output. Data manipulation.
[7, P. 9-10, 27-48, 62-75]
[1, P. 61-70]
7.Loop and Conditional Statements used in VBA Excel programming.
The If... statement. Logical operators: And, Or and Not. Logical expressions. . Operator Select Case. Loop functions:: For…Next; For Each…Next; Do…Loop; While…Wend.
[7, P. 49-61]
[1, P. 61-70]
8.MS Excel objects.
Object variables. Object properties and methods. Object model. Object collections. Referencing objects
[7, P. 96-131]
[1, P.61-70, 77]
9.User Forms.
Manipulating forms: constructor mode, run mode.
Event handlers, global variables.
Forms initialization.
[7, P. 132-140]
10.Form controls.
Text controls. Choosing controls.
Manipulating form controls.
Creating and manipulating lists.
[7, P. 141-160]
Distribution of hours
#
|
Topic
|
Total hours
|
Contact hours
|
Self-study
|
Lectures
|
Seminars
|
|
1.
|
Using built-in functions for data analysis
|
28
|
|
12
|
16
|
2.
|
Graphical analysis in MS Excel
|
16
|
|
8
|
8
|
3.
|
Working with large series of data
|
20
|
|
12
|
8
|
4.
|
MS Excel Add-ins for solving economic tasks
|
8
|
|
4
|
4
|
5.
|
Introduction to Macros and VBA. Recording macros.
|
4
|
|
2
|
2
|
6.
|
Variables and data types. Data operations.
|
8
|
|
4
|
4
|
7.
|
Loop and Conditional Statements used in VBA Excel programming.
|
16
|
|
8
|
8
|
8.
|
MS Excel objects
|
16
|
|
8
|
8
|
9.
|
User forms.
|
16
|
|
8
|
8
|
10.
|
Form controls
|
12
|
|
6
|
6
|
|
Total
|
144
|
|
72
|
72
|
|
