Free Tutorials

Android

Angular

Apple watch

ASP.Net

C#

C++

C

CSS

Data structure

HTML

Ionic

Java

Javascript

Jquery

Microsoft SQL

MongoDB

MySQL

NodeJs

Objective-C

Oracle SQL

PHP

python

R

React

Ruby

scala

Selenium

SQL Server

SQL

Swift / ios

Unit Testing

Xamarin

WordPress


Advertisements

Course Curriculum For Excel VBA Training

Introduction
  • Welcome to the Course – Introduction 
Overview of VBA and of the tool
  • The power of Excel with VBA, The VBA code, Recording, Saving and Running Macros 
  • Overview of the Excel Application as a Tool (Version 2016) 
  • Macro Security
  • Protect your work 
  • VBE – The VBA editor, VBA toolbar (Part I) 
  • VBE – The VBA editor, VBA toolbar (Part II) 
  • VBE – The VBA editor, VBA toolbar – Version 2016 
  • The Developer Ribbon 
  •  
The VBA language
  • Understanding the code, Remove the concept “VBA is HARD!”, The Debugging tool
  • VBE to analyze recorded macros, Objects, Methods, Properties, Code cleaning and 
Functions, subroutines, constants and variables
  • Private and Public functions and subroutines, variables by val and optional vari 
  • Functions, subroutines, constants and variables 
  • Recap on Functions and Subroutines 
  • Variables, Private and Public variables
  • Recap on Variable and Constants 
Declare functions 
  • Options: Explicit, Implicit 
  • Declare variables, declare functions in libraries, Types 
  • Dimension variables (Dim, Redim, Redim Preserve) 
  • Dimension variables 
Objects
  • Objects, Excel Objects (Worksheets as objects, Workbooks as objects) 
  • Worksheet properties and events 
  • Workbook properties and events 
  • Cells and Ranges as objects, VBA object in Excel sheets 
  • Assign events to objects in Excel sheets 
  • Objects in VBA and Excel 
Ranges and cells
  • The Range Object 
  • Range properties and selection tips, Refer to Ranges in other sheets 
  • The Offset property, Columns and Rows to refer to Ranges 
  • Retrieve the address in ranges, Size and resize ranges 
  • Columns and Rows to refer to Ranges, Join multiple ranges 
  • Merge ranges, select ranges 
  • Intersect ranges 
  • IsEmpy function 
  • CurrentRegion property 
  • Areas Collection properties 
  • Evaluate positions in Excel sheets with the range properties (End, Row, Column) 
  • identify ranges and cells in the object hierarchy, the value property 
  • Format ranges – create an automatic routine
  • Special cells 
  • Cells and Ranges
VBA Dialogs
  • The Msgbox object, Msgbox properties, Predefined Dialogs 
  • Standard Excel Dialogs, Customize a dialog box 
  • VBA constants, VBA constants for dialogs 
  • Combine VBA constants for complex statements in dialogs 
  • The InputBox 
  • How to find VBA constants and their references 
  • Dialog Boxes in VBA 
Special functions
  • User-defined functions, Excel functions in VBA functions 
  • Custom Excel functions, complex functions
Loops
  • For..Next Loops 
  • Do Loops, While..Wend loops, Do While Loops, Do Until Loops 
  • For Each – The VBA loop: loop in objects 
  • Loops in VBA
Operations and logical instructions
  • Operators, The MOD instruction, The logical instructions 
  • The IF instruction, IF, ELSE, ELSEIF… 
  • With instruction 
  • Instruction to interrupt the execution of the code 
  • Select case instruction 
  • Conditional Instructions
Formulas in ranges and Excel objects
  • Formulas, R1C1 style formulas, Display R1C1 style formulas in Excel, 
  • Retrieve ranges in Excel objects 
  • Conditional formatting of R1C1 formulas 
  • Range properties to be used to retrieve information, The R1C1 reference style 
  • Excel Formulas 
Object events and properties
  • Events and properties in worksheets, workbooks and charts 
  • The Workbook object 
  • The Chart object 
  • The Worksheet object, Default events (used and not used) 
  • Custom events and properties within Excel objects 
  • The Application object 
  • Excel Objects events and properties 
Forms
  • User Forms, Create a Userform 
  • The objects, the code, the controls 
  • Form events, User Interaction Methods 
  • Referring to controls within a form, Design a form 
  • Create interactions between controls 
  • Example of a complex Form 
  • Example of Form with overview of the controls and their properties and methods 
  • Tabs in Forms, Execute a Form from an Excel sheet 
  • Manage multiple forms and access the information of commands in a form 
  • Add references to other object in a form: the example with the calendar and the 
  • Example with tabs and formatted commands 
  • Richtext box in forms, show and hide forms 
  • Add images and reference to websites in a form 
  • Add commands at run time 
  • Scrollbars and textboxes 
  • Add help information to commands, change cursor icon 
  • Form properties: tabindex and management of the focus of the commands 
  • Change the layout of Forms using APIs 
  • Some examples 
  • Summary of the User Form 
Charts in VBA
  • Charts, Embedded Charts and ChartSheets, How to create a chart using VBA 
  • Locate Charts, Customize Charts, Label Charts, Identify the Chart Points 
  • Retrieve Data from a Chart, The Chart Types, Format the Series Collections 
  • Interactive Charts 
  • Sources and Axes, Format Charts 
  • Summary of Charts and Chart Objects

Wrap up

For any inquiry, please contact Atal @9818569825 or write to me at atal.upadhyay@gmail.com. Best yet cheapest training guaranteed.

 

Curriculum for Data Analysis with Excel Pivot Tables

Course outline and structure

PivotTable Getting Started

  • Why PivotTables?
  • Structuring Your Source Data
  • Inserting Your First PivotTable
  • Navigating the Field List
  • Manipulating PivotTable Views
  • PivotTable Options: “Analyze” & “Design”
  • Selecting, Clearing, Moving & Copying Pivots
  • Refreshing & Updating Pivots
  • Dealing with Growing Source Data
  • How PivotTables ACTUALLY Work

PivotTable Formatting

  • Number Formatting
  • Automatically Formatting Empty Cells ]
  • Table Layouts & Styles
  • Using Tabular Tables to Create New Source Data
  • Customizing Headers & Labels
  • Conditional Formatting
  • Data Bars with Invisible Text

Sorting, Filtering & Grouping

  • Sorting Options
  • Why is my Alphabetical Sorting Wrong?
  • Label Filters & Manual Selections
  • Using Label Filters with Wildcards
  • Value Filters
  • Enabling Multiple Filters
  • Grouping Data
  • Automatic Date Grouping
  • Using Slicers & Timelines to Filter Data
  • Breaking Out Report Filter Pages

Calculated Values, Fields & Items

  • “Summarize Values By” Options
  • Avoiding the “Count Of” Trap
  • “Show Values As” Calculations
  • Show Values As: % of Column/Row
  • Show Values As: % of Parent
  • Show Values As: Difference From
  • Show Values As: Running Total
  • Show Values As: Rank
  • Show Values As: Index
  • Show Values As ..More

Inserting Calculated Fields

  • Calculations in Pivots vs. Raw Data
  • Calculating Using Counts
  • Inserting a Calculated Item
  • The Solve Order & List Formulas Tools

PivotCharts

  • Intro to PivotCharts
  • PivotChart Demo: Column Chart
  • PivotChart Demo: Pie/Donut Chart
  • PivotChart Demo: Clustered Bar Chart
  • Prevent Charts from Resizing with Cells
  • Changing Chart Types on the Fly
  • PivotChart Demo: Stacked Area Chart
  • PivotChart Layouts & Styles
  • Moving PivotCharts to New Sheets
  • Applying Slicers & Timelines to Multiple Charts
  • Building a Dynamic Dashboard

PivotTable Case Studies

Wrapping Up

For any inquiry and demo, please contact Atal @9818569825 or write at atal.upadhyay@gmail.com

 

Curriculum – Microsoft Excel – Advanced Excel Formulas & Functions

Course Structure & Outline

Excel Formulas 101

  • Introduction
  • The Formula Library & Auditing Tools
  • DEMO: Evaluate Formula
  • Basic Formula Syntax
  • Fixed, Relative, & Mixed References
  • Common Errors & the IFERROR statement
  • Function, CTRL & ALT Shortcuts
  • Creating Custom Data Validation Rules
  • Fixed vs. Volatile Functions
  • Formulas 101

Conditional Statements & Logical Operators

  • Introduction
  • Anatomy of the IF Statement
  • Nested IF Statements
  • AND/OR Operators
  • NOT vs. “<>”
  • Fixing Errors with IFERROR
  • Common IS Statements
  • Logical Operators

Statistical Functions

  • Introduction
  • Basic Statistical Functions
  • SMALL/LARGE & RANK/PERCENTRANK
  • RAND() & RANDBETWEEN
  • The SUMPRODUCT Function
  • COUNTIFS/SUMIFS/AVERAGEIFS
  • PROJECT SHOWCASE: Building a Basic Dashboard with COUNTIFS & SUMIFS
  • Statistical Functions

Lookup/Reference Functions

  • Introduction
  • Working with Named Arrays
  • ROW/ROWS & COLUMN/COLUMNS
  • VLOOKUP/HLOOKUP
  • Joining Data with VLOOKUP
  • Fixing Errors with IFERROR & VLOOKUP
  • VLOOKUP Reference Array Options
  • The INDEX Function
  • The MATCH Function
  • Using INDEX & MATCH together
  • Combining MATCH with VLOOKUP
  • UPDATE: VLOOKUP Correction
  • The OFFSET Function
  • Combining OFFSET with COUNTA to return a variable list
  • PROJECT SHOWCASE: Using OFFSET to create a dynamic scrolling chart
  • Lookup/Reference Functions

Text Functions

  • Introduction
  • UPPER/LOWER/PROPER & TRIM
  • The CONCATENATE Function (&)
  • LEFT/MID/RIGHT & LEN
  • TEXT/VALUE
  • SEARCH/FIND
  • Categorizing Data with IF(ISNUMBER(SEARCH))
  • Combining RIGHT, LEN, and SEARCH
  • The SUBSTITUTE Function
  • Text Functions

Date & Time Functions

  • Introduction
  • DATEVALUE: Your New BFF
  • Date Formatting & Fill Series
  • TODAY()/NOW()
  • YEAR/MONTH/DAY & HOUR/MINUTE/SECOND
  • The EOMONTH Function
  • The YEARFRAC Function
  • WEEKDAY, WORKDAY & NETWORKDAYS
  • The DATEDIF Function
  • PROJECT SHOWCASE: Designing a Sample Budget Pacing Tool
  • Date/Time Functions

Formula-Based Formatting

  • Introduction
  • Creating, Editing & Managing Formula-Based Rules
  • Highlighting Every Other Row Using the MOD function
  • Formatting Cells Based on the Value of Another Cell
  • Formatting Cells Using Statistical Functions
  • Formatting Cells Using Text Functions & Logical Operators
  • Formula-Based Formatting

Array Formulas

  • Introduction
  • Rules of Array Functions
  • Pros & Cons of Array Functions
  • Vertical, Horizontal, and 2-Dimensional Array Constants
  • Using Array Constants in Formulas
  • Named Array Constants
  • The Transpose Function
  • Linking Data Between Sheets: Array vs. Non-Array Comparison
  • Returning the “X” Largest Values in a Range
  • Counting Characters Across Cells
  • Creating a “MAX IF” Array Formula
  • “MAX IF” with Multiple Criteria
  • Using the Double Unary Operator (“–“)
  • Array Formulas

Bonus Functions

  • Introduction
  • The INDIRECT Function
  • HYPERLINK: as Awesome as it Sounds
  • Tapping into Real-Time Data with WEBSERVICE & FILTERXML

Wrapping Up
Conclusion & Next Steps

For inquiry and demo, please contact Atal @9818569825

How do I get more storage on seedr.?

Seedr.cc offers the following methods to get you free space.

Azure Blob Storage vs. File Service

Azure Blob Storage and File Service offer the ability to store file(s) and folder(s) (I understand that blobs can store any binary object, but any serialized binary stream is just a file at the end of the day) in a hierarchical structure that mimics a file system. Here are few points to differentiate between two:

  1. You can’t mount Azure Blob Storage as a native share on a virtual machine.
  2. Azure Blob Storage isn’t hierarchical beyond containers. You can add files that have / or \ characters in them that are interpreted as folders by many apps that read blob storage.
  3. Azure File Service provides a SMB protocol interface to Azure Blob Storage which solves the problem with (1).
  4. Pricing: Blob storage is much cheaper than file storage.
  5. Portability: With blob storage if you decide to migrate to a diff platform in future you may have to change your app code but with File storage you can migrate your app to any other platform that supports SMB (assuming you are using native file system APIs in your app)

When to use Azure Files vs Azure Blobs vs Azure Disks

We have three main ways to store data in the Azure cloud – Files, Blobs and Disks. All three abstractions take advantage of Azure Storage stack and the Azure platform. The following section compares the advantages of each abstraction.

Azure Files: Provides a SMB 2.1 interface in addition to the REST interface to provide access to files. The SMB interface enables applications running in the cloud to use native file system APIs to read and write from files. A file share is best for:

  • Lifting & shifting applications to the cloud which already use native file system APIs to share data between pieces of the applications
  • Storing development and debugging tools that need to be accessed from many cloud instances
  • Applications that want REST access to data from anywhere and SMB access to data from within the region the storage account is located in

Azure Blobs: Provides a REST interface for massively scale out object storage.

  • Applications can be written to use REST APIs to store unstructured data into Azure blobs at a massive scale (a single container can be 500TBs in size). .
  • Supports streaming scenarios and random access
  • Access to data from anywhere via REST

Azure Disks: Provides persistent disks to be attached to Azure virtual machine in which data is made durable by storing the disk as a fixed formatted VHD in a page blob in Azure storage. When used as a VHD, the disk can be attached to a single VM, but not shared across VM instances.

  • Lift & Shift applications that use native file system APIs to read and write data to persistent disks, but does not require this data to be available to other cloud VMs. Note that a single disk can be attached to only a single VM at any given time.
  • Individual data stored on the disk is not required to be accessed from outside the VM as the data is stored in a VHD formatted with NTFS. However, the VHD can be downloaded from anywhere via REST API and then loaded onto a local virtual machine.
  • Disks can be snapshotted to create point in time read-only backups. When recovery needs to take place, a snapshot can then be copied to a different blob and data can be recovered.

When accessing Azure Files via SMB 2.1, the share is available to VMswithin the same region as the storage account. The REST interface on other hand is available from everywhere. This new service enables customers to “lift and shift” legacy applications that rely on file system APIs to the cloud without code change.

 

 

Difference between SOAP and RESTful Webservices

Difference between SOAP and RESTful Webservices

SOAP:

1. Developer View: Object oriented
2. Standards Based: Yes .

SOAP web services are based on SOAP and WS-* specifications

For acquiring security tokens,it uses WS-Trust.

For conveying security tokens, it uses WS-Security

For defining policy, it uses WS-Policy

For suppoting distributed ACID transactions, it uses WS-AtomicTransaction and WS-Coordination

For acquiring interface definitions, it uses WS-MetadataExchange

For providing end-to-end reliability, it uses WS-ReliableMessaging

For establishing security context, it uses WS-SecureConversation

3. Security: SSL, WS-Security .

WS-Security provides end-to-end security covering message integrity and authentication

4. Transactions : WS-AtomicTransaction
5. Reliability : WS-ReliableMessaging
6. Performance: Good
7. Caching : No
8. Message Size : Heavy, has SOAP and WS-* specific markup
9. Message Communication protocol : XML
10. Message Encoding :Yes

SOAP Web Services support text and binary encoding
11. Service Description :WSDL
12. Human intelligible Payload :No
13. Developer Tooling :Yes

Complexity of SOAP Web Services dictates the need for using frameworks to facilitate rapid application development.
14. Orientation :Wraps business logic
15. Abbreviation: SOAP stands for Simple Object Access Protocol
16. Who is using SOAP? Google seams to be consistent in implementing their web services to use SOAP, with the exception of Blogger, which uses XML-RPC. You will find SOAP web services in lots of enterprise software as well.
17. Simplicity: No
18. Transport protocol support: HTTP, SMTP, JMS

Multiple transport protocol support makes SOAP Web Services flexible

REST:

1. Developer View: Resource Oriented
2. Standards Based: No
3. Security: SSL
4. Transactions : No
5. Reliability :Application specific
6. Performance: Better

Caching and lower message payload makes RESTful web services performance efficient and scalable
7. Caching : GET operations can be cached
8. Message Size : Lightweight, no extra xml markup
9. Message Communication protocol : XML, JSON, other valid MIME type .

This flexibility of REST makes its extremely useful in providing consumer need specific message payloads
10. Message Encoding : No

RESTful encoding is limited to text
11. Service Description : No formal contract definition

In REST, no formal way to describe a service interface means more dependence on written documentation
12. Human intelligible Payload : Yes
13. Developer Tooling : Minimal or none

REST on the other hand due to its simplicity can be developed without any framework
14. Orientation : Accesses resources/data
15. Abbreviation: REST stands for Representational State Transfer
16. Who is using REST? All of Yahoo’s web services use REST, including Flickr, del.icio.us API uses it, pubsub, bloglines, technorati, and both eBay, and Amazon have web services for both REST and SOAP.
17. Simplicity: Yes
18. Transport protocol support: HTTP

Areas where SOAP based WebServices is a great solution:

Asynchronous processing and invocation: If application needs a guaranteed level of reliability and security then SOAP 1.2 offers additional standards to ensure this type of operation. Things like WSRM – WS-Reliable Messaging etc.
Formal contracts: If both sides (provider and consumer) have to agree on the exchange format then SOAP 1.2 gives the rigid specifications for this type of interaction.
Stateful operations: If the application needs contextual information and conversational state management then SOAP 1.2 has the additional specification in the WS* structure to support those things (Security, Transactions, Coordination, etc). Comparatively, the REST approach would make the developers build this custom plumbing.

Areas where RESTful WebServices are a great choice:

Limited bandwidth and resources: Remember the return structure is really in any format (developer defined). Plus, any browser can be used because the REST approach uses the standard GET, PUT, POST, and DELETE verbs. Again, remember that REST can also use the XMLHttpRequest object that most modern browsers support today, which adds an extra bonus of AJAX.
Totally stateless operations: If an operation needs to be continued, then REST is not the best approach and SOAP may fit it better. However, if you need stateless CRUD (Create, Read, Update, and Delete) operations, then REST is suitable.
Caching situations: If the information can be cached because of the totally stateless operation of the REST approach, this is perfect.