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
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
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
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
Thx for your advice. I am currently studying finance, so I should probably learn a programming language 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 ^^
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.
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
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.
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.
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.
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.
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…
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.
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.
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, tu confirmes avoir lu et être d'accord avec l'avis de dégagement de responsabilité présenté sur http://www.mustachianpost.com/fr/
Durch das Lesen und die Teilnahme an diesem Forum bestätigst du, dass du den auf http://www.mustachianpost.com/de/ dargestellten Haftungsausschluss gelesen hast und damit einverstanden bist.