Friday, September 7, 2018

Python / SQL / VBA-Excel Private 1 on 1 Tutor NYC Analytics

New York Python SQL Bootcamp Coding Classes (Affordable & Cost-effective Machine Learning). Best Free classes in NYC. SQL 101 & Python 101 Classes. Big Data Science Classes for beginners interested in Analytics & Data Science. Weekend part time and full time classes in Manhattan & Queens. 1 on 1 Tutoring also available. Free weekend 2hrs class. Small group courses (2-3 attendees), free takes and 1 on 1 : Python 101, Python Data Science Immersive Python for Data Analytics. VBA Macros Immersive. SQL 1 day Class.Project and Portfolio Oriented on weekends and also free evening classes in NYC. Upload your portfolio to get better job. Best Python Class in NYC. FREE RETAKES.


 --------------------------------------------------------------------------------------------------------
Python 101 Intro to Python
Create Azure Notebook Account (15 minutes)
Downloading Python Anaconda to your laptop
Intro to common terminology (AWS, Jupyter, Azure Notebook)
Hello World Practice, Variables, data types, functions, loops
Questions (Intro to adv features Part 2)
Python 102
Numpy & Pandas
Importing from different sources, Cleaning Data & Handling Missing Data
Data Wrangling - Group by, Joins and Pivot
Python 103
Intro to common Datasets (15 minutes)
Visualization from Matplotlib
User defined Functions and in built commands for Wrangling
Applying all to your dummy dataset / online dataset
* Easy Revision from the notebook
 -----------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
Python 101 and Object Oriented Python Advanced Python 102 SQL Basics
Machine Learning Fundamentals Scikit Learn 101
EDA Charting using Matplot, Seabourne and Pyplot 101
Pandas for Analytics (SQL and Excel equivalence) 101
Regression and Logistic Regression - Python and the Math Behind 101
SV, Stochastic Gradient Descent, Naive Bayes Classification
Decision Trees and Random Forest Ensemble Models 101
Unsupervised Learning Clustering K-means Neural Network 101
Dimension Reduction using PCA, Lasso and Ridge 101
Big Data Hadoop Spark Mapreduce 101
Natural Language Processing 101
Web Scraping Python using beautifulsoup and selenium web driver 101
Tensor Flow and Keras 101
Django 101, Flask 101

Full day Course schedule:
Sunday Python Part 1 & Part 2
Monday: Blockchain
Tuesday: Pandas Data Analytics & Wrangling
Wed: Hadoop Big Data / SQL
Thursday: VBA Macro
Friday: Machine Learning
Saturday: Hadoop Big Data / SQL
https://www.meetup.com/New-York-Python-SQL-Bootcamp-Data-Science-Analytics/



BAINYC Math Matters Python / SQL / VBA Classes in Queens, NYC
www.bainyc.com
1 on 1 Private Tutoring at $29 per hour

Tutoring Venue::
Mathmatter Tutoring
3707 74 Street 3rd Fl Suite 7 · Jackson Heights, ny 11372
How to find us: Roosevelt Ave, Jackson heights subway

Small group courses:

Python 101 $49
3 hours
Print Hello World Azure Notebooks & Anaconda Book and Content Functions (Arguments and Return) Loops (For While) If else List/Dictionary

Intro to Python (Intermediate) $174
9 hours in total, 3 sessions/days of 3 hrs
Print Hello World Azure Notebooks & Anaconda Book and Content Functions (Arguments and Return) Loops (For While) If else List/Dictionary
Nested Loops with if else List/Dictionary (JSON) Class Lambda Functions List Comprehension


Python Immersive $499
35 hours in total, 5 sessions/days of 7 hours each

Print Hello World Azure Notebooks & Anaconda Book and Content Functions (Arguments and Return) Loops (For While) If else List/Dictionary
Nested Loops with if else List/Dictionary (JSON) Class Lambda Functions List Comprehension
File Handling Web Scraping Exception handling SQLite Python
Capstone Project for Github Portfolio


Python for Data Science $499
35 hours in total 5 sessions/days of 7 hours each
Print Hello World Azure Notebooks & Anaconda Book and Content Functions (Arguments and Return) Loops (For While) If else List/Dictionary
Nested Loops with if else List/Dictionary (JSON) Class Lambda Functions List Comprehension
Pandas Plotting Time Series Data Cleaning
Capstone Project for Github Portfolio

Excel VBA 101 / SQL 101 $49
3 hrs / 1 Session / Days
$49

Introduction to Excel VBA / Introduction to SQL (Intermediate) $174
9 hours, 3 sessions / days

Excel VBA Immersive / SQL Immersive (Advanced) $499
35 Total hours, 5 Sessions / Days

Also available* at:
Practical Programming
Address: 115 W 30th St 5th fl, New York, NY 10001
Email me for prices.

To register for classes:: https://www.meetup.com/New-York-Python-SQL-Bootcamp-Data-Science-Analytics/

Notebook:
https://notebooks.azure.com/shivgan3/libraries/PythonClassesNYCBootcamp

Youtube:
https://www.youtube.com/playlist?list=PL_-KSXJS5pxO1YfaVjx9kr0SGO_gTb-oV

PPT:
https://docs.google.com/presentation/d/1LmBC6uq2iZPDSnqjdaZqILkDJjl4SB-97ARgPFEejHE/edit?usp=sharing

Instructor
Shivgan Joshi
929 356 5046
Linkedin (Instructor):
https://www.linkedin.com/in/shivganjoshi/

Tuesday, September 4, 2018

QcFinance Indore India

How many times have you seen problems or opportunities for improvement in your workflow and promised to fix them if you only had the time or if you only could train my employees with such expertise?

Unforeseen, undocumented, forgiven, hidden cost of issues like below are bringing your valuations down:
1.    Poorly-understood, legacy framework mostly into excel—dilapidated on unknown, unstated assumptions
2.    Slow process of calculations
3.    Un-scalable excel models that are difficult to maintain, improve, replicate or extend
4.    Increased pressure, requiring added reporting and enhanced auditing
5.    Lack of in-house research resources for SQL,Python and R – both of which are open source (free – with no extra burden)
6.    Over-reliance on external novel untested software from vendors
QCFin provide solutions for such hurdles, working quickly, properly, and thoroughly to create more value in your employers and finally an increased valuation for your firm. 

Our company qcfinance.in, Indore specializes in developing advanced modeling, simulation, data analysis and visualization software for clients. Using the MATLAB, R, Python suite of numerical analysis software from The MathWorks, together with other technologies such as C++ and VBA, allows us to solve a wide range of design and implementation problems for our clients including risk analysis in active and passive strategies.

Leveraging expert knowledge in areas such as computational finance, we have immense experience in delivering customer specific requirements from clients across the nations.

Get support in Research Writing, Software Development & Back testing.

Beside this, we also provide training for CFA, FRM, CQF, BAT, other Finance Exams. We also provide consultancy on MATALB, R, VBA, SQL, Python, MongoDB, SAS.

Sub topics in Excel VBA Bootcamp:
Basics of Excel: Vlookups, Hlookups, Index Match, Dependents, Data tables (1 way and 2 ways), Pivots, Charting, Filters, SQL integration, VBA coding, Address and indirect, Offset, Array functions, etc.
Applications: Regression, Histograms, Monte carlo simulation, rank correlation, dashboards, more.
Automation using VBA: Loops (for, do while, case), Recorder, Arrays and Matrices, if else, indexing, etc.

Monday, August 27, 2018

SQL Installation MySQL Workbench

----Start of MYSQL---
#switch to main user
su - joshi
sudo apt-get update
cd
sudo apt-get install mysql-server

sudo mysql_secure_installation




----Installing Workbech---

sudo mysql

SQL> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
$sudo apt update && sudo apt upgrade
$sudo apt install mysql-workbench
$sudo mysql-workbench


----------------------------------

To run anytime

$sudo mysql-workbench

Saturday, July 21, 2018

Python Data Science 101 Bootcamp (Beginners and Non Programmers) 6 hrs PAID $65 Python Data Science Machine Learning Bootcamp NYC

 New York Python SQL Bootcamp Coding Classes (Affordable & Cost-effective Machine Learning). Best Free classes in NYC. SQL 101 & Python 101 Classes. Big Data Science Classes for beginners interested in Analytics & Data Science. Weekend part time and full time classes in Manhattan & Queens. 1 on 1 Tutoring also available. Free weekend 2hrs class.

https://www.meetup.com/New-York-Python-SQL-Bootcamp-Data-Science-Analytics/events/251782354/

Python Data Science Machine Learning Bootcamp NYC

The course is developed for non programmers and non stat audience.
It consist of games, graphics, and examples to sensitize you to the terms used in Data Science.

Check out our PPT and Jupyter Notebook for 1st Session:

https://notebooks.azure.com/shivgan3/libraries/PythonClassesNYCBootcamp

https://docs.google.com/presentation/d/1LmBC6uq2iZPDSnqjdaZqILkDJjl4SB-97ARgPFEejHE/edit?usp=sharing

Group size is 5.

This course is prerequisite for Part 2.

Part 1 / 2
Two day intensive boot camp for Python Data Science Enthusiast.

Topics:
Introduction to Python
Foundations of programming: Python built-in Data types
Concept of mutability and theory of different Data structures
Control flow statements: If, Elif and Else
Definite and Indefinite loops: For and While loops
Writing user-defined functions in Python
Classes in Python
Read and write Text and CSV files with python
List comprehensions and Lambda
How to start using Python
Parsing information with Python
Practice Python to solve the real-world tasks

Skills that you will GAIN while working on the course are:

Python Programming Language
Statistical Hypothesis Testing
IPython
Hypothesis-testing
Matplotlib
Numpy
Pandas
Scipy
Python Lambdas
Python Regular Expressions

Collection of powerful, open-source, tools needed to analyze data and to conduct data science. Specifically, you’ll learn how to use:

python
jupyter anaconda notebooks
pandas
numpy
matplotlib
git
and many other tools.

We’ll cover the machine learning and data mining techniques are used for in a simple example in Python:

Regression analysis
K-Means Clustering
Principal Component Analysis
Train/Test and cross validation
Bayesian Methods
Decision Trees and Random Forests
Multivariate Regression
Multi-Level Models
Support Vector Machines
Reinforcement Learning
Collaborative Filtering
K-Nearest Neighbor
Bias/Variance Tradeoff
Ensemble Learning
Experimental Design and A/B Tests

Joshi

Thursday, June 14, 2018

Advanced sQL

https://staff.brighton.ac.uk/is/Published%20Documents/Excel%20Formulae%20and%20Functions%20(PC)%20QRC.pdf

Takeaways
The workshop will include the following topics:
Build SQL Database in the Cloud (using Amazon Web Services)
Import and Export of Data
Create and Manage Users
Advanced Querying Techniques (e.g., case statements, extract, union)

Python advanced course

 Object oriented techniques.
- Iterators, Generators and Closures.
- Working with Threads.
- Exception handling.

Tuesday, June 12, 2018

Big Data hadoop part 1


Python Map reduce program:

 http://www.science.smith.edu/dftwiki/index.php/Hadoop_Tutorial_1_--_Running_WordCount

import java.io.IOException;
import org.apache.hadoop.io.IntWritable; import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.Text; import org.apache.hadoop.mapreduce.Mapper;
public class MaxTemperatureMapper  extends Mapper<LongWritable, Text, Text, IntWritable> {
  private static final int MISSING = 9999;    @Override  public void map(LongWritable key, Text value, Context context)      throws IOException, InterruptedException {        String line = value.toString();    String year = line.substring(15, 19);    int airTemperature;    if (line.charAt(87) == '+') { // parseInt doesn't like leading plus signs      airTemperature = Integer.parseInt(line.substring(88, 92));    } else {      airTemperature = Integer.parseInt(line.substring(87, 92));    }    String quality = line.substring(92, 93);    if (airTemperature != MISSING && quality.matches("[01459]")) {      context.write(new Text(year), new IntWritable(airTemperature));    }  } }

Monday, May 21, 2018

SQL Commands

GRANT SELECT ON OBJECT::dbo.Table1 TO Kalyan;
GRANT INSERT ON OBJECT::dbo.Table1 TO Kalyan;
GRANT UPDATE ON OBJECT::dbo.Table1 TO Kalyan;
GRANT DELETE ON OBJECT::dbo.Table1 TO Kalyan;


Wednesday, April 18, 2018

Advanced Topics in Python

New York Python SQL Bootcamp Coding Classes (Affordable & Cost-effective Machine Learning). Best Free classes in NYC. SQL 101 & Python 101 Classes. Big Data Science Classes for beginners interested in Analytics & Data Science. Weekend part time and full time classes in Manhattan & Queens. 1 on 1 Tutoring also available. Free weekend 2hrs class. Small group courses (2-3 attendees), free takes and 1 on 1 : Python 101, Python Data Science Immersive Python for Data Analytics. VBA Macros Immersive. SQL 1 day Class.Project and Portfolio Oriented on weekends and also free evening classes in NYC. Upload your portfolio to get better job. Best Python Class in NYC. FREE RETAKES.

Advanced Topics in Python

Topics for Advanced Python usage:
Design Pattern - Using decorators, constructors, classes and data structures in Python
Using Flask framework in the same way as React using the same folder config and other settings. In place of JS we will use Python
Functional Programming in Python and passing on functions in a function. More list comprehensions.


__init__

single underscore vs double underscore


Python Generators and Iterator Protocol
Python Meta-programming
Python Descriptors
Python Decorators (class and method based)
Python Buffering Protocol
Python Comprehensions
Python GIL and multiprocessing and multithreading
Python WSGI protocol
Python Context Managers
Python Design Patterns


Advanced topics in python are:

System Programming (pipes, threads, forks etc.)
Graph Theory (pygraph, Networkx etc)
Polynomial manipulation using python
Linguistics (FSM, Turing manchines etc)
Numerical Computations with Python
Creating Musical Scores With Python
Databases with Python
Python Generators and Iterator Protocol
Python Meta-programming
Python Descriptors
Python Decorators (class and method based)
Python Buffering Protocol
Python Comprehensions
Python GIL and multiprocessing and multi-threading
Python WSGI protocol
Python Context Managers
Python Design Patterns

Third party libraries aside here are some:
metaclasses
writing decorators, generators, iterators
writing context managers
C/c++ extensions
Multiprocessing

  • Understand the python object model (at least a passing understanding of metaclasses, slots, and descriptors, as well as how inheritance works), bonus points for recent additions like __prepare__ and __init_subclass__, but also simpler things like when __new__ is useful
  • Understand python's ABCs and inferred types (ie. Iterable, Iterator, Generator, etc.)
  • Understand the c-level data model (ie. at a high level how an int, a list, and a dict are laid out in memory), bonus points if they are actually correct about the way a dict works in cpython, but simply understanding how an unoptimized dict would work is fine.
  • Know why a list comprehension is faster than a for loop (which really is to say understand how bytecode is generated, at high level)
  • Advanced unittesting. Mocks, patches, possibly a more advanced library like pytest
  • Working knowledge of recent features (async/await, type hints)
  • A decent knowledge of the important parts of the standard library: math, itertools, functools, random, collections, logging, sys, os, and threading/multiprocessing/asyncio (I realize these aren't the same, but still). That is, I'd expect a senior dev to know what contextlib.contextmanager, functools.wraps, and itertools.chain were, and when/why one might want to use them. No need to know every function, but where to look at least.
  • A decent knowledge of some non-standard library modules in the domain. This would highly depend on the field, but scipy stack, django/flask/sqla/jinja2, etc.
  • Know at least one sane way to manage environments. This could be a bare venv, or it could be a docker based solution, or a combination, or pipenv, but something
Coroutines (not just generators)
Decorators
Advanced class construction and topics
C/Cython extensions
Data structures
Ability to debug and profile code
Tests


Table of Contents of the book:  Advanced Python 3 Programming Techniques By Mark Summerfield

Section 1: What This Short Cut Covers 3
Section 2: Branching Using Dictionaries 4
Section 3: Generator Expressions and Functions 5
Section 4: Dynamic Code Execution 9
Section 5: Local and Recursive Functions 16
Section 6: Function and Method Decorators 21
Section 7: Function Annotations 25
Section 8: Controlling Attribute Access 27
Section 9: Functors 31
Section 10: Context Managers 33
Section 11: Descriptors 37
Section 12: Class Decorators 42
Section 13: Abstract Base Classes 45
Section 14: Multiple Inheritance 52
Section 15: Metaclasses 54
Section 16: Functional-Style Programming 59
Section 17: Descriptors with Class Decorators 63
Section 18: About the Author 65

http://buildingskills.itmaybeahack.com/book/python-2.6/html/p03/p03c02_adv_class.html
https://python.swaroopch.com/oop.html
www.shahmoradi.org/ECL2017S/lecture/11-python-advanced-decorator-class
 https://www.reddit.com/r/Python/comments/6wl0qk/what_are_the_top_10_key_featuresadvanced_topics/?st=jg5fbksp&sh=b1e49398
http://python-3-patterns-idioms-test.readthedocs.io/en/latest/Metaprogramming.html
https://jakevdp.github.io/blog/2012/12/01/a-primer-on-python-metaclasses/
http://blog.thedigitalcatonline.com/blog/2014/10/14/decorators-and-metaclasses/

SQL security

--create login that uses windows
--authentication and is associated
--with a windows security group
CREATE login [TC\TP_Doctors] FROM windows
--access views to verify that
--the login has been created
SELECT * FROM   sys.server_principals
create a login for a specific windows user
CREATE login [TC\md1] FROM   windows
       --create database users and database roles
       --first activate the database
USE touropharmacy
--find out who is connected now
SELECT Suser_name()
       --set up a user associated with
windows authenticated group login
CREATE USER [TPDoctors] FOR login [TC\TP_Doctors]
       --set up a user associated with
windows authenticated user login
CREATE USER [MD1] FOR login [TC\md1]
       --execute as user = 'MD1'
       --create a server role
USE mastergo
--create server role
CREATE server role [dbOnlyCreator]
--view the types of permissions available on the server level
SELECT *FROM   sys.Fn_builtin_permissions('SERVER')
--view the permissions granted to dbcreator
EXEC Sp_srvrolepermission   @srvrolename = 'dbcreator'
  --assign a server level permission to a login
GRANT CREATE any DATABASE TO dbonlycreator
to view the explicit permissions granted to a server loginSELECT     *
FROM       sys.server_principals PR
INNER JOIN sys.server_permissions PER
ON         PR.principal_id = per.grantee_principal_id
USE touropharmacy
           --create a database role
CREATE role doctorrole
--assign database level permission to doctor role
SELECT * FROM   sys.Fn_builtin_permissions('DATABASE')GRANT
SELECT to doctorrole

       --assign schema level permission to doctor role
DENY SELECT ON SCHEMA::sales TO doctorrole
       --assign table level permission
DENY SELECT ON hr.job TO doctorrole
       --assign object level permission
DENY SELECT ON object::hr.physician(dr_licenseid) TO doctorrole
       --add doctor user as a member of DoctorRole
ALTER role doctorrole ADD member tpdoctors




use [AdventureWorks2014]
GO
DENY SELECT ON [Production].[ScrapReason] ([ModifiedDate]) TO [productionofficer.awuser]
GO
use [AdventureWorks2014]
GO
GRANT SELECT ON [Production].[ScrapReason] ([Name]) TO [productionofficer.awuser]
GO
use [AdventureWorks2014]
GO
DENY SELECT ON [Production].[ScrapReason] ([ScrapReasonID]) TO [productionofficer.awuser]
GO


-- list permissions of all users
SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
  ,dbp.permission_name as 'PermissionName'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.type = 'R'

crreate login customerlogin WITH passwrod ="xxx"EXEC sys.Pp_addlogin
  @logname = 'cuistomerlogn'GRANT
CREATE TABLE TO developer_roleDENY
SELECT
ON SCHEMA::humanresourec TO developer_roleGRANT
SELECT
ON prodcution.priciton TO awscusetem_roleDENY
SELECT
ON object::pridction.parent (stanardarcost) TO awcustomerrole exce sp_serverloleperssmion@sernerolname =
'dbcreator'







Thursday, March 15, 2018

Ubuntu play

54.218.49.38

http://forums.fast.ai/t/run-jupyter-notebook-on-system-boot/749/4


http://dlcdnet.asus.com/pub/ASUS/ZenFone/ZX551ML/UL-Z00A-JP-4.21.40.209-user.zip?_ga=2.105212225.318671865.1522430755-1791646875.1522430755

Friday, March 2, 2018

ico






https://github.com/JincorTech/backend-ico-dashboard

https://jincortech.github.io/backend-ico-dashboard/#initiate-password-change-post

docker-compose exec ico systemctl status mongod

https://www.digitalocean.com/community/tutorials/how-to-install-mongodb-on-ubuntu-16-04

https://docs.mongodb.com/tutorials/connect-to-mongodb-shell/


https://docs.docker.com/samples/library/mongo/#connect-to-it-from-an-application

Thursday, March 1, 2018

Backend Developer

Backend Developer with Web development and http, tcp, web-sockets
Modern NoSQL datastores like MongoDB, Redis
Web/mobile application development using Python
Python web framework Worked with MongoDB
Containers or VM like Docker, Kubernetes, Vagrant
Ansible and Jenkins
AWS products: For Compute, Storage, Database or Networking sections
Solr or ElasticSearch
Product startup which have ranked within top 30 in Play Store or iOS App Store



https://blog.codeship.com/using-docker-compose-for-nodejs-development/
https://sub.watchmecode.net/guides/build-node-apps-in-docker/
https://blog.docker.com/2016/07/live-debugging-docker/
https://medium.com/@creynders/debugging-node-apps-in-docker-containers-through-webstorm-ae3f8efe554d

Wednesday, January 31, 2018

Bitcoin Blockchain



Nice Videos:


https://www.youtube.com/watch?v=pLJQy0B5OKo

Tuesday, December 26, 2017

Deep Learning Skills / Data Science

Check out my Data Science Bootcamp on: http://www.qcfinance.in/python-for-data-science-machine-learning/

PDF of Pricing and Outline: http://qcfinance.in/wp-content/uploads/2018/06/Data-Science-Course-Curriculum-v3.pdf

Programming languages (Python, R, Lua, Scala …) and multiple frameworks and technologies (Tensorflow, Torch, Hadoop, Spark, RDBMS…) to support the modeling requirements


Deep learning, other AI, natural language processing, data mining, information theory, and optimization


Python, R, Lua, Scala, C++


Major deep learning libraries:. TensorFlow, Torch, DeepLearning4J


GPU (CUDA), ASIC, or FPGA


Distributed system (e.g. Spark, Hadoop, Ignite …)


Big data visualization

Substantial programming experience with almost all of the following: SAS (STAT, macros, EM), R, H2O, Python, SPARK, SQL, other Hadoop. Exposure to GitHub.
Modeling techniques such as linear regression, logistic regression, survival analysis, GLM, tree models (Random Forests and GBM), cluster analysis, principal components, feature creation, and validation. Strong expertise in regularization techniques (Ridge, Lasso, elastic nets), variable selection techniques, feature creation (transformation, binning, high level categorical reduction, etc.) and validation (hold-outs, CV, bootstrap).
Database systems (Oracle, Hadoop, etc.), ETL/data lineage software (Informatica, Talend, AbInitio)

Data visualization (e.g. R Shiny, Spotfire, Tableau)

AWS ecosystem: experience with S3, EC2, EMR, Lambda, Redshift

Data pipelines  Airflow, Luigi, Talend, or AWS Data Pipeline

APIs:  Google, YouTube, Facebook, Twitter, or Oauth

version control (Github, Stash etc.)

http://qcfinance.in/Data%20Science%20Course%20Curriculum%20(1).pdf


Sunday, December 24, 2017

MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training New York - VBA code for loops play

MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training New York

MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training

Details
# About This Meetup:
Have you ever felt like you are limited to do your calculation in excel cells? Do you want to harness the power of Excel Visual Basic Applications(VBA)? If this sounds like you, this course is for you. This meetup is for Excel beginners and non-programmers and is all about Excel VBA.

# Key Takeaways:
* Referencing
* Shortcuts
* Dragging.
* If command
* Locking
* Sum-Product
* Vlookup
* Hlookup
* Index Match
* Offset
* Find Dependable
* Complex example of mixing all commands
* Matrix multiplication.
* Array Formulas
* Handling big files.

Pre-requisites:
Please bring your own laptop.

About the Speaker:
Shivgan Joshi
Lead Instructor
# 929 256 5046

** Payment Policy: We only accept payment on site and before the class. We accept payment through cash, Venmo & Paypal(+5). **

If you have already attended session 101, take a look at what we offer in session 102.
* Correlation,
* Regression
* Linear modeling, Goal seek,
* Optimization,
* Internal Rate of Return,
* Linest command,
* Graphing,
* Conditional formatting,
* Pivot tables
* Monte Carlo Intro,
* Min, Max, Bin
* Histograms
* Rank,
* Spearman correlation,
* Frequency,
* Error Handling,
* CountIFs,
* ISSomething,
* Cal modes
* Data Tables: Sensitivity, 2 way, and 3-way data table,
* Vlookups with data tables,
* Monte Carlo using Data tables.






'Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub



Sub Sample2()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
   
    Cells(i, 20) = i
     Cells(j, 21) = j
      Cells(k, 22) = k
      Cells(l, 23) = l
      lastrow = lastrow + 1
      CountComb = CountComb + 1
   
      Cells(lastrow, 24) = lastrow
      Cells(CountComb, 25) = CountComb
   
    Next: Next
    Next: Next

    Application.ScreenUpdating = True
End Sub

Sub Sample3()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4
    For j = 1 To 4
    For k = 1 To 8
    For l = 1 To 12
   
    Cells(i, 20) = i
     Cells(j, 21) = j
      Cells(k, 22) = k
      Cells(l, 23) = l
      lastrow = lastrow + 1
      CountComb = CountComb + 1
   
      Cells(lastrow, 24) = lastrow
      Cells(CountComb, 25) = CountComb
   
    Next l
    Next k
    Next j
    Next i

    Application.ScreenUpdating = True
End Sub
Sub playarray()


Dim myThirdColumn As Variant

myThirdColumn = Application.Index(myArray, , 3)



End Sub

' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/

Sub Test()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:E10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, , 2)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
MsgBox UBound(varTemp) - LBound(varTemp) + 1
    'MsgBox varArray(1, 1)

End Sub


Sub Test2()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:Z10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, 3)
    varTemp2 = Application.Index(varArray, , 3)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
'MsgBox UBound(varTemp) - LBound(varTemp) + 1
'MsgBox varArray(1, 1)
'MsgBox UBound(varTemp2) - LBound(varTemp2) + 1
MsgBox varTemp2(10, 1)
' VBA Array starts at 1



End Sub



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Test3()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:Z10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, Array(1, 2))
 
    'first two row elements
    'varTemp2 = Application.Index(varArray, , 3)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
 
 
'  MsgBox Array(1, 2)(0)
 MsgBox varTemp(1)
 ' the first element actually using array command
 ' the above var temp starts with 1 and not with 0
'MsgBox UBound(varTemp) - LBound(varTemp) + 1
'MsgBox varArray(1, 1)
'MsgBox UBound(varTemp2) - LBound(varTemp2) + 1
'MsgBox varTemp2(10, 1)
' VBA Array starts at 1



End Sub