Here you are going to learn the way how you can automate power Point activity using excel vba.
Here in this article you are going to learn the way how you can scrap the data from the browser and update in the excel spreadsheet.
In order to communicate with internet explorer, you need to add the internet explorer type of object to Vba module. To add there are two types of procedures early binding and late binding. Once you define the object then you need call the specific url to start scrapping the data from the browser. Then we have to try to catch the getElementsbytagname, getElementsbyid, getElementsbyclass or any other element to pull the data. If you are able to catch the data, then you can paste the same data into excel sheet.
Excel VBA can control almost all applications. All we need to do is specific DLL file to be linked in the reference of specific VBA Module. When you want to automate Microsoft outlook related activities such as sending bulk emails to various people, getting alerts when specific email related mails reached to inbox, setting up bulk meetings at the same time, searching in specific folder of outlook for emails related content to fetch into excel spreadsheet and getting email body or subject lines into excel for multiple emails. Anything that need not judge in outlook can be automated using Excel VBA. Because Outlook also Microsoft Application. Hence it is very easier to control by Excel VBA.
As soon as you add outlook related DLL (Dynamic linked library) files in VBA module reference, you can start writing the code to automate the outlook related activity. Here I will show you one small example to automate the outlook related activity.
There are two types of declarations you can use to communicate with Outlook Application from Excel VBA. First one is early binding and second one is late binding.
Early binding option to connect with Outlook from Excel VBA
In this option you will get the help while writing the code from VBA Module. It is bit easier to use this option because you can see the choices to call the various outlook related objects
Late binding option to communicate with Outlook from Excel VBA
This is very easy to use and difficult to get the help with in the VBA module environment. Here you cannot get any help to call the outlook objects. When you are expert in the programming, you will know all the objects, that time you can use this Late binding concept to work with outlook application automation.
Open outlook application to use the above code from the Excel VBA Module.
There are many excel works in the market for freelancers. Web scraping, Excel templates, Dashboards, Analytics, Data Analysis and VBA Macros. Here we are going to know on each and every work that freelancers are getting in the market. If any wants to become freelancer for Excel related works, this will be helped them a lot. Excel job would be very enjoyable to yourself you know excel very well. It has many features like conditional formatting, formulas, Array formulas and user defined formulas. Also it has many events like workbook, sheet, cell activate etc.
This would be happened when user requires some data from web browser to excel. Most of the times web scraping related projects would be outsourced to freelancers. This work can be done by various programs like python etc. Even using Excel VBA programming we can scrap the data from web browser to Excel spreadsheet. In order to do this activity, manly we can use the” get element by id” related codes in the VBA programming to scrap the browser data. VBA Programming also known as Excel Macro programming. Here in this web scraping model, due diligence is very important than programming skill.
These are very useful to get important numbers from raw data. Here most of the times Sumif and Countif, Vlookup, Hlookup, Index, Match, if, nested if, and, or, not, indirect, and Text formulas can be used to fetch the important numbers from the raw data and project in the Excel template based on the given requirement. Also you can align the column headers to update the data on frequency basis. Excel templates very similar to Dashboards. The only difference between dashboard and template is data size would be lesser in the excel template comparing to dashboard. Excel templates can be used for simple tasks means one to three sheets of the data related numbers projection. As a freelancer, I am getting many excel template requirements to do in the market.
Analytics in Excel:
In the MS Excel, we have lot of analytical facilities. Now we have many software programs for analytics like SAS, R, MATLAB and so on. But previously we used to do in the MS Excel. Single Linear regression, multi linear regression, predictive analytics and statistical analytics like finding standard deviation, coefficient. Small companies are doing analytics in excel even now since they cannot afford the price of the other software program’s license cost. Analytics base works can be under the data mining concept related projects for freelancers in the market.
Data analysis in the MS Excel:
90% of data analysis would be completed with graphs and charts in the ms excel. Remaining would be arranging the data. Hence we need to arrange the data in a specific format and then draw the graphs or charts to interpret the correct scenario of the data. We get many data analysis projects in the freelancer market. These concepts are also under the data mining department.
Hope you understand all about excel based freelancer works, you can comment in the below, if you have any doubts about freelancer jobs.
Excel dashboard designing is very easy, if you have good grip on data handling and business drivers of the particular activity. Once you prepare the dashboard, it can be used for a long time until that particular process get huge changes in the dimension of the data. It is going to be dashboard template for your valid information. Hence you can use the same template every time by clearing the old data and feeding new data into the dashboard template. Excel dashboard is creation is one of major work in the MIS department
What is Excel dashboard?
Dashboard is a excel template that will have all the KPI (Key performance indicators) and CPI (Critical performance indicators) metrics along the visual graphs for all the important data points. Also raw data would be included in the dashboards by cleaning the data. Usually raw data sheets can be hided when you are circulating to the management.
Important points while building the excel dashboard
- Template should have the home page or summary sheet which should be connected with all the display sheets as show in the below screen shot
- Main sheet which is going to be circulated for management as screen shot will have all the important number which are related to KPI & CPI metrics. The same screen shot can be sent as an email copy and provide the link below for the dashboard shared location by hyperlink
- Graphs and tables can be showcased in the rest all of the sheets for the same business drivers.
- Main sheet related each and every KPI or CPI metric can be enlarged in the separate sheets by creating the visualized tables. If management wants look into particular business driver, they can go and check in line.
- Based on requirement, we can write the macros also to automate the dashboard. This can be very helpful to clean the data.
- There are many varieties of dashboards, like Accounts payable dashboard, Procure to Pay dashboards, Accounts receivable dashboard, finance related dashboard, banking related dashboard, marketing related dashboard and sales dashboard,
- Usually dashboards have the huge data with lot of formulas. In such cases, you need to turn off the formula calculation to update the raw data in the template sheets. Else dashboard gets hang each and every time when you update the data. It keeps on calculate the formulas.
- If it is daily dashboard, you can clear the data on daily basis or weekly basis in the sense weekly you can clear the data, similar way for month or quarter as well. In order to clear the data, it is better to write small VBA macro code as described in the above point. You can save some time to clear all the data from various sheets.
- It would be awesome experience in creating the excel dashboard. It takes some time to create fully. Because once you start, you will keep on getting ideas to key the information in understanding manner for the management.
- Once you create the template, it is very easy update the data into the dashboard based on your frequency either daily or weekly.
This is what all about the dashboard creation, hope you understand. And this was written based on my work experience.
Excel VBA’s role is very important in the MS Excel application. Microsoft Excel is main application in the MS Office. Because Office related tasks like MIS activates are getting done in the MS Excel. In order to automate this Excel tasks, VBA Macros are best choice. And we can control any application from the Excel VBA programming. Hence VBA programming has significant role in the MS Excel automations. Usually when user is preparing some report and he is taking around one hour to do that which is not a judgmental task. Since it is copy paste work, you can automate with VBA Macro programming and save that one hour time. User has to spend time only on reviewing the report. This is what the best advantage of the Excel VBA Macros.
VBA Macros programming can be written with in the excel environment. In order to see the VBA program environment, you have two ways. One is Alt + F11 short cut key from Excel workbook. Or you can click on view menu and then see the macro option then view option as shown in the below screen shot.
Then either you can create new by giving the name or you can select the existing one, if you already written. Once you are in the VBA environment, you can see the coding, if you have already written. Else you can start writing the code in the white screen. Here there is an option called record macro. This is very useful option for the beginners. Sometimes when you are not familiar with VBA programming, this option can be used to see the exact code for particular desired thing that you want to do in the front end. This is main feature of the excel macro programming.
Record Macro option:
This option works like a tape recorder. Just click the record macro option and start doing some activity in the Excel, you can see the pre written code in the VBA module of that excel workbook. Even intermediate users can use this option as a help to know, when one particular piece of code. You can find the record macro option in the above screen shot.
Writing VBA Code:
When you press Alt +F11 option, you will be in the VBA Macro environment of that particular Excel workbook. Then you can insert module by clicking in the Insert menu. Once you done it, you need to start subroutine.
Within the below codes, we have to write the macro coding part.
I am going to write small VBA Macro for you to understand yourself.
Adding two numbers program
Macro Sample program
Dim I, j, k as integer
K= I + j
Range(“A1”).value = k
Similar way you need to write the vba code for any other tasks in the Excel. VBA programming is ocean to automate the things which would be available in excel. Almost every application we can control with VBA programming. All you need to do is adding the DLL of other application. Even in this programming language we can not automate the judgmental cases. This is only the drawback in this programming language. If we are effective programmer, we can just save the lot of FTEs ( Full Time Employees ) work. It would be converted into money. Sometimes If you are in six sigma projects, then we are eligible to take the green belt projects.
Hope you understand about the Excel VBA Macro the way how you can write the coding part within the Excel.
Now a days in the USA and Uk countries prefer to outsource the work for reducing the cost to their companies. Here Freelancer concept came into the picture. And it became wonderful opportunity to the people who wants to work from the home. Also freelancer can work at any time based on his interest where employees can only work in one particular point of time. In order to work as freelancer, we have many websites which would work on commission basis. Here i am going to provide some name from those. Fiverr.com, Upwork.com, Freelancer.com etc,,
Since my website is belongs to excel based, here i am going to brief you about the Excel VBA Freelancer tasks. First thing all excel related works can be done by this freelancer. Also before you give any work to Excel freelancer, you must need to discuss with him about your activity that you want to automate or formulate. Then excel vba freelancer checks the feasibility study on that activity and confirm you the timeline. And let you know in case if there any challenges to do that. Then as a buyer, you can negotiate that particular piece of part to do in a alternate manner. This is how it goes.
I am also an Excel VBA Freelancer and you can find my profile in the below mentioned link https://www.fiverr.com/sivaramakris170 . Being as a excel vba freelancer, I got some relief from the daily basis job activities. In day to day employee life I did not have the freedom wherein I am getting lot free time and able to earn better that what I was earning as an employee. Hence, if any one has the outstanding skills in any aspect, you can take my help, in order to become as a freelancer. That would become an awesome step in your life.
As a excel vba freelancer, i am getting works like formulating the workbooks, automating the Excel reports using VBA Macros, in order to save the processing time of the user. Using the Excel VBA, we can do almost many things like communicating with other applications, fetching the data from websites to excel. If you are a Excel VBA freelancer, you must need to clarify the user, as what you are going to automate or formulate and it would be helpful to use that particular tool. Also need to support to the beginner users as they will have lot of confusions until they get habituated with your tool. Once you do 2 or 3 tools for the users and you get positive feedback from them. Then you will become very busy with offers. Feedback system or reviews are very important for the freelancer. Hence as a freelancer, you should be more specific on what you are going to do to the user and how it is going to helpful in future.
Hope you people understand that what exactly is freelancing job and how it is going to be worked out. You can comment in the below in case, if you have any questions. Also i will give you the referral link to enroll in the Fiverr website. If you are interested, you can signup and check you luck. http://www.fiverr.com/s2/2e6e2eefb0
Sumif formula has significant role in MS Excel. It is very useful to create the dashboard. Also it can be used with combination of formulas based on the requirement. When you need to sum the values based on the one particular criteria (value), you can use this Sumif formula. This can be used as an array formula, if necessary.
Management Information System (MIS)
Here we are going to provide you detailed information about what is MIS, where MIS activities can be used, what exactly to be done in the MIS, How to manage MIS Reports and MIS Excecutives / Data Analysts, how to get MIS Executive Job for freshers, experienced people, Qualities of the MIS Executive / Data Analyst and MIS job advantages and disadvantages.
We also discuss some important MIS Daily Reports like Head count report, SLA Report, Procure to pay report ( P2P Report ), AP Dashboard, AR Dashboard, GL Dashboard Invoice Summary Report, Payroll team related.
What is MIS?
MIS is nothing but managing the huge information in a sequential order in order to retrieve the required information. Whenever we require the particular information from the huge database, you should have the proper plan to maintain the data.
Where MIS activities can be used?
MIS activities can be used in the corporate companies, government sectors and many other places where the huge data involves.
What exactly to be done in the MIS?
As discussed in the above, we have to maintain the huge data to retrieve again on need basis. Hence usually if the data is limited we can go head with the MS Excel. And MS Excel is compulsory to use in the MIS. Because calculations purpose MS excel is the excellent application. Usually in the MIS, Information would be stored as reports, dashboards and in the other data formats. In order to store the data in the sequential order, we will maintain in the reports wise. If MS excel is not capable to maintain the data, then we can go ahead with the MS Access to store the data. IF data is more 1 GB in one single file then we have to choose the SQL or other database model.
Excel single file capacity 30 MB. if exceeds more than 30 MB, then we have to choose the MS Access
MS Access single file capacity 1 GB. If exceeds more than that, then we can choose SQL database or any other database oracle based availability.
How to manage MIS reports, Dashboards ?
Managing the MIS Reports:
Usually we prepare the reports for the daily, weekly, monthly, quarterly, half-early or annually in a single file for the respective day/ week/ month etc.. we will keep the important cleaned data in a particular order column header wise to pick the important numbers ( Business drivers) to show the activity related status in a report. These reports to be cascaded to top level management and they take the decisions based on these numbers also they come to know the present situation of the activity.
Managing the MIS Dashboards:
When you create the dashboards, you can keep the data sheets in the hidden mode and show only the display sheet which have important numbers and graphs related sheets. based on the frequency data can be empty and refill the data into the dashboard template.
Here one important thing is we need to have the formulas if we are going to prepare in the MS Excel. Excel formulas would play the key role in the MIS Reports/ Dashboards. This is one excellent advantage with the MS Excel. Any difficult thing can be achieved with formulas. we have the good formatting look in the MS Excel. That’s why management prefers to show the data in the Excel than any other applications.
MIS Executives / Data Analyst :
MIS People are decision makers who can advise the awesome ideas to the top level management. Operation manager must have the MIS skills. MIS executives are also called as Data Analyst.
Qualities of MIS Executives / Data Analyst :
This job role is very crucial for the company. All the confidential information will be there with the MIS Executive. Hence it is very important to hide the information from the non related people. Data Analyst must hide the lot of information from non related people.
Assume that John is the MIS Exective and he is supporting to 4 departments in the same team. Hence he has all the confidential information of 4 departments. In such case one of the departments manager Rock came and asked about the total income for the month for the other department. Then John will be in trouble, he can not reject his request and at the same time he should not provide that information to Rock. Then John should say, “i am yet prepare the report, once it is done, I will let you know”. That’s how john should escape from Rock’s request. Because it is not related to Rock’s department. Due diligence is very important quality for the Data Analyst.
Payroll Team Related MIS Activities:
Pay role team also has the MIS activities. Since this team maintains the salaries of the all employees, it is highly confidential task. Also it is very challenging job to process the salaries on monthly basis. I will provide another example which was happened to my colleague. He has process one guy salary to another guy because of the naming confusion. Two employees have to same name. That’s why he got confused and interchanged their salaries. It has become very big mistake in the Payroll MIS team.
Similar way finance team also has MIS activities. In this team all the reports will deal with the money related. Hence it is very challenging job to do. Every time MIS guy would deal with lot of money related numbers. If any report goes with wrong number, then company would be in trouble. Hence in this department, management would do double check all the MIS activities. Still sometimes errors occurs. whenever error occurs in the finance team, management gets many calls from their higher level like from clients. Hence It is very tough thing to manage, sometimes need to pay the penalty to client by company.
How To Get MIS Executive Job as a fresher or experienced?
- Educational qualification of the MIS Executive is a graduate from any bachelors
- Hands on experience should be there in the MS Excel mainly and better to have power point presentation skills.
- Should be capable to speak English fluently without any MTI ( Mother tongue influence). Means when people speak their mother tongue should not be appeared in the spoken English. Because sometime we may need to handle client calls at that point of time. Client may face problem to understand because most of the clients are from the USA, UK or Australia. If the client is from India that is fine. If we keep practice on daily basis, it is easier to remove the MTI from our spoken English
- It is better to have 30 wpm minimum typing speed. Because sometimes there are requirements to type some information on need basis. It is very rare but good to have the typing skills
- Better to have the Analytical knowledge, since always MIS executive would work the huge data. Freshers can ignore this point as they will not have any idea at the beginning stage
- MIS Executive always plays with the huge data. Hence should be well versed with data analysis knowledge. Means when executive receives the data, need to check the what exactly the information and what exactly to be helped to management and what are the business drivers in the given data as such.
- Good to have 7 QC tools (1 Pareto Analysis, 2 Histogram, 3 Fishbone analysis (cause and effect diagram), 4 check sheet, 5 Control chart 6 Scatter diagram, 7 Stratification) knowledge but not compulsory.
- Pivot tables preparation is must for MIS Job position. Because most of the time need to handle with the huge data. When we have huge data, it is very difficult to understand the whole data at once. Hence if MIS job holder has capability of the creating pivot tables, It helps him a lot in understanding the whole data in a easy manner. Also helps him in creating the reports and dashboards
- Should be capable with Excel dashboard creation, Excel reports, Excel template. When you say dashboards or reports. These are involved the excel formulas and other excel techniques like hide in the excel sheets and all. In order to learn this either you can see the older dashboards or google it with the excel dashboards and get the sample dashboards to understand. Freshers can ignore this point as they just need only excel formulas skills.
- Excel Charts or Graphs would play key role in the MIS job. As management only looks these charts when they have no time to see whole information. Based the given data, MIS Executive would select the graphs like bar graph, pie chart or any other which suits to existing data points
The above points are very important for experienced guys and freshers who wants to join as MIS Executive. Few points are not compulsory to fresher as i have already mentioned in those respective points.
MIS Reports / Dashboards:
Here we can learn about some important MIS reports and dashboards with examples.
Head count report:
This report conveys the number of people working in the respective team or in the organisation. HR will have this head count report for entire company as he/ she is responsible to maintain this report. If the team size is bigger more than 50 people approximately. Usually MIS Executive or HR prepares this report on monthly basis as they have to know the number people in the respective team or in the company. Now we can learn how to prepare this report. At first you need to have the all the employee details in the one worksheet. Like First Name, Last Name, Email address, Reporting manager, Joining date etc. As shown in the below
In the above list, whenever employee leaving the organization or team change, we need to capture. Then we can find the opening balance for the next month, closing balance for the current month and department wise number of employees.
Every month end you have to prepare this report, then you will come to know the exact head count on monthly. When you do this, you also find the attrition percentage. This is confidential report.
Procure to pay report ( P2P Report ):
This report is mainly for vendor payments and invoice ageing status. When we are producing one product, we require some raw materiel to produce. or if we have to provide some service also we require some things in order to provide the service. To provide the service or raw material, we have to pay amount to vendors. At that point of time, this procure to pay concept comes into the place. In order to pay the amount, we will have some time and dead line. we prepare this report to know about the vendor payments mainly. We will have some service level agreement to pay the amount.
when we need to pay some amount to the vendors, we will have some duration to pay that. Hence we must maintain one report for calculating the time on day to day basis. In order to do this process, we will have the invoice copies. These are classified as purchase order, No purchase order, Travel & Expenses as such. In this case No purchase order to process, value of the purchase order payments to be captured. This report will have the below tabs ( Sheets) approximately. Similar way various business models will have their respective tabs.
Summary Dashboard sheet can be shown as in the below manner. This is just an example to understand by yourself. You can prepare the same in your own manner based on your client requirement.
SLA Report sheet has the ageing like 0-3 days, 4-7 days, 8-14 days, >14 days. Using the excel formulas, we can pull the number of days into the above ageing columns as shown in the below
Similar way we can show the Purchase order, No Purchase order, Freight, Travel and Expenses summary in different sheets. Once we prepare these sheets, we can show one consolidated worksheet to for all the overall information. Then we have to prepare the payments sheet, to show the payment information for the vendors as on that particular day.
Hope you understand the Procure to pay report in detailed.
MIS Job role Advantages:
- You will not get continues flow of work. Actually speaking bit lesser work most of the times. Because 100% Accuracy required in this job. 4 hours of the work would be assigned in the 8 hours of job time.
- Typing work would be very lesser comparing to other non voice jobs.
- It would be very interesting and enjoyable, when you are dealing the challenges with Excel formulas or helping others to solve their excel template issues
- Most of the other team members would respect the MIS Executive, because this job role would support to the team as I explained in the above point.
- Once this job gets habituated with all the assigned tasks, it would be cake walk for working in daily.
- Managers would rely on the MIS Executive’s report numbers to take their crucial decisions in the business
- Many team members would try to catch you all the time to learn excel tricks from MIS Executive
- This job role would circulate reports to top level management hence MIS Executive will have the good connectivity with top level management like BU Heads.
- This job role will even send the reports to clients. Hence even in client location people also gives the respect to this job role. Since they will also send requests to prepare the Ad hoc reports
- By doing all these activities, usually MIS Executive would become an subject matter expert in their respective team activities.
MIS Job role Disadvantages:
- MIS guy would rely on production team member’s inputs to prepare the report, dashboards. This would little bit hectic process due to some reason, when they are unable to send the inputs, Report circulation would be delayed. Managers would ask the MIS guy for the report as they would like to know the process current situation.
- Whenever clients come with ad hoc reports requirement, it would be an urgent requirement. MIS guy should prepare the daily reports as well as this ad hoc report for the client. That particular time, this MIS Executive would face a problem to work in fast pace mode. In the Workforce management team, MIS Executive has the always tasks in the queue to do.
- Sometimes production would update the numbers incorrectly and the same would be taken by MIS guy and getting errors in the report after next day. That time it would be bit difficult to prove that he has done correctly.
- Payroll team MIS job also bit difficult to do because it is almost similar to bank job. Every paisa should be accountable while preparing the payroll salary report or any other report in the payroll team.
Based on the above points, you can understand MIS Job would be enjoyable also hectic task sometimes. Hence MIS executive should learn how to play the safe game in the job role while dealing with the production team or any other top level management.
Index formula advantages and disadvantages
Index formula is equal to Vlookup and HLookup. Both the formulas capability was included in the index formula. Vlookup has a drawback like you can not pull left side value. But using this Index formula you can overcome that problem.
Index formula syntax:
within the index if you include the match function. Then this formula is stronger and best formula in the excel. Match formula can be used within the rownum or columnnum place in the above syntax. When you need to find the particular column within the data data set, then we have to use index match combination.
Index formula in reports
This can be very useful to prepare the SLA report for the project.
you can keep the data sheet hidden and pick the hidden values to display sheet in the SLA dashboard. Then keep the conditional formatting to show the red, amber and green for KPIs (Key performance indicator) and CPIs( Critical performance indicator)
Index formula is known as advanced excel formula. Since it