Archive

Archive for May, 2011

Scheduled Import a online csv file into Workbook

May 27th, 2011 admin No comments

For a client I needed a solution for a scheduled import of a online csv file into a workbook. There are many ways to download files from the internet but it needed to be in excel as there were a whole lot of macro’s allready in place to transform and process the downloaded data. They had a machine running at all time ( not a server ) to be used.
So this is the code I came up with. It runs every day after it is started by running the StartSchedule Procedure

Option Explicit

Public tTime As Date

Sub StartSchedule()

    tTime = Now()
    Schedule

End Sub

Sub Schedule()

    tTime = tTime + TimeSerial(23, 59, 59)
    Application.OnTime tTime, "ImportCSV"

End Sub

Sub ImportCSV()

    Dim tmpSheet As Worksheet

    Set tmpSheet = ThisWorkbook.Sheets.Add(,,, "http://www.dummy.com/temp/file.csv")

    tmpSheet.Cells.EntireColumn.AutoFit
    tmpSheet.Name = tmpSheet.Name & " " & Format(Now(), "yyyy-mm-dd hh-mm-ss")

    Schedule

End Sub
 
Categories: CSV, Excel, VBA Tags: , ,

New Plugin to display the sourcecode

May 19th, 2011 admin No comments

Just downloaded and installed the SyntaxHighlighter Evolved Plugin on my blog to display the sourcecode here in a more readable way.

regards

Categories: Uncategorized Tags:

Importing varbinary fields into Excel

May 18th, 2011 admin No comments

I needed to compare two large datasets and find out if they contained the same records.
So I amended the records with a HashByte at the end so I could check if they existed in the other one.
First I imported both queries in Excel with the idea to do a lookup on them.
But a varbinary field is not imported in excel and it will return no error when ommitting a field,
except when the only field is a varbinary field then Excel willl tell you:

No columns that Microsoft Excel can use were returned from this query.

So I had to go back to the Management Studio and join the two sets which was a pain because they were on differend servers.
So much pain I kept trying to find a Excel solution.
All I had to do was convert the varbinary field by making it a parameter in a function which would return a normal varchar.

So

SELECT [field] FROM [table]

had tot change to

SELECT master.dbo.fn_varbintohexstr([field]) AS [field] FROM [table]

 

enjoy

Categories: Excel, SQL Tags: ,

Finally up to date….!

May 18th, 2011 admin No comments

Finally I put back all my post in chronological order. I have a bucket full of ideas for post mainly on PowerPivot, Excel, VBA, SQL Server etc. etc. Probally they will also be posted on the excelusergroup.org and maybe on some forums because they are not always finalized ideas.

I’m aiming for some feedback and comments….

glad to be back

Jelle-Jeroen

Categories: Uncategorized Tags:

Checking if a range is sorted with a formula

May 18th, 2011 admin No comments

January 7th, 2011

To test if range B2:B8 is sorted use {=AND(AND(B2:B7<=B3:B8)<>AND(B2:B7>=B3:B8))} in Excel
To enter a array formula ( and get the brackets ) you need to put the formula in without the brackets and press Control+Alt+Enter

thnx to Chandoo.org

Categories: Excel, Formulas Tags: ,

Slicers and Filters in Excel 2010 Pivottables

May 18th, 2011 admin No comments

May 28th, 2010

I’m not sure if what I experienced is a bug or a feature….
I created a pivottable in Excel 2010 with slicers and made a selection via the slicers.
So far so good but when I wanted to see the records for a specific datapoint and double click on this point it will give me the complete recordset not taking into account the slicer settings.
But when I put in pivottablefilters for the same fields as the slicers it all works like good old pivottables doubleclicking.

Does anybody know if this is a feature or a bug….?

Risk Management is a game without end

May 18th, 2011 admin No comments

March 16th, 2010

Three actuaries and three accountants are traveling by train to visit a ‘Risk Management Conference’. At the station, the three accountants each buy tickets and watch as the three actuaries buy only a single ticket.

“This looks very risky. How are three people going to travel on only one ticket?”, one of the accountants asks.

“Watch and you’ll see! Take notice of our brand new risk management approach”, one of the actuaries answers.

They all board the train. The accountants take their respective first class seats, but all three actuaries cram into a restroom and close the door behind them.
Shortly after the train has departed, the conductor comes around collecting tickets. He knocks on the restroom door and says, “Ticket, please.” The door opens just a crack and a single arm emerges with a ticket in hand. The conductor takes it and moves on.

The accountants were deeply impressed by the actuarial approach and agreed it was – after all – quite a clever idea without any substantial risk.

So, completely confident and with even more Risk Management skills gained at the inspiring Conference, the accountants decide to copy the actuaries new risk approach on the return trip and save some money (accountants have always been clever with money!). When they get to the station they buy a single ticket for the return trip.

To their astonishment, this time the actuaries don’t buy a ticket at all. “This is reckless, how are you going to travel without a single ticket?”, one of the perplexed accountants asked. “Watch and you’ll see! Take full notice of our latest risk management approach” answered an actuary.

When they board the train the three accountants cram into a restroom and the three actuaries cram into another one nearby. The train departs.

Shortly afterward, one of the actuaries leaves his restroom and walks over to the restroom where the accountants are hiding. He knocks on the door and says, “Ticket, please.”

Conclusion:

What conclusions can we draw from this simple story?

Risk Management is a game without end

The effect of Risk Management Conferences is threefold:

Some attendants get smarter
Others get overconfident
Final result: Increasing Risk, instead of decreasing Risk

There’s an old Dutch saying that expresses the danger of increased Risk Management :

“A warned man counts for two”

If we want to reap the fruits of Risk Management, accountants and actuaries have to start working together, instead of struggling and competing each other.

Risk Manager Profile and qualifications
Insight, creativity and integrity are important requirements to become a professional Risk Manager. Unfortunately, this is not enough.

To tackle Risk Management in a company, you need the best potential crook around. One who’s willing to settle his salary and earnings for a little less than he would have earned as a real crook, in return for having a respectable job and not risking to end up in jail. You could call it the Personal Risk management of the Risk manager. Employers that settle for an inferior Risk Manager, know one thing for sure: someday somebody more ‘crooky’ than ‘your risk manager’ will tear your company down!

Categories: Financial Tags:

VBA function for returning Column names, type as a dictionary

May 18th, 2011 admin No comments

February 16th, 2010

I needed the column names of a sql server 2008 table.
So I wrote a function who would return a dictionary with the column names as the key and the type as the item.

‘Set Reference to Microsoft Scripting Runtime
‘Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Function ColumnNames(sConnection As String, sTable As String) As Dictionary

   Dim cConnection As ADODB.Connection
   Dim rsRecordset As ADODB.Recordset
   Dim sSQL As String
   Dim dColumnNames As Dictionary

   sSQL = sSQL & “SELECT sc.name AS column_name, st.name AS datatype ”
   sSQL = sSQL & “FROM sysobjects so ”
   sSQL = sSQL & “JOIN syscolumns sc ON so.id = sc.id ”
   sSQL = sSQL & “JOIN systypes st ON sc.xtype = st.xtype ”
   sSQL = sSQL & “WHERE so.xtype = ‘U’ ”
   sSQL = sSQL & “AND st.name <> ’sysname’ ”
   sSQL = sSQL & “AND so.name = ‘” & sTable & “‘”

   Set dColumnNames = New Dictionary

   Set cConnection = New ADODB.Connection

   cConnection.Open sConnection
   Set rsRecordset = New ADODB.Recordset
   Set rsRecordset.ActiveConnection = cConnection

   rsRecordset.Open sSQL

   Do While Not rsRecordset.EOF And Not rsRecordset.BOF

      dColumnNames.Add rsRecordset.Fields(0).Value, rsRecordset.Fields(1).Value
      rsRecordset.MoveNext

   Loop

   Set rsRecordset = Nothing
   cConnection.Close
   Set cConnection = Nothing

   Set ColumnNames = dColumnNames

End Function

 
Source file: M_ColumnNames

Categories: Excel, VBA Tags: ,

VLOOKUP returning multiple columns

May 18th, 2011 admin No comments

February 8th, 2010

I use this method for a long time now a just reccently discovered nobody else does.

I’ve got 2 tables, lets call them tbMaster ans tbSlave. Most people use a new vlookup for every column they want to retrieve from tbSlave. What I usually do is use vlookup as an array formula.

For example if I want to get the 4th, 7th and 9th column from tbSlave I select 3 columns in tbMaster and type

=VLOOKUP(tbMaster[[#This Row],[Name]],tbSlave,{4,7,9},0) followed by Ctrl Shift Enter

Besides the normal vlookup disadvantages in my opinion the array solution is better

 

Categories: Uncategorized Tags:

The risks of stupidity

May 18th, 2011 admin No comments

October 8th, 2009

A general once made a practical observation on the risks of stupidity.

I divide my officers into four classes; the clever, the lazy, the industrious, and the stupid.

Each officer possesses at least two of these qualities.

Those who are clever and industrious are fitted for the highest staff appointments.

Use can be made of those who are stupid and lazy.

The man who is clever and lazy however is for the very highest command; he has the temperament and nerves to deal with all situations.

But whoever is stupid and industrious is a menace and must be removed immediately!

 

 

Categories: Uncategorized Tags: