Author: FinancialRadDeveloper

I am an Python & Excel VBA RAD Developer in Banking. I am trying to share the tips and info I have picked up in the last few years.

Quick Transposer


A post from a Pistonhead member in need :

Hopefully someone can help!

I have a list of approx 50000 numbers in ‘Column A’ of spreadsheet. I need to split this list into batches of 1000, populated into Column B onwards.

Anyone with some VBA skill got any ideas? Thanks in advance!

 
My reply :
 
Sub QuickTranspose()

    Dim rng As Range
    Dim StartRowNum As Long
    Dim EndRowNum As Long
    Dim ColumnOffsetCounter As Long
    Dim GroupSize As Long
    Dim v
    Dim i As Long
    Dim TotalRows As Long
    GroupSize = 1000
    TotalRows = 50000
    
    'Dirty but without range names etc you just need to get the
    'selected cell to be the first one in the list
    Set rng = Application.Selection
    StartRowNum = rng.Rows.Row
    For i = 0 To TotalRows / GroupSize
        EndRowNum = StartRowNum + GroupSize - 1
        v = Range("A" & StartRowNum & ":A" & EndRowNum).Value
        Range(rng.Offset(0, i + 1), rng.Offset(GroupSize - 1, i + 1)).Value = v
        StartRowNum = EndRowNum + 1
    
    Next i
End Sub
0

Triskaidekaphobia! Or in English: Microsoft Excel Team’s fear of 13.


So today when I saw something that look like this it made me smile:

Public Enum ExcelVersionEnum
    Excel4 = 4
    Excel95 = 7
    Excel97 = 8
    Excel2000 = 9
    Excelxp = 10
    Excel2003 = 11
    Excel2007 = 12
    Excel2010 = 14    'Triskaidekaphobia!
End Enum

There is no Excel version 13.  I of course only just found this out when I encountered Excel 2010 which, I blush to admit, I have not downloaded from TechNet yet and played with.

Triskaidekaphobia (from Greek tris meaning “3,” kai meaning “and,” and deka meaning “10”) is fear of the number 13; it is a superstition and related to a specific fear of Friday the 13th, called paraskevidekatriaphobia or friggatriskaidekaphobia.

Source : en.wikipedia.org/wiki/Triskaidekaphobia

Continue reading “Triskaidekaphobia! Or in English: Microsoft Excel Team’s fear of 13.” →

0

G Challenge III – Practice Excel VBA Interview question – Watch face maths


I have heard of this question coming up a little bit in all sorts of technical interviews so I thought I would set it as a G Challenge.

Question:

“Please tell me what you would implement to tell me the angle between then hands on a watch face at any given time of the day.  Please show your working and use these examples.”

6:00, 3:00, (lets make this non trivial now ) 9:15, 1:30 12:50,  3:20.

Answer Style:

It is up to you how you go about answering this, but I would try this in two stages. 

  1. Have a pen and paper and go about it as if you were in an interview, then :
  2. Actually try to write some VBA for a spreadsheet that goes about doing it.  Esp if you can call it from the sheet so your function returns the answer from an Excel Date Time entered in a cell.

Extra Homework:

  • Could you write this as a VB dll, C++ dll, C# XLL etc?
  • Does your function cope with 24hr time format?
  • Can you also output a list of times from an angle?  Ie. what times equate to 90 deg, 27 deg etc? 
    • If so can you output this as an array function so that a list can be shown in Excel?
2

Watch Accuracy Tracker


I had an idea the other day that I would write a small app in Excel to keep track of all my watches.

I was planning to use a constant source like :

http://time.is/

to keep track of the ‘real’ time and then have an ability to take a ‘snapshot’ of what the time is on each watch in a list ( that can be added to of course). This way using the inbuilt goodies of Excel all manner of graphs and stats can be applied to the raw data and people can see how their watch is behaving.

I was also thinking of adding a few features like resting orientation to see if any of the theories about leaving the watch 3 down, 9 down , dial up, dial down do influence the readings. Along with whether the chrono is running, is it being worn during the timing period etc.

Would anyone be interested in a copy of this? If anyone has any ideas / suggestions that would be great.

I am also thinking of using this as a quide too :

http://rcm-uk.amazon.co.uk/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=finraddev-21&o=2&p=8&l=as4&m=amazon&f=ifr&asins=B004BLIW22

1

Essential tools for Excel VBA RAD (Rapid Application Development)


I guess a major point of RAD is speed.  The clue is right there in the name: Rapid Application Development.

So, all other things being equal, how do we go about getting more speed.  I don’t mean smashing out a load of garbage or not testing things. 🙂

I mean what things can be used to help productivity?  I am going to list a few here.

I will keep adding to this list, but if anyone knows of any good sources please add a comment and I will add it in.

1

Do you use protection? Really useful factoid – 002


We are all told that using protection is a good thing.  This applies to both real life and also programming, both in full OOP and the quasi OOP world of VBA / VB. It can be possible in some cases for your variables to be changed inside the sub / function you have called, even the ones one may have designated in your human mind as ‘input’ variables.   This can lead to odd behaviour and hard to find bugs.

So.. what am I going  on about?  Well I am talking about passing by value or by reference.  (Experienced VBA Programmers may want to skip this bit).   In VBA you can use two keywords ByRef or ByVal when defining your Sub or Function.   Continue reading “Do you use protection? Really useful factoid – 002” →

3

G Challenge II – Roman Numerals


Ok, in quick sucession here is another G Challenge.  I think this one might be a bit of a stinker though!

II  (2) ;p   Functions that can be called from Excel to convert a number to its Roman equivalent, and also back from Roman to Normal.

So :

2010 -> MMX

Too easy… ?

231241 -> ccxxxMCCXLI

I have yet to attempt this myself as I normally set challenges from something I have had to do in the past I have found tricky.  This came from me choosing to put the challenge number as II not 2, and it popped into my head!

Edit:  I *know* that ROMAN() exists but that only goes up to 3999, and I am interpreting this as ‘classic’ numerals.    I think having looked at it that this is a massive challenge for a learner, but I have posted it now and we can always come back to it.   I’ll need something more sympathetic for GC III

0

G Challenge I


Ok, my friend is trying to learn Excel VBA and get into RAD development.   I am going to set a number of challenges which I am going to call a ‘G Challenge’  😛

So the first.  Take a string in the format :

20101124_170615_dd20101124_EOD.g

where the format is :

yyyymmdd_hhmmss_ddyyyymmdd_EOD.g

In VBA I would like 2 things. 

  1. The string back in the format dd-mmm-yy hh:mm
  2. An actual date serial

Example above would go to 

  1. 24-Nov-2010 17:06:15
  2. 40506.7126736111

Go!   I would use 2 seperate functions to start off with.

Answers will be forthcoming in a post G Answer I

3

New Financial Product Today – PRD & Callable PRD


After all my interviewing ( more on that to come ) I have a new contract. As such, a new bank and a new lot of things to learn. So while watching a training video I cam across a mention of PRD’s and callable PRD’s. I just had to look it up. Google didn’t yield as much, but trusty ol’ Wikipedia did the job as per usual.

Power Reverse Dual – there we go.

http://en.wikipedia.org/wiki/Power_reverse_dual_currency_note

http://giddy.org/sf/articles/structured_notes.pdf

0

Blog at WordPress.com.

Up ↑