Excel, BI and more

Importar datos de Cuentas bancarias en Excel

Es este post os expongo la solución a la típica cuestión de importar datos de cuentas bancarias a Excel para tenerlas actualizadas día a día y poder así hacer conciliaciones y otro tipo de cálculo.

Antes de nada, un aviso importante la contraseña y el usuario están visibles en el código, asi que acordaros de proteger el libro como es debido y darle acceso sólo a la gente de vuestra confianza.

Por supuesto hay que cambiar cosas en el código (usuarios, contraseña, url del banco, los nombre de los formularios…). Para la parte delos formularios podeis utilizar Firebug, un add-inn muy bueno para el Firefox.

El código que muestro a continuación lo podeis encontrar en Mr. Excel.

'Need reference to Microsoft HTML Object Library.  Select this in Tools - References in VB editor.

Option Explicit

Public Enum IE_READYSTATE
    Uninitialised = 0
    Loading = 1
    Loaded = 2
    Interactive = 3
    complete = 4
End Enum

Sub Test()

    Const cURL = "https://pronto.firsthorizonwholesale.com/Index.asp"
    Const cUserID = "XXXX"     'REPLACE XXXX WITH YOUR USER ID
    Const cPwd = "YYYY"        'REPLACE YYYY WITH YOUR PASSWORD

    Dim ie As Object
    Dim doc As HTMLDocument
    Dim PageForm As HTMLFormElement
    Dim UserIdBox As HTMLInputElement
    Dim PasswordBox As HTMLInputElement
    Dim FormButton As HTMLInputButtonElement
    Dim Elem As IHTMLElement

    Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = True
    ie.navigate cURL

    'Wait for initial page to load

    Do While ie.busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop

    Set doc = ie.document

    'Output HTML tags to debug window

    Debug.Print "Login page: " & ie.LocationURL
    For Each Elem In doc.all

        'Debug.Print Elem.tagName

    Next

    'Get the only form on the page

    Set PageForm = doc.forms(0)

    'Get the User Id textbox
    '< input class="TextBox" maxlength="15" name="UserName" size="12">

    Set UserIdBox = PageForm.elements("UserName")

    'Set the User Id

    UserIdBox.Value = cUserID

    'Get the password textbox
    '< input class="TextBox" type="password" maxlength="10" name="Password" size="12">

    Set PasswordBox = PageForm.elements("Password")

    'Set the password

    PasswordBox.Value = cPwd

    'Submit the form (like clicking the 'Submit' button) to navigate to next page

    PageForm.submit

    'Wait for the new page to load

    Do While ie.busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop

    'Get the HTML document of the new page

    Set doc = ie.document

    'Output HTML tags to debug window to prove this is the new page

    Debug.Print "Terms of Use page: " & ie.LocationURL
    For Each Elem In doc.all
        'Debug.Print Elem.tagName
    Next

    'The new page contains 'Terms of Use' conditions and an 'Accept' button within a form
    'Get the only form on the page

    Set PageForm = doc.forms(0)

    'Get the form submit button and click it to navigate to next page
    '< input type="submit" value="Accept" name="selection">
    'Note: unlike the login page, can't use PageForm.submit to submit this form because it doesn't have
    'a method="post" attribute

    Set FormButton = PageForm.elements("selection")
    FormButton.Click

    'Wait for the new page to load

    Do While ie.busy Or Not ie.readyState = IE_READYSTATE.complete: DoEvents: Loop

    'Get the HTML document of the new page

    Set doc = ie.document

    'Output HTML tags to debug window to prove this is the new page

    Debug.Print "Main Pronto page: " & ie.LocationURL
    For Each Elem In doc.all
        'Debug.Print Elem.tagName
    Next

End Sub

Tags: VBA

MDX Cheat-Sheet

I was looking for some MDX functions to get somo work done and I found this web page. It seems to be a little bit old but it has a good summary of the functions and some example code.

It might help

([Time].CurrentMember, [Measures].[Sales])
- ([Time].CurrentMember.PrevMember, [Measures].[Sales])

([Time].CurrentMember, [Measures].[Sales])
- (ParellelPeriod(Year, 1, [Time].CurrentMember), [Measures].[Sales])

sum(ytd([Time].CurrentMember), [Measures].[Sales])

sum(PeriodsToDate([Time].[Year], [Time].CurrentMember), [Measures].[Sales])

([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].CurrentMember.Parent, [Measures].[Unit Sales]) * 100

([Product].CurrentMember, [Measures].[Unit Sales])
/ ([Product].[All Products], [Measures].[Unit Sales]) * 100

Sum(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])
/ Count(Descendants([Time].CurrentMember, [Month]))

Avg(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

(Tail(Descendants([Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

(ClosingPeriod([Month], [Time].CurrentMember), [Measures].[Quantity])

Max(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Min(Descendants([Time].CurrentMember, [Month]), [Measures].[Quantity])

Avg([Time].CurrentMember.Lag(2):[Time].CurrentMember, [Measures].[Unit Sales])

Count(Filter(Descendants([Product].CurrentMember, [Product Name])
            , ([Time].CurrentMember, [Measures].[Unit Sales])
              < ([Time].CurrentMember.PrevMember, [Measures].[Unit Sales])))
/
Count(Descendants([Product].CurrentMember, [Product Name]))

Tail(Filter([Time].[Month].Members
     , Not IsEmpty([Time].CurrentMember)), 1).Item(0)

Filter([Store].[Store Name].Members
       , Val([Store].CurrentMember.Properties("Store Sqft")) < 21000)

select
    {{{[Measures].[Products Down]}
      * {[Customers].DefaultMember}
      * {[Product].DefaultMember}}} on columns
    , {[Time].&[1998].&[Q1].&[1]
       , [Time].&[1998].&[Q1].&[2]
       , [Time].&[1998].&[Q1].&[3]
       , [Time].&[1998].&[Q2].&[4]
       , [Time].&[1998].&[Q2].&[5]
       , [Time].&[1998].&[Q2].&[6]
       , [Time].&[1998].&[Q3].&[7]
       , [Time].&[1998].&[Q3].&[8]
       , [Time].&[1998].&[Q3].&[9]
       , [Time].&[1998].&[Q4].&[10]
       , [Time].&[1998].&[Q4].&[11]} on rows
from
    [Sales_MDX2]

select
    {[Measures].[Unit Sales]
     , [Measures].[MA]} on columns
    , {Descendants([Time].[Year].&[1997], [Month])} on rows
from
    [Sales_MDX2]

Tags: BI

Cheat sheets

Surfing the web I accidentally found this great web site. You will find very usefull cheat-sheets on various technology sections

Tags: Tech