Using NAV SumIndexFields in the Stock Exchange Bookeeping App
Find out how NAV SumIndexFields Technology works. I used it in my Stock Exchange Bookkeeping Application in order to calculate fast the Average Buy Price.
An attempt was made to change an old version of a record- NAV Error
I currently investigated the following NAV Error reported in a support incident: —————————— An attempt was made to change an old version of a Customer record. The record should first be reread from the database. This is a programming error. Identification fields and values: No.=’01121212′ —————————— This NAV error can appear on any record, I…
How to implement trial period or protection for your Excel Add-ins or workbooks using Excel VBA
There might be situations when you would like to set a trial period for your Excel applications: maybe you want to prevent employees take the applications and use them forever in other places after they leave your company or you simply need to provide to a client a trial that can be tested only a limited…
An example of how to use Excel VBA Dictionary of arrays data structure
In this post I will demonstrate how to use Dictionaries in Excel VBA in order to calculate, store and use data summarized on different criteria. In the real life I used the algorithm from this post to automate the issue of inter-company invoices. This automation saved dozens of hours considering that before they had it, users where issuing…
Case Study- Automation of processing and sending reports
1. Project Introduction: Each team from the Accounting department of a SSC had a delegated person who was responsible to manually prepare and send via email every day, for 10-15 days in a row each month, a report regarding the status of the account reconciliations for the month. There was no automation for this process…
Algorithm to Calculate Workdays and Import in Outlook Calendar
What was the problem: In finance-accounting area some companies consider workdays as below: WD-5: the last fifth working day from the previous month … WD-1: the last working day from the previos month WD1: the first working day from the month WD2: the second working day from the month … How can we create an algorithm…
Excel Formula and VBA solution to dynamically extract the file name from website paths
You have an Excel file with thousands of rows like in the below image and for each row you need to extract the file name: How would you do this ? Solution: One formula that can solve your problem is this one: =RIGHT(A3,LEN(A3)-FIND(“$”,SUBSTITUTE(A3,”/”,”$”,LEN(A3)-LEN(SUBSTITUTE(A3,”/”,””))))) Let’s explain how it works: The file name is the sub-string after the…
Python script to send every month an email reminder in a specific day of the month
In this post I explain how to create and schedule a Python script that automatically sends every month an email to a list of persons in a specific day and hour of the month.
Excel import exchange rates from website and some nice formulas
In this post I show you how to Import in Excel exchange rates from a webpage using VBA Internet Explorer Automation. You will also learn to use some nice formulas (date formulas, counta, indirect, text) and create a dynamic chart to represent the trend of the imported data.
Best practice to find with Excel VBA the Last Row and the Type of the current Selection
In this post I present the best practices to find in Excel VBA the Last row, last column of a worksheet and Type Name of the current selection. See here how to do it properly