Word to PDF

word to pdf
A week ago had a personal task to convert 40 or so word documents to pdf in standard and minimum size. Doing them by one at a time was felt such a waste, so recorded and edited a vba macro to do the same task in a jiffy.

Here’s the simple vba subroutine to do the conversion.

Continue reading

Simple Code to Find Which Excel Row is Being Modified

Excel’s inbuilt functions are powerful. They can achieve a lot. Almost anything can be conquered with these functions.

Well almost……everything!!

Recently needed a function to know which row is being modified. As far as i know, there’s no way this can be achieved by using excel’s function. This is where little bit of vba helps.

Here’s the code that helped.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 3 Or Target.Column = 4) Then
        If Target.Row > 1 Then Cells(1, 6) = Target.Row
    End If
End Sub

In the above sheet change method, the code checks if any row has changed in the columns 3 and 4 and if something has changed beyond row 1 in these columns, the F1 cell is updated with the row number.

Simple and small code but very useful when one wants to know which row is getting modified in excel.

The information about the row being modified can be used to do lot of things using excel in built functions.

Now think where can one use this. Stay tuned I have a follow up post on the use.

Darwin Portfolio – How Charles Darwin Can Help Build a Stock Portfolio

Darwin Portfolio

A post on Charles Darwin by a friend reminded me an approach I am experimenting in terms of cleaning my stock portfolio.

As I have discussed here, I am a Gardener now when it comes to investing. I find selecting and buying lot easier than selling.

For buying I have a system or at least a framework but selling lacks any systematic approach. So while reading the book Seeking Wisdom from Darwin to Charlie Munger by Peter Bevelin,I thought of taking Darwin’s help.

Now every quarter and during annual results, I assess the stocks in my portfolio using 4 variables, ROE, Cash by Net Profit, Debt to Equity and growth of each additional rupee invested.

Gather this data into this Excel sheet and then rank them. Consistent lower ranks are the ones that need to go. So Darwin’s survival of the fittest in action. I also use this sheet to evaluate a potential new stock for the portfolio.

Getting this sheet populated is simple, hard part is acting on the information it reveals. .

Here’s the excel sheet if anyone is interested.

Is There a Dinosaur Sitting on Your Excel File?

Dinosaur Sitting On your excel App
I am handing few big excel data files lately and these files are loaded with vlookups, sumifs,sumproduct and truckload of loopy VBA code.

Running the VBA code was pain, excel drags as if a dinosaur is sitting on it. The files were so muddled that even the excel in-built functions to convert a range of cells to number takes 5 mins.

Trying to optimise the vba application in the excel file, I did the usual Application.ScreenUpdating = False and Application.Calculation = xlManual in the VBA to speed up all macros. This helped but not entirely.

The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates and re-calculates each cell and resolves dependencies. Excel recalculate’s whenever an event occurs (for example a cell is changed, a VBA code calls for re calculation or anything else). For each change the entire calculation process is carried out, so the trick to gain major benefit came from looking at all the formulas and optimising them.

It’s not easy but there are many fantastic resources that can help. Here are some of my favourite ones.

75 Excel Speeding Tips
Its a long List, many repeated but worth a visit.

Optimize VBA
Excellent tips to optimize vba code. You should do this before you move onto anything.

How to speed up calculation and Improve performance of Excel and VBA
These are excellent tips and must visit to know more about the calculations steps followed by Excel.

Pareto Chart in Excel

Pareto Chart in Excel
Creating pareto chart in excel requires multiple steps as there is no direct method to accomplish this in excel. Here’s a vba a script that creates a pareto chart in excel.

How to use the script?
Select the data and run the ParetoChart sub. That’s it. The scripts always assumes the first row is headers. Don’t forget that.

And if you need Boxplot in excel, this is where you should go.

Here’s the code….

Continue reading

Getting User Id of The Person Currently LoggedIn [vba]

Working on a side project in VBA, a few weeks ago, I needed a function to get the userid of the person currently logged in to the system. Here’s the function that came handy! Posting it here for the general audience!

 

Option Compare Text
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long</code>

Public Function NTDomainUserName() As String
' This function gets the user id of the person currently logged in
Dim strBuffer As String * 255
Dim lngBufferLength As Long
Dim lngRet As Long
Dim strTemp As String

lngBufferLength = 255
strBuffer = String(255, 0)
lngRet = GetUserName(strBuffer, lngBufferLength)
strTemp = UCase(Left(strBuffer, lngBufferLength - 1))

If strTemp = "" Then
NTDomainUserName = "Not Known"
Else
NTDomainUserName = strTemp
End If

End Function


' Test function

Sub test_Open()
UserId = NTDomainUserName
Sheets(1).Cells(1, 1) = UserId
End Sub

An Excel App for Your Brain!

Screen Shot 2013-04-29 at 10.24.23 PM
While browsing through google play, this android app got my attention. Having discarded my android phone, I asked my friend N to download the app on his mobile.

Liked the game and then we two went for a walk to stretch our legs and give our eyes break from the computer screen.

It is in this walk that we challenged each other if we can create this simple game in our programming language of choice.

We figured by an hour we can complete the game. I wanted to do it in java but didn’t have the sdk installed on my system, so decided to use my old pal vba. Launched excel and began with my phone timer set to shout in an hour.

By stretching 5 mins more to an hour, I had the basic functionality ready in excel.

Then it took, another one hour to get this game little more polished. Though far from complete.

So here’s the result. Click to download the excel file and play

What I learnt from the exercise?
The application.ontime function in excel 2007 is broken. Was not able to stop it. So had to introduce a new Boolean variable to stop the play.

Working with shapes was a new thing for me. I have always used cells or a user form for working in excel VBA, but for this I wanted to see how to use shapes.

Not bad for a total of 2+ hours of effort and it refreshed my dormant vba skills!!

Lot can be improved in the app, but hey I have real work to do. If you want, you are free to refine the game further. or look at the code here.

Blast From the Past- 4 function keys in excel

excel_animation2I wrote the following post in 2009 at one of my old blog. Yesterday I got a comment on that entry, which led me to that post.

It’s still relevant and I think knowing few shortcuts in excel or any application we regularly use is a good thing. Knowing it lets us focus on the core of our work and make us more efficient.

So here’s full copy of the post.

Whatever you use, be it catia, solidworks, UG, NASTRAN,ANSYS ,PATRAN, HYPERMESH or any other CAE software, excel is one of the software tool that will always cross your way. Maybe for preparing some graphs or presenting the data from the analysis. For my current work, excel is tool that I use everyday in my office. So today I would like to share 4 function key shortcuts of excel that has made my life easy.

F2 the second most used key for me. If you want to edit a cell value. This is the key to use. Instead of double clicking F2 comes handy to edit those long formulae’s.

F7 spell checker. My spellings are bad, even in spread sheet I manage to get many spelling mistakes, so one click on F7 ! I don’t know how I would survive without it.

F4 is the most versatile function key of excel. I use it the most. It repeats your last action. So this can be used for many tasks. Since there is no fix action, so its programmable to use any action. If I have to delete a row, repeatedly. I select the row and then right click on it and choose delete. After than any row I want to delete I just choose that row and press F4. Similarly if I have to clear lot of stuff from one sheet. I use F4 key instead of right click and choosing clear contents multiple times.

F11. Most of the time my work in excel involves making graphs of the data and F11 comes to the rescue. just select the data and press F11. You will have the default graph plotted. Since I want a particular type of graph so I always make a default graph and use it every time I need a graph.

Do you have any shortcut keys then please do share it with me.

Shortcuts

image

We were taught “Never take shorts cuts” but in excel and other software program, shortcuts make you productive.

Here are my two most used shortcuts in Microsoft excel. They save a ton of clicking and touching the mouse.

1. Use F4. It repeats the previous action. Automate any single action instantly.

2. Control+1 quick formatting

What are your most used shortcuts?

Fortran DLL and Excel -a 4 step video tutorial

Ever since I had used the screen-o-matic screen-cast, I was itching to use it again to create something.

Today morning finding some time, I decided to go ahead, but then stumbled on these awesome four step video tutorials on mixing fortran and excel.

This is what I was going to screen-cast, but this videos do an excellent job.

So please enjoy and learn.

Step 1

Step 2

Step 3

Step 4

Coordinates from Excel to GMSH

A colleague of mine is working on a personal project, where he is manipulating some blade coordinates in excel.

He wanted some way to visualize his modifications. After some mulling we selected open source GMSH for viewing the blades.

But this brought a new problem, how to transfer the coordinates from excel to GMSH.

Since he just wanted to visualize the 3d coordinates, I wrote him this simple fortran routine that transfers the excel coordinates stored as comma separated values to GMSH points.

Here’s the fortran code to transfer coordinates from excel to gmsh.

        MODULE gmeshGeowriter

        CHARACTER(len=255) :: inputfile,outputfile
        CHARACTER(len=1) :: leftCurl="{",rightCurl="}",semiColon=";"
        CHARACTER(len=1) :: leftBrac="(",rightBrac=")"
        TYPE points
           DOUBLE PRECISION ::x,y,z
        END TYPE

        CONTAINS
           SUBROUTINE WRITEout()
           TYPE(points) pnt
           INTEGER i

            OPEN(unit=20,file=inputfile,status='old')
            OPEN(unit=30,file=outputfile)

            WRITE(30,'(a)') "lc = 0.0009;"
            i=1
            DO
              READ(20,*,end=20)pnt%x,pnt%y,pnt%z
              WRITE(30,100) "Point"//leftBrac,i,rightBrac//" = "//leftCurl,pnt%x,&
              &",",pnt%y,",",pnt%z,",","lc"//rightCurl//semiColon
              i=i+1
            END DO
  20        CONTINUE
            CLOSE(20)
            CLOSE(30)
  100       format((a6,i4,a5,3(f8.2,a1),a5))
           END SUBROUTINE

        END MODULE

        PROGRAM test
        USE gmeshGeowriter
        INTEGER IARGC

         noarg = IARGC()
         IF(noarg .EQ. 0) THEN
                WRITE(*,'(a)',advance='no')"Enter the input FILE name  : "
                READ(*,'(a)')inputfile
                WRITE(*,'(a)',advance='no')"Enter the output FILE name : "
                READ(*,'(a)')outputfile

             ELSE
                CALL GETARG (1, inputfile)
                CALL GETARG (2, outputfile)
             END IF

        CALL WRITEout()

        END PROGRAM
 
 
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

image

Code can be improved to transfer connect the points as lines and then to surface. Well maybe will do this some other day.

Fortran DLL and Excel – How to mesh them together

Making a fortran DLL and using it with excel has two distinct advantage.

1. We don’t need to re write the fortran code to a vb code.

2. Fortran DLL is significantly faster than the vba code.

And with all this we get goodness of excel with the power of fortran.

It is with this idea, I had created few fortran dll’s over the years and used them with excel in my professional and personal projects.

The graphics and data handling capability coupled with fortran’s numerical engine had served me well.

If you are interested in how to mesh the two, the following posts should be useful to you.

Fortran and Excel – good fit for marriage

How to create fortran DLL with intel compiler using Visual Studio?

How to create fortran DLL with intel compiler without visual studio?

How to create fortran DLL with gfortran open source compiler?

Evolution of BoxPlots

Famous statistician John Tukey (http://en.wikipedia.org/wiki/John_Tukey) created the boxplot in 1970. Boxplot shows a distribution summary in a small amount of space. In this paper [PDF], Hadley Wickham and Lisa Stryjewski look back on boxplot and its evolution up to present.

Boxplots are particularly useful for comparing distributions across groups. I was introduced to them two years back and have been using them ever since.

I think Boxplots are perfect example of what John Tukey believed

Its far better to have an approximate answer to the right question, than the exact answer to the wrong question…

Oh by the way, excel 2003 donot have boxplot by default so I have created a excel VBA code to just produce boxplot in excel. Do check it out!

BoxPlot in Excel

Two years back around this time, while working with matlab, I came across the plot called boxplot.

Liked the idea of. Ever since I wanted to replicate that in one my fav language of choice, fortran.

Well that became part of my work.

While reasearhing boxplot on google, I came across this page. Loved the idea of creating boxplots in excel.

So coded this excel VBA macro that takes in some numbers and creates a boxplot for it.

The video below shows how this works.

Download the excel file and don’t forget to look under the hood.
or for those who don’t want to download, here’s the complete code.

How to create Fortran DLL in Visual Studio with Intel Fortran compiler

The following link is a PowerPoint presentation on creating a Fortran DLL with intel fortran compiler in visual studio!!

Please click the link to view the presentation!

Create Fortran DLL with Visual Studio

Created this to help a friend trying to do the same for his work. Thought it will be nice to have it online for everyone else.

Maybe next post, I will post how to use this DLL with microsoft excel!!  Stay tuned!!

More posts

  1. Fortran 90 with Excel – gfortran example

  2. How to use pgplot in windows with Intel Fortran?

  3. STL file reader in fortran

  4. Boxplot in excel 
  5. Fortran DLL and Excel VBA how to mesh them together?

Fortran Program to Calculate Quartiles as calculated by Minitab and Excel

A part of my work at my day job needed calculating quartiles. I quickly coded a routine to find the required numbers. But when i checked the numbers through excel and then with Minitab. I was astonished, as for the same set of numbers, i was getting different quartiles results in these two software’s.

Which one to trust?
Excel gave a different Q1 and Q3 , while Minitab showed me another. So i googled and found this excellent paper  discussing how different software’s calculate quartiles and why they can and are different..

With this clarification, i ended my day and came home. But knowing about this three methods of calculating quartiles, as soon as i reached home, i quickly code this following program to see how this work.

So here is the Fortran program that will calculate quartile in all the 3 methods describe in that document.

 

program quartile
! Fortran program to test the 3 methods of Quartile Calculation.
! Quartiles are calculated differently in EXCEL, Minitab and SAS method 5
! This program tests the 3 methods.

implicit none
interface
subroutine sasm5(x,quart)
  real*8 :: x(:),quart
end subroutine

subroutine sasm4(x,quart)
  real*8 :: x(:),quart
end subroutine

subroutine excel(x,quart)
  real*8 :: x(:),quart
end subroutine
end interface

real*8 :: a,b,c,x(4),quart
data x/1.0d0,2.0d0,3.0d0,4.0d0/

Print *, "Excel Method"
quart=0.25d0
call excel(x,quart)
print *,’  q1=’,quart

quart=0.500d0
call excel(x,quart)
print *,’  Median=’,quart

quart=0.750d0
call excel(x,quart)
print *,’  q3=’,quart

 

Print *, "Minitab Method"
quart=0.25d0
call sasm4(x,quart)
print *,’  q1=’,quart

quart=0.500d0
call sasm4(x,quart)
print *,’  Median=’,quart

quart=0.750d0
call sasm4(x,quart)
print *,’  q3=’,quart

Print *, "SAS Method 5"
quart=0.25d0
call sasm5(x,quart)
print *,’  q1=’,quart

quart=0.500d0
call sasm5(x,quart)
print *,’  Median=’,quart

quart=0.750d0
call sasm5(x,quart)
print *,’  q3=’,quart

end

subroutine excel(x,quart)
implicit none
! Excel’s method to calculate quartiles.
! Based on discussion in this paper http://www.haiweb.org/medicineprices/manual/quartiles_iTSS.pdf
!

real*8 :: x(:),quart,a,b,c
integer :: n,ib

n=size(x)

a=(n-1)*quart
call getgp(a,b,c)

ib=int(c)
!print *,n,a,b,c,ib

quart= (1-b)*x(ib+1) +b*x(ib+2)

end

subroutine sasm4(x,quart)
implicit none
! Minitab’s method of calculating is the same as the SAS Method 4.
! Based on discussion in this paper http://www.haiweb.org/medicineprices/manual/quartiles_iTSS.pdf
!

real*8 :: x(:),quart,a,b,c
integer :: n,ib

n=size(x)

a=(n+1)*quart
call getgp(a,b,c)

ib=int(c)
!print *,n,a,b,c,ib

if((ib+1)>n) then
quart=(1-b)*x(ib) +b*x(n)
else
quart=(1-b)*x(ib) +b*x(ib+1)
end if

end

subroutine sasm5(x,quart)
! Calculate Quartiles using SAS Method 5
! This method is the default method of SAS and is based on the empirical distribution function.
! Based on discussion in this paper http://www.haiweb.org/medicineprices/manual/quartiles_iTSS.pdf
!
implicit none
real*8 :: x(:),quart,a,b,c,tol,diff
integer :: n,ib

tol=1.e-8
n=size(x)

a=n*quart
call getgp(a,b,c)

ib=int(c)
!print *,n,a,b,c,ib

diff=b-0.0d0
if(diff <=tol) then
  quart=(x(ib+1)+x(ib))/2.0d0
else
quart=x(ib+1)
end if

end

subroutine getgp(a,b,c)
! Subroutine to that returns the Right hand and Left hand side digits of a decimal number
real*8 :: a,b,c

b=mod(a,1.0d0)
c=a-b

end

 

wondering what is a quartile, visit http://en.wikipedia.org/wiki/Quartile

Fortran 90 with Excel – gfortran example

Recently i created a program that involved Fortran and excel.  The main solving was handled by the Fortran and excel provided the GUI.

I know many people search for this so here’s the small example that i worked on before going to the main program.

I used gfortran as Fortran compiler and Excel 2003 for the GUI.

Fortran program accepts and array and does some calculation and returns the values back to excel.

The excel VBA code, call the Fortran dll and then the results form the dll call are posted in excel cells.

Here’s the Fortran program

        Subroutine FortranDLL( Array1, upbound )
        Implicit None
! …argument declarations
        Integer :: upbound
        Integer :: Array1(1:upbound)
! Local variables
        Integer :: i
        do i=1,upbound
        Array1(i)=288.16-0.0065*Array1(i)
        end do
        End Subroutine FortranDLL

It was compiled with the following options.

gfortran -mrtd -fno-underscoring -shared -o fortrandll.dll fortrandll.f90

And here’s what I did in the excel.

Declare Sub fortrandll Lib "C:\TEMP\fortrandll.dll" (ByRef Array1 As Double, ByRef upbound As Long)

Sub Button1_Click()
    Dim II As Long
    Dim test(10) As Double
    II = 11
    Call fortrandll(test(1), II)
    Range("a1").Value = test(1)
    Range("a2").Value = test(2)
    Range("a3").Value = test(3)
    Range("a4").Value = test(4)
    Range("a5").Value = test(5)
    Range("a6").Value = test(6)
    Range("a7").Value = test(7)
    Range("a8").Value = test(8)
    Range("a9").Value = test(9)
    Range("a10").Value = test(10)
End Sub

Will post more in coming weeks. Maybe a full fledged excel application using a fortran dll.