VBA/Macros

Hi Gang,

So I'm trying to learn VBA for excel. While I hear it is not really THAT complicated I am having a hard time trying to find examples of how VBA in excel would simplify certain excel processes.

If anyone uses VBA or Macros what do you use them for in a acquisition model or debt or equity model?

Any specific examples would be really helpful.

Oh, and enjoy the weekend everyone!

 

learning VBA is one of the most useful things you can do IMO. It puts you head and shoulders above most other people in terms of efficiency. You can automate any process you want really. Whether it be adding rows or columns in certain places, changing certain cell values, formatting things, importing or exporting data from other files. When you combine all these things together it allows you to click a button and excel import data, interpret it, and spit out a results in the format you want. It can be pretty powerful. It also is pretty easy to instantly finish repetitive tasks that are a time sink. I don't even use VBA that frequently but it's saved me countless hours from the times I do need it.

 

I was a VBA power user in college but having gone int into workforce, unless a job explicitly states that you will be using it nobody cares too much. My personal experience is that it will definitely raise eyebrows if you know it (especially in real estate) but dont think it will give you a big edge over anyone else in a job that doesnt really require it.

Just my $0.02.

 

I always write VBA code to auto recalculate formulas/pivot tables for any workbooks that I send to others, especially if they aren't power Excel users. A lot of people don't realize that pivot tables don't auto update, so someone will be looking at base data and change something and then flip over to the pivot table you created and have no idea that it didn't update itself. But you can program VBA to do that for you.

If I'm working on a large project with a shared workbook, I use VBA to build an audit log so I know who changed what.

Also, automated reporting is a big one for me. I get downloads from accounting, valuations, middle office, etc. and have to create quarterly reporting. All of the downloads look exactly the same each quarter, so I automated all of the importing, formatting and a lot of the calculations. Once base data is checked, I can click a couple of macros and the reports look exactly like last quarter without having to remember if this was left justified or center and if that was accounting or number formatted.

I'm the only person in my shop that knows VBA/some of the advanced Excel techniques like array formulas. Definitely can be useful, but mostly because it makes your life easier. Nobody is going to really care that you know it.

 
Best Response

I have one that formats ARGUS DCF outputs. It removes the ARGUS logo on the left side (the embedded photo can sometimes corrupt an Excel file), deletes all of the header information on the right side, concatenates column headings/wraps text + formatting, adds deal/property name and proper titles that are specific to my company's Investment Committee rules.

I also have a macro that adds debt metrics (DS, DSCR, LTV, etc.) at the bottom of the DCF which is keyed off of debt information that I put into another worksheet.

And then I have a bunch that are aren't acquisitions specific which are pretty common among the VBA community. * remove all hyperlinks * change all text to numbers * delete blank rows * copy and paste special values/Replace #N/A with blank cell

 

Haha, everything. No joke. On most trading desks, vba and macros are used for almost everything. As an analyst on a prop trading desk, I spend a good chunk of my time with vbas and excel, mostly automating manual processes. We also use it to pull data each day, run p&l, and it runs our whole risk platform. So...basically everything. Oh, I also wrote a tool to automatically send an emails, which is helpful.

 

EagleSpread nailed it...Macros / VBA can be used very effectively to automatically populate spreadsheets like theo sheets, PnL reports, trade activity reports, blotters, etc. You can also generate theos w/ VBA & produce bids & offers around your theo. You can graphically model your positions w/ respect to price, time, etc. You can obviously generate budgets, commission schedules, etc. The list goes on. VBA & Macros are a huge part of market making & the like.

 

I took an excel class at school, first half was mostly a joke but second half we used "excel 2007 vba for dummies", you can pretty much teach yourself from that book. it should be easy if you've ever learned any programming

 

excel 2003 vba for dummies. really good book. it has examples that even much thicker vba books don't have. from there, you'll find google searches to be the best bet. when searching I usually end up at sites like ozgrid or excel forum..

 

There are lots of things, but most are hard to do, given that they depend on the format of input data. For instance, I can tell you that one of my very first tasks was to create a bunch of sheets that would help people remember various specific events, such as option expiries and swap fixings (e.g. turn them into reports or, better yet, appointments in Outlook). In general, it really depends on what tasks people find particularly onerous/repetitive.

 

I agree with Martinghoul answer above. From a team to another, even for the same business line / bank, your tasks could be very different depending on your manager, team skills balance , last events / commercial / operational issues, etc.

Coding skills could be used for a wide range of tasks inside your desk : booking macros, pricing macros, greeks macros, project management macros, market data macros, KYC macros, mailing macros, etc. "VBA is a plus" is quite a common requirement in trading but could mean many things: from maintaining the existing tools to automate a manual process from scratch, from the most boring ones (book staff leaves) to the most valuable ones (a new pricer, a market impact simulator or even a strategy algo).

Now, if you need something to train yourself to get / format / analyze / calculate / post data project, just start any personal project that you find impelling and that could need this kind of A-Z design: a poker bot, a twitter news reader, a project manager or accounting dashboard for your father, etc. From my side, before my first job in S&T, I created a VBA macro getting data from yahoo finance (HTML request + parsing), formatting data (dictionnary, arrays), inputting in Access database (I/O management with ADO objects), and calculating basic financials ratios. Just try to find something motivating and involving different coding skills.

 

as a starter, learn to use and master the following excel tools

Solver (for solving linear optimization problems) Linest Pivot tables Linear Regression (chart plotting and creating a simple linear model to calculate rich/cheap) Data Validation (to create dropdown selectors from a list or named range when building spreadsheet) VBA (to automate all of the above)

A great example project that will be actually useful is to take a data set (say,2yr, 5yr, 10yr and 30yr yields from the https://fred.stlouisfed.org/ database, and plot the 10/30 yield curve against the level of 10yr and 5yr rates. Use both Linest and Solver to calculate the relationship over different time frames (last 6 months, 2 years, 5 years, etc.) to come up with trade ideas.

Then, make a more generic version of your spreadsheet so that any relationship (curve, fly, condor) can be analyzed this way. Then use the generic tool you built to search for trade ideas.

Then add other things to the data set you are using to find correlations (S&P 500 index, Spot Crude Oil price, FX like USD/JPY and EUR/USD, foreign govt yields like German and UK 10yr yields). Search for relationships that come and go under different time frames. Try to think like a prop trader. If you could trade anything and everything among all the spot instruments, what would you trade and why. Use your tool to help answer that question. Relationships come and go depending on what is happening in the world. Try to explain divergences in the relationships you find with world events at the time of the divergence...you will find that sometimes, the data does indeed tell a story.

 
RustyFork:

Sounds pretty simple. Just record a macro and clean it up.

It probably is pretty simple, but this has motivated me to go ahead and start learning what I can about it.

 

Sounds like this is close to what you want to do. First I would do a couple lines to get the date data that you need, assuming that you follow some kind of conventioin "May2013invoice.xls"

Range("M1").Select ActiveCell.FormulaR1C1 = "=NOW()" Range("N1").Select ActiveCell.FormulaR1C1 = "=MONTH(RC-1)-1" Range("M1").Select Selection.NumberFormat = "yyyy" Range("O1").Select ActiveCell.FormulaR1C1 = "=YEAR(NOW())" Range("P1").Select ActiveCell.FormulaR1C1 = "=IF(LEN(RC[-2])=1,CONCATENATE(0,RC[-2]),RC[-2])"

This will get you some info to use to help you pull in the old file. Now you will want to set these values to variables.

Dim newdate As String
Dim newyear As String

    newdate = Sheets("Sheet3").Cells(1, 16).Value
    newyear = Sheets("Sheet3").Cells(1, 15).Value

Now you have two variables that you can use to pull up your file from last month.

Workbooks.Open Filename:= _
    "\\**Cdrive\**Folder\" & newyear & "-" & newdate & " Invoice.xlsx"
Windows("" & newyear & "-" & newdate & " Invoice.xlsx").Activate

The drive and folder you will have to find, and you can add more slashes if it is nested further into a folder. Now you will want to copy this data and paste it into your new file.

Range("A:BB").Select Selection.Copy

Windows("Newinvoice.xlsx").Activate Range("A1").Select Activesheet.paste Windows("" & newyear & "-" & newdate & " Invoice.xlsx").Activate ActiveWorkbook.Close False

Now you will have the prior months data copied into your new spreadsheet and you can apply whatever formatting and new info that you need. This also copies the whole sheet, you can pick and choose what you need. I did something very similar at work the other day, so thought I'd try to help out.

 

Billy Beane offers a good initial outline on how to get this done. I would suggest researching VBA and trying to improve the code.

For example, the first part of the code enters formulas into a sheet so that you can eventually determine the dates to use in the file name. So you should look into a way where the code doesn't have to insert the formals into a sheet, it would just assign variables the date values directly.

You can google this stuff and get answers, i did a quick search with "excel vba last month" and found a forum post with this answer, MyMonth = Format(DateAdd("m", -1, Date), "mmmm") which saves a bunch of lines of code. of course you should research each of the functions in there to understand what it means and to make sure it will work for all dates. This site is good because it gives an explanation of formulas and also shows how to use it in VBA, http://www.techonthenet.com/excel/formulas/

So if you wanted to quickly see what the above is doing, you can just open new sheet and run this:

Sub test()

MyMonth = Format(DateAdd("m", -1, Date), "mmmm")

Sheet1.Range("A1") = MyMonth

End Sub

Always make sure you think about all possibilities and QA your code. What if someone doesn't name the file right that you are trying to open? VBA would just toss a random error, but you can put some code in that has a pop up that says something like, "Error in code, check to make sure the file name is formatted like blahblahblah". Then you can expand on this, like adding a popup box where you can select what data to copy over. Some good websites are ozgrid and MrExcel.

 

hey BBand, I appreciate the effort.

For some reason, I was an idiot. The way I did it was just copying the formula, coing CTRL+down arrow, and moving over to Column A/B and CTRL+SHIFT+UP and paste.

I'm not sure why this didnt work for me before, but i think its working now......... Well see, thanks again.

 

Okay, one more:

How do I function wise select the cell next to the highlighted one.

IE; B1 is higlighted, how do I write something so that A1 is selected (but constant, so B4 would bring me to A4).

If I just push the left arrow button, it records it as that exact cell, and I just want it to be a constant.

Oh, and if you guys couldnt tell already, I'm doing this the recording way, and not writing shit since I dont feel like learning the code stuff.

Thanks

 

just hit F4, hitting it once makes the whole cell constant, hitting it twice makes only the row constant, hitting it three time makes only the column constant, and a fourth tap will bring you back to the beginning...

 

Unless i'm using a macro package, I most frequently set and use macros for blue font color for input data, thin bottom cell border (underlining across cells), center align, right align, etc.

Remember most functions can be performed by using the menu-based shortcuts (i.e. Alt + O + R + E), but ones that are good for macros are things that are more difficult to get to like cell bordering, font coloring, etc.

 

agree, there are plenty of sources for macro info, but generally they're great for anything that's repetitive

i'll usually use a macro for tasks like paste special when i know i'll be doing them a ton in a worksheet since it's easier to assign it one key than to have to press a multi-key shortcut each time. also macros are awesome if you have to regularly compile reports/summaries from any source data (i have to make a few weekly summaries for the higher ups and now i can do it in a couple minutes)

pivot tables are useful anytime you have a large amount of data that you want to be able to rearrange/manipulate easily. i generally hate the new Office but pivot tables in excel 07 have a couple of useful features

 

Do you HAVE to write macros for this project? If the search criteria is consistent (e.g. all the consumer focused funds are labeled "consumer", energy --> "energy", etc.) then you can just use INDEX and some LOOKUP functions to pull the data in.

Ace all your PE interview questions with the WSO Private Equity Prep Pack: http://www.wallstreetoasis.com/guide/private-equity-interview-prep-questions
 

I must be missing something but I'm with Stringer here... shouldn't you just be able to use LOOKUPs to search by whatever you want (Industry, AUM, etc) and pull the data that way? Or to make it more user-friendly you could just drop in a button next to some entry field where you specify what criteria to search by, then have the LOOKUP use the values from those fields to spit out the information from the database when someone clicks the button. You could use VBA for that I guess.

I hate victims who respect their executioners
 

Put a sum formula in the top spreadsheet referencing the cells in the bottom spreadsheet...

It would be like this "=SUM([Linesmar_jun_journals.xlsx]mar_jun_journals!C2:C5)"

but since you are doing credits and debits seperate, if they are always the same value, just divide the total by 2 after. So (C2:C5)/2

edit: Just realized this will take you forever because the scroll bar is tiny and you will have to go one by one. Well yes you can definitely do this in VBA and it will consist of IF statements, where if the number changes you move to the next cell and start getting the total again. Don't got time to put the whole thing together though for you, search around for an answer on google.

Frank Sinatra - "Alcohol may be man's worst enemy, but the bible says love your enemy."
 
yeahright:

Put a sum formula in the top spreadsheet referencing the cells in the bottom spreadsheet...

I would advise you to rename the spreadsheets first though to something way shorter and basic.

On the top WS, each row is a unique Doc #. The sum of each of those Doc #s is composed of one to several line items.

I don't see how I could use just a sum function and drag it down because the amount of line items matching each heading varies.

"Mr. Perkins poses an extreme risk to the market when drunk."
 

Yeah I knew this from the getco but didn't realize how many you would have to do. If it was only a couple it would of been no biggee to do it manually. Read my edit above.

Frank Sinatra - "Alcohol may be man's worst enemy, but the bible says love your enemy."
 

Yeah I knew this from the getco but didn't realize how many you would have to do. If it was only a couple it would of been no biggee to do it manually. Read my edit above.

Frank Sinatra - "Alcohol may be man's worst enemy, but the bible says love your enemy."
 

=SUMIF( bottom spreadsheet B:B , document reference in top spreadsheet ie. B2 , bottom spreadsheet H:H )/2

Like somebody above mentioned, the /2 is because debits and credits are assumed equal and absolute duplicates of each other.

 
RustyFork:
Cruncharoo:

Guys it's a SUMIF

What would the function look like?

=sumif(RANGE- basically the B column on the bottom f4 to lock that in, CRITERIA - the cell you want to lookup down below so L2 for top row don't need to lock it in, SUMRANGE - column H down below lock it in). Then divide by 2 since you have equal credit/debits.

This to all my hatin' folks seeing me getting guac right now..
 

Id et est veniam et laudantium. Eaque quia optio natus assumenda dolorem qui. A et eaque ea enim aut qui deleniti. Atque ipsum eos dolores aut vero. Minima dicta quia id minima sit sequi. Harum harum ut aliquam. Commodi culpa voluptas sed laboriosam quis.

Sit consequuntur cumque impedit tempora quis. Et accusantium maxime reprehenderit aut quos natus qui amet. Voluptates exercitationem vitae ut blanditiis facilis. Voluptas voluptatem amet quam quibusdam debitis a. Totam repellat non corrupti accusamus maiores dignissimos. Eligendi earum illo pariatur vel facilis rem perspiciatis.

Dolorem repellendus qui atque. Rerum dolores cum eos modi voluptates aut voluptas. Architecto aspernatur accusantium ratione quia placeat. Quae dolore natus nemo distinctio.

Porro impedit voluptates accusantium ullam. Ad nesciunt facere et consequuntur explicabo.

 

Molestiae harum quisquam aperiam quos. Quia quaerat quia ut vel aliquam quibusdam. Laboriosam quia unde asperiores vero unde. Autem temporibus nostrum veniam similique qui dolore.

Dolorum et aut ex esse facilis eum eum beatae. Et ut sed cupiditate quia voluptatem laboriosam illo. Aut perferendis enim et minus eaque labore.

Voluptate esse qui laudantium est porro earum voluptatibus. Nam minima iusto consequuntur quod quia quia rerum. Numquam et voluptatibus autem eum corporis esse quia.

A et labore ex inventore dolores. Quam et eum et ea qui eos. Quia distinctio eos qui velit natus veniam cupiditate odit.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”