Changing the Accounting Again

This gets old – the company that is in or was just in bankruptcy keeps changing its books.

Every month my boss does variance analysis against the budget that was created earlier in the year (and not forwarded to the sites that have to manage their money, go figure). I gather the data for her since I created a semi-automated system to collect the data and place them in the right spot on the report. My boss knows how to do it but the file is getting so big (it now has roughly two years of data) that it is becoming temperamental. And she is doing her own budgeting reviews and meetings for the next year.

If we had to enter the information manually, with 26 sites, it would take forever and the variance report would contain lots of errors. This variance report is not simple; the client asks for details for everything so we have to pull in the information as automatic as possible.

As part of my updates with the latest month, I also check that the prior months do not change. Public companies, as a rule, do not change the accounting once the books have been closed. But the private companies in this industry seem to re-open the books to make updates. Now, supposedly it is done infrequently and should not be a common practice but this company makes changes every month; hence why I check the prior months as part of my process. Usually the changes have been one or two months previously.

This time the changes went back to January and they weren’t chump change. It turns out that this time it was the property taxes changing – maybe they are changing from accrual to actual. This whole thing of re-opening the books and updating the accounting is very different from what I’m used to.

Checking the numbers and tracking down the changes is very time consuming. So I decided to do a macro. Once I tested two or three properties to see if the changes are within the same account, I built up a macro to pull in, from the downloaded data from the accounting system, the values for a specific GL and specific month(s). This accounting system forces you to download data site by site; you can’t do one massive download that contains all of the site accounting information. So I have 26 downloaded files, one for each site, and my macro goes through each of the files and pulls out the site name and the dollars for specific months (in this instance it was set to pull property tax information for the months of January and July). It took me maybe 3 hours to develop the macro (and I wondered if I was going to figure it out) and maybe 60 seconds to collect that specific data. I use that information to make manual updates to my system. Why didn’t I automate that process? Well, I had already used up a lot of time and that problem would be an even greater challenge.

The next section below is just a part of the macro but the main part dealing with finding the specific GL you want and then copying the information.

[divider] A Snippet of the Macro[/divider]

I first started out using the macro recorder but found that I couldn’t really use it so I searched Stack Overflow for an answer. The snippet of the macro below is the altered version that I found in Stack Overflow. The item that I needed, that seems to help make the recorded macro work, was a single line that was changed by using the aCell variable (it’s highlighted in red below). It is just a single small change but it made a difference from not working to working. (Sorry, I can’t make the indent work for the If Then section.)

Dim wbSource As Workbook ‘Yardi downloads
Dim wbdest As Workbook ‘Changes.xlsm
Dim wbmaestro As Workbook ‘This file (Changes.xlsm)
Dim rangetocopy3 As Range ‘Value1
Dim rangetocopy3b As Range ‘Value2
Dim rangetocopy4 As Range ‘Property Name
Dim aCell As Range ‘Where we find the search value
Dim rowcount1 As Long ‘Count where we are in the data in the “data” tab – starting point – “data” is repository of information pulled from source

‘STEP 3c: Find the GL you are looking for

Set aCell = Sheets(“Report1”).Range(“A5:A65000″).Find(What:=”60030”, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)   (The macro recorder had this line but it was slightly different, just enough to cause me problems.)
If Not aCell Is Nothing Then
Set rangetocopy3 = wbSource.Sheets(“Report1”).Range(Cells(aCell.Row, aCell.Column + 2), Cells  (aCell.Row,  aCell.Column + 2))
Set rangetocopy3b = wbSource.Sheets(“Report1”).Range(Cells(aCell.Row, aCell.Column + 8), Cells(aCell.Row, aCell.Column + 8))
End If

‘STEP 3d: Copy the data from Yardi downloads to the ‘Changes.xlsm’ file

wbdest.Activate
rangetocopy3.Copy wbdest.Sheets(“Data”).Cells(rowcount1, 2)
rangetocopy3b.Copy wbdest.Sheets(“Data”).Cells(rowcount1, 3)

‘STEP 3e: Copy the property name. First create a formula to pull out the property name

wbSource.Activate
Range(“O1”).Formula = “=TRIM(LEFT(A1,SEARCH(“”(“”,A1)-1))”
Range(“O1”).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Set rangetocopy4 = wbSource.Sheets(“Report1”).Range(Cells(1, 15), Cells(1, 15))

wbdest.Activate
rangetocopy4.Copy wbdest.Sheets(“Data”).Cells(rowcount1, 1)

Now I can re-use this macro in the future, saving myself time.

[divider]But the real issue is: how far back are they changing the accounting books? Years? There’s a real sense of unease.[/divider]