Need help from a VBA Wizard

I assume that some of you guys also track your investments in excel. I am right now trying to build a daily value tracker, but I can`t find a solution to make sure that I only have one date entry when I hit the button multiple times a day.

The Code so far:

Sub Refresh()
ActiveWorkbook.RefreshAll
ActiveSheet.Range(“A1”).End(xlDown).Offset(1, 0).Value = Date
Worksheets(“Portfolio”).Range(“B3”).Copy
ActiveSheet.Range(“B1”).End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End Sub

That’s more of a question for Stack Overflow :stuck_out_tongue:

image

Option Explicit

Sub AddPrice()
    
    Dim Prices As ListObject
    Set Prices = Sheets("Sheet1").ListObjects("prices")
    
    Dim DateCol As ListColumn
    Set DateCol = Prices.ListColumns("Date")
    
    Dim PriceCol As ListColumn
    Set PriceCol = Prices.ListColumns("Price")
    
    Dim LastDate As Date
    LastDate = Prices.DataBodyRange(Prices.ListRows.Count, DateCol.Index)
    
    If LastDate < CDate(Date) Then
        Prices.ListRows.Add
        Prices.DataBodyRange(Prices.ListRows.Count, DateCol.Index) = Date
        Prices.DataBodyRange(Prices.ListRows.Count, PriceCol.Index) = 1.23
    End If

End Sub
1 Like

Who is Stack Overflow?

I assume you are a VBA Wizard :wink: I just started with VBA. Did my Rebalancing Tool without a macro and took me an hour and multiple Ifs

Example:

1 Like

Ahh :smile: thx gonna check it out

I don’t know your story but if you want you time spent on programming languages to be an investment rather than a waste, look for stuff like python, java, … but not VBA :smiley:

I also started with excel sheets long ago. today I am with beancount, and I can recommend it to anyone who likes to tinker about his finances.

Stack Overflow? hm, good question. last time someone mentioned that name, it cam along with that of the other guy, I think Googl or so was his name :wink:

3 Likes

Thx for your advice. I am currently studying finance, so I should probably learn a programming language :wink: Do you have any language suggestion for a career in the banking sector?
VBA was just a side project to improve my personal excel portfolio tracker ^^

:laughing:

Best investment IMO are Python or R (also as transferable skill for data processing/analytics anywhere, not just in banking), and SQL for the databases.

Datacamp has fantastic hands-on learning courses.

2 Likes

If you work a lot with Excel, then VBA is a wonderful addition. From small to large business, if you receive data in Excel files, you can automate a lot of stuff using VBA. The advantage is also, that your business PC might not run Python and you may be not allowed to install it. But MS Office is standard and you usually can run VBA anywhere.

Let me give you an example: at my company, they defined a master excel sheet with some classifications. I had to convert this into code. I wrote a small VBA script that converted the code from table. So each time that they updated the excel, I would just run the script and copy paste it into the IDE and check in :slight_smile:

Oh and VBA is wonderful for debugging. You have the Step In button, with which you can run the code line-by-line and stop when you want. You have Locals Window, where you see all the declared variables and their values. And you have the Immediate window, where you can type in some quick code while the execution is suspended.

2 Likes

Agree 100%, especially in Finance roles Excel is still the largest ERP in the world (for good or for worse). Many traders still have very elaborate Excel files with VBA / Macros / Add-ins.

And, for busines users, you can record Macros to avoid writing code from scratch and just modify where needed.

We perhaps are the MouStach Overflow :smile:

2 Likes

This is also excellent, for off-the-sheet data manipulation, where VBA sometimes has limits.
I would recommend @Tea however to get familiar with VBA first, very easy to work with, and step to the other two a bit further on when he hits VBA limits.

1 Like

Thx gonna check that out.

@Bojack do you have any recommendation where to find learn ressources for VBA?
@lowyield
Guess I am gonna start in the following order:

  1. VBA to an adequate level
  2. Getting better at R (already have some basic knowledge)
  3. Python

Not really, my guess is as good as yours. I have learned through trial and error, reading various tutorials and stack overflow questions. In the end, if you know programming, vba should not be difficult to grasp. It’s pretty standard stuff. For sure what is great is the library reference at microsoft.com, you can check what properties and method e.g. a Range or a Sheet has.

@Bojack guess than I will also learn it by trail and error :wink:

Agree.

VBA + COM/OLE was supposed to be deprecated by .NET nearly 20 years ago but due to high demand it’s still in use - even if not updated since long time - and the standard if you need to automate stuff with a spreadsheet.

Easy to learn/use, simple syntax, tons of docs on internet… so @Tea any question you may have has already been posted and answered long, long time ago -> so just Googl and you will be served !

As @Bojack says, macro recorder, good ide, just in time editor, immediate window etc etc…

As not really oop not so immediate to make really reuseable code but still…

if you then get hooked and want to deepen the classic VB topic, get yourself a 2nd hand copy of this classic … I still have mine in my basement and browse it from time to time with nostalgy… :joy:

2 Likes

You’re probably going to have a bad time if you start asking questions yourself there. Stackoverflow is not about helping you, it’s more like a Wikipedia but with questions and answers so lots of other people can be helped.

1 Like

Stackoverflow does help, the problem is that it’s a site for programmer, so you need to ask the good questions, which usually translates in asking google first to find the correct answer on SO. Maybe with VBA is more difficult because it doesn’t debug well (or not, it’s been years …)

Is it me or when someone ask for “Wizard’s help” I start getting a tiny bit annoyed? It feels a bit like those times in the 80es when people come to you for any computer questions because you have a C64 at home where they made you feel like their life is too important to start learning how to use a pc.

Not on this case, it’s just a feeling. It’s something I’ve never seen or heard in other professions.

1 Like

Cant really relate to the 80s ^^ I was born in the late 90s and made my first computer experiences on a standing around win97.

I assume you dont have many friends working in different fields with different interests :wink:

I’m sorry that you feel attacked.

By reading and partipating to this forum, you confirm you have read and agree with the disclaimer presented on http://www.mustachianpost.com/
En lisant et participant à ce forum, vous confirmez avoir lu et être d'accord avec l'avis de dégagement de responsabilité présenté sur http://www.mustachianpost.com/fr/