Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

DW Simpson Global Actuarial & Analytcs Recruitment
Download our 2016 Actuarial Salary Survey
now with state-by-state salary information!


Reply
 
Thread Tools Display Modes
  #11  
Old 05-08-2017, 11:55 AM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 3,546
Default

Read VBA for dummies.

Start with manipulating excel-- simple loops that copy and paste things around (kudos if you don't literally copy-and-paste).

Then work on slightly trickier things like making graphs or formatting cells or doing math.

Then branch out into other MS products.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
Reply With Quote
  #12  
Old 05-08-2017, 01:56 PM
ronaldy27's Avatar
ronaldy27 ronaldy27 is offline
Member
SOA
 
Join Date: Jan 2012
Studying for MLC
Posts: 1,266
Default

Quote:
Originally Posted by Sredni Vashtar View Post
Read VBA for dummies.

Start with manipulating excel-- simple loops that copy and paste things around (kudos if you don't literally copy-and-paste).

Then work on slightly trickier things like making graphs or formatting cells or doing math.

Then branch out into other MS products.
Thanks!
__________________
Spoiler - for size:



Reply With Quote
  #13  
Old 05-08-2017, 03:18 PM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: Knock Turn Alley
Studying for ACAS
College: Hard Knocks
Favorite beer: Sam Adams Cherry Wheat
Posts: 56,029
Default

Used VBA to automate data pulls.
__________________
I find your lack of faith disturbing

Why should I worry about dying? Itís not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #14  
Old 05-08-2017, 03:39 PM
ronaldy27's Avatar
ronaldy27 ronaldy27 is offline
Member
SOA
 
Join Date: Jan 2012
Studying for MLC
Posts: 1,266
Default

Quote:
Originally Posted by Vorian Atreides View Post
Used VBA to automate data pulls.
Can you elaborate a bit more on that?
By data pull, you mean just getting data from some source.

Do you use SQL and/or Access?
__________________
Spoiler - for size:



Reply With Quote
  #15  
Old 05-08-2017, 07:24 PM
Sredni Vashtar's Avatar
Sredni Vashtar Sredni Vashtar is offline
Member
 
Join Date: Mar 2010
Favorite beer: pilseners
Posts: 3,546
Default

Quote:
Originally Posted by ronaldy27 View Post
Can you elaborate a bit more on that?
By data pull, you mean just getting data from some source.

Do you use SQL and/or Access?
You can connect to databases through VBA and use SQL from VBA to query databases or other files. Very useful, but again, not good for practice because you'll spend all your time trying to google the right ADODB commands instead of learning good fundamentals.
__________________
Sredni Vashtar went forth,
His thoughts were red thoughts and his teeth were white.
His enemies called for peace, but he brought them death.
Sredni Vashtar the Beautiful.
Reply With Quote
  #16  
Old 05-08-2017, 08:16 PM
ronaldy27's Avatar
ronaldy27 ronaldy27 is offline
Member
SOA
 
Join Date: Jan 2012
Studying for MLC
Posts: 1,266
Default

Quote:
Originally Posted by Sredni Vashtar View Post
You can connect to databases through VBA and use SQL from VBA to query databases or other files. Very useful, but again, not good for practice because you'll spend all your time trying to google the right ADODB commands instead of learning good fundamentals.
Thank you!
__________________
Spoiler - for size:



Reply With Quote
  #17  
Old 05-08-2017, 09:43 PM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS AAA
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 1,890
Default

Quote:
Originally Posted by ronaldy27 View Post
That sounds really interesting. I started learning VBA by watching videos on youtube. Do you have any book/website recommendation for learning VBA? I know VBA is used very broadly but I'm trying to find a source that can teach me functions used by actuaries.
I don't know too many VBA-literate actuaries who read a book.

I got started by recording macros and reading the help files. Google searches fill in the gaps.
Reply With Quote
  #18  
Old 05-09-2017, 12:23 AM
ronaldy27's Avatar
ronaldy27 ronaldy27 is offline
Member
SOA
 
Join Date: Jan 2012
Studying for MLC
Posts: 1,266
Default

Quote:
Originally Posted by Maphisto's Sidekick View Post
I don't know too many VBA-literate actuaries who read a book.

I got started by recording macros and reading the help files. Google searches fill in the gaps.
Yeah I agree. I have the Excel Bible book but I just can't sit there and read it. When it comes to learning programming, it's just the best to find a project and try to do it yourself.


With that said, I am having a hard time finding a small, do-able project that I can do on my own...


If you guys have something, I'd gladly try it.
__________________
Spoiler - for size:



Reply With Quote
  #19  
Old 05-09-2017, 08:48 AM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS AAA
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 1,890
Default

Quote:
Originally Posted by ronaldy27 View Post
If you guys have something, I'd gladly try it.
I'm going to preface this with three comments:

First, I think most of us learn VBA because we have specific tasks that come up...but those tasks will vary from person to person and company to company. My suggestions aren't going to be necessarily useful tasks...but they should give you a taste of how to work with VBA without specifying "real work" that may or may not be "real"/useful in your particular situation.

Second, remember that a big part of learning to use VBA is figuring out the code yourself and/or learning how to find answers/tips without necessarily pestering others. Google and help files are your friends.

Third, there isn't necessarily a "right answer" for any of these (although some techniques are better than others). The results don't necessarily matter for these (although sanity-testing is always a good idea); the goal is that if you can pull off these tasks, you'll have gained a good feel for the structure of VBA, an inkling of what's possible, and an idea of where to obtain information on the language....and those three things are all that you really need to know for a head start on real-world usage in any language most actuaries are likely to use.

A. Start with a list of 5-10 trios of latitudes/longitudes/time zones of places in the continental U.S. Use VBA to create a table showing sunrise/sunset times of those locations for the next 3 years. (Don't forget about DST; for fun include a location in those parts of Arizona that don't observe DST.)

B. Generate a list of 50+ Excel files on your hard drive or network, with complete paths and filenames (e.g. "c:\foo\bar\baz.xlsx"). Use VBA to create, in Excel, a table with two columns, showing filename in the first column, and the contents of cell B4 on the first tab of the workbook in the second column.

C. Get a table showing number of games won by Major League Baseball by team by year. (E.g. http://www.baseball-reference.com/leagues/MLB/) Use VBA to create for each pair of teams a plot where the X axis is the number of wins for one team, and the Y axis is the number of wins for the other team. Make the plots pretty, and appropriately labeled. Remember to make the plots only for those years in which both teams existed. Don't make plots for pairs of teams that didn't exist at the same time.

D. There has been at least one thread on the Outpost in which folks shared what was in their personal.xls/personal.xlsb files. Poke around in those posts for ideas.
Reply With Quote
  #20  
Old 05-09-2017, 08:54 AM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS AAA
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 1,890
Default

P.S., for parts of tasks A and C in particular, remember that sometimes the easiest way to get an idea for how to do something with VBA is to record a macro of you doing it yourself "manually".
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 07:19 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.27794 seconds with 9 queries