After building a minimal FastAPI + SQLite CRUD application in Docker, I wanted to show how Excel can connect directly to it. With a few lines of VBA, we can turn Excel into a client that talks to the API. …
Docker Basics in Excel with VBA | Full CRUD Demo Read more »
If you are just starting with Docker and want a practical project, here is a fun one: let’s build a tiny CRUD app in Python, run it inside a Docker container, and then connect to it from Excel with VBA. …
Docker + Python CRUD API + Excel VBA – All for beginners Read more »
Ok, so some 10 years ago, I was having fun coding A* Search Algorithms in Excel in VitoshAcademy and this is what I had built back then: VBA – A* search algorithm with Excel – Really? VBA – A Search …
Data wrangling with Excel and Pandas is actually quite useful tool in the belt of any Excel professional, financial professional, data analyst or a developer. Really, everyonecan benefit from the well defined libraries that ease people’s lifes. These are the …
In this article, we will explore how to analyze stocks using Python and Excel. We will fetch historical data for three popular stocks—Realty Income (O), McDonald’s (MCD), and Johnson & Johnson (JNJ) — calculate returns, factor in dividends, and visualize …
Automate Stock Analysis with Python and Yfinance: Generate Excel Reports Read more »
Automated stock diagrams with Python is actually a rather useful idea, if you want to see how your portfolio is distributed. Just by using 2 columnes – “Ticker” and “Shares” in a CSV file, you can obtain a lot of …
Getting a list of all folders within a given folder is actually a single line command. Writing it to Excel is usually another line. Getting all files, within these folders is not a lot as well. However, having a tool …
Python – Getting an Excel List of all Folders and Files in them Read more »
Excel with VBA is really a powerful tool. There should be some 5 more years, until Python really becomes a standard for the Excel people and I am not sure that it would actually happen that quickly. Anyway, working with …
In the last weeks, I have noted that one of my answers in StackOverflow has received some upvotes. Which was somehow strange, as people keep on telling me that #VBA is going to die and it is a “funny scripting …
Finally! Microsoft did it! Put python in MS Excel! And I was able to install it and test it on my machine! The video of my attempts is here below: The “code” is that one: def factorial(a): print(a) if (a …
Creamer’s rule for a solution of linear equations states pretty much the following: Using this interesting picture from the German Wikipedia, I have created the following video, explaining a bit the Rule of Mr. Cramer: The video goes through the …
Python – Cramer’s Rule for Linear Equations – With YouTube Video Read more »
The idea of the article is to provide a Python code that does the following: Takes a list or DataFrame; Splits it into quite a few smaller parts; Writes each one on a separate Excel worksheet; Pretty much like this: …
Python – Write a DataFrame or List to multiple tabs in Excel Read more »
Option Compare in VBA is the thing, that will save you ugly stuff like this one: Sub Testing() Dim userInput As String userInput = InputBox(“What is decimal 4094 in hex?”) If UCase(userInput) = UCase(“ffe”) Then Debug.Print “You have correctly written …
VBA – Option Compare (Text || Binary || Database) Read more »
Getting some tiny URL, that redirects you to somewhere else might be dangerous. There are a lot of people, that would never ever click on bit.ly/something or goo.gl/something, just because they do not know where the redirect is going to …
How to get redirect url using Excel VBA or Python Read more »
Reading and writing to an Excel ActiveX textbox with Python can be fun. If you really do not have anything better to do. Anyway, if this is your current task, you are on the correct place to see how it …
Python – Read and Write to Excel ActiveX Textbox Read more »
Working with dictionaries in VBA is a bit more unpleasant, than working with dictionaries with almost any other language. Because of the VBA IDE, you only see the keys, but not the values. Thus, if you have experience with Visual …
VBA – Printing Dictionary of a Dictionary of a Dictionary… Read more »
Saving Excel files, when working with VBA is unfortunately a bit of pain the ass. You always need to make a new version every now and then, because it does not matter how seasoned VBA developer you are, still there …
Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant: Dim myRange As Range Set myRange = Range(“a1″).CurrentRegion Dim myArray As Variant myArray = myRange The “strange” …
VBA – Ubound of Multidimensional Array or How to Get What We Need? Read more »
Intersection of dictionary (or of any two lists) is a list with elements, that are present in both other lists. Thus, if we have the following 3 dictionaries in Python: dict_a = dict([(‘aa’, 4139), (‘bb’, 4127), (‘cc’, 4098)]) dict_b = …
Everyone, who has worked with Excel more than a year knows that the comments are actually quite useful – they allow us to put info data into a cell, without actually putting data in the cell. However, the standard way …
What is the difference between IIF() and IF() in #VBA? The idea of IIF() is that it evaluates both the True and the False part of the condition, before evaluating the condition itself. Thus, if the False part throws an …
Hashing strings in general is useful. It is one of the good things, that we get from granted by the various .Net libraries and we should not try to do on our own, as the HMACSHA1 is quite a powerful …
Converting Excel column number to letter and letter to column number is actually something that every VBA developer does at least once a day. The easiest way is probably to use the property .Column or .Address of the range object …
VBA – Convert Excel Column Number to Letter and Letter to Column Number Read more »
Nested dictionary means a dictionary within a dictionary. Some days ago last week I was trying to build one and it took me some solid 15 minutes to do it, thus I have decided that it is worth an article …
Using API calls with VBA and Python to write a database is actually a pretty decent architecture. Here, I would like to say that “It takes the best of the both worlds”, but the truth is that it takes the …
Using API calls with VBA and Python to Read and Write to a Database Read more »
Yesterday I received an email, refering to some 2 old articles in VitoshAcademy for nested loops without recursion: VBA – Nested loops with recursion (Permutations) VBA – Avoid nested loops with recursion (Part 2) As far as the person from …
Cells and ranges in Excel are quite interesting, as these will always give some points to think of. About an year, after writing the article about VBA – Cells and Ranges in Excel (A bit more than the standard story), …
Most probably you feel like you have a good clue already how to locate a value in a given Excel row. There are plenty of ways – Find() , looking through the cells one by one, looking through the Internet for …
Copying worksheet in VBA is actually a trivial task – use the macro recorder see the code and use it further. Anyway, if you are coding professionally with VBA (yup, we still exist:), you somehow get stomach problems, whenever you …
C#, Python, Java and all the other “fancy” languages have a built-in method, which writes variables into a string. VBA does not have one. Depending on why do we need it, and if we can guarantee that no injection would …
VBA – String.Format or how to put variables in a string Read more »