How to generate 432 000 random dates faster for excel? Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
DanRim
Posts: 153
Joined: 20 Jul 2018, 15:16

Re: How to generate 432 000 random dates faster for excel?

Post by DanRim » 25 Jan 2022, 02:30

@flyingDman My goal is to generate random data which I will use to create reports with VBA in order to compare production.

Sharing pricture of excel table on how I imagine it should look.

I tried to use Random AHK function to control ranges of dates, but code is very slow and it will take me to generate in excel all data ~120 hours.
What is why I was looking for help (do it with AHK or maybe there is some data generators tools).
It does not matter where I generate that data, because I will copy it to excel. Speed is important, but I can wait and 10 min if needed. If data values are valid, that is perfect, if it is not, I will survive.

In my example I was trying to control date validation ranges with AHK Random function for each year, month, day, hours, min, sec and add 0 if needed if date would have 1 digit (not 1.1.2022 1:1:15 but 01.01.2022 01.01.15). My knowledge with dates and formatting dates are weak, that is why I mentioned that dates does not valid for leap years, because I do not know how to control it. I might misguided you guys with my request when mentioned that it is dummy data and you focused on speed. I am sorry for that.

@flyingDman your example is what I needed and I explained why I chose your example. You understood me right.
I red all comments and it is great when many AHK, Excel and other experts take a look at the task with different approach.
Speed, data validation, possibility to control ranges is important and would be great. Your example was what I needed for my knowledge - you speed up all code, plus - all ranges I can control and for me it is enough.

If it is possible to generate data fast with leap year (valid data) and right format that would be great. But you did what i asked :)

I really appreciate all experts on this topic and please except my apologize if I misguided you.



flyingDman wrote:
24 Jan 2022, 18:33
@DanRim Could you provide an example of what the final output should look like in Excel?
Attachments
table.JPG
table.JPG (50.54 KiB) Viewed 1559 times

just me
Posts: 9425
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: How to generate 432 000 random dates faster for excel?

Post by just me » 25 Jan 2022, 07:14

DanRim wrote:
24 Jan 2022, 17:28
@just me Your code seems interesting too. Does this code generates valid dates? And how could I store all values in clipboard (tried couple times, but I fail, not sure what is approach should be here? Should I Loop or use For loop? Not sure how to concatenate all values to clipboard :)

Hello @DanRim,
all generated dates are valid because AHK's date math is used to create them. I didn't expect that copying 432000 dates at once to Excel using the clipboard would work reliable. But I changed my code to support it:

Code: Select all

#NoEnv
SetBatchLines, -1
; Settings =============================================================================================================
NumOfDates := 432000                   ; number of dates to generate
DateFormat := "dd.MM.yyyy HH:mm:ss"    ; date format as used with FormatTime
MinDate := "20220101"                  ; minimum date
MaxDate := "20221231"                  ; maximum date
; ======================================================================================================================
MaxSecs := MaxDate
MaxSecs -= MinDate, S
FormatTime, TestDate, , %DateFormat%
CharLen := A_IsUnicode ? 2 : 1
ListLen := (StrLen(TestDate) + 1) * NumOfDates * CharLen
DateList := ""
VarSetCapacity(DateList, ListLen)

S := A_TickCount
Loop, %NumOfDates% {
   Random, Secs, 0, %MaxSecs%
   Date := MinDate
   Date += %Secs%, S
   FormatTime, Date, %Date%, %DateFormat%
   DateList .= Date . "`n"
}
T := A_TickCount - S
MsgBox, % NumOfDates . " in " . T . " ms!"
MsgBox, % SubStr(DateList, 1, StrLen(TestDate))

Clipboard := DateList

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to generate 432 000 random dates faster for excel?

Post by flyingDman » 25 Jan 2022, 12:47

I am running behind!
First I want to comment on @FanaticGuru 's "fastest non-Excel" version. It indeed is the fastest todate. It is slightly faster than my last version by an average of about 30 ms. 496 ms vs. 526 ms. On a 432K run, that should not make of break anything, but "credit where credit's due".
Spoiler
:bravo:
I have not had time to look at the two last post above. Will do when I have time.
Edit: got names mixed up. Corrected now.
Last edited by flyingDman on 25 Jan 2022, 14:18, edited 1 time in total.
14.3 & 1.3.7

amateur+
Posts: 655
Joined: 09 Oct 2021, 15:43

Re: How to generate 432 000 random dates faster for excel?

Post by amateur+ » 25 Jan 2022, 13:16

flyingDman wrote:
25 Jan 2022, 12:47
First I want to comment on @just me 's "fastest non-Excel" version.
His nickname is FanaticGuru.
Have found any drawback in my code or approach? Please, point it out. /The moderator ordered to remove the rest of the signature, I had obeyed.
And I really apologize for our russian president. Being a citizen of an aggressor country is very shameful. Personally I tried to avoid this trying to defend elections from fraud being a member of the election commission of one of the precincts but only was subjected to a hooligan attack and right before the vote count was illegally escorted from the polling station and spent the night behind bars (in jail) in a result of illegal actions of corrupt policemen.

DanRim
Posts: 153
Joined: 20 Jul 2018, 15:16

Re: How to generate 432 000 random dates faster for excel?

Post by DanRim » 25 Jan 2022, 14:45

@just me Thank you for you update and time, I really appreciate it :)

User avatar
SKAN
Posts: 1551
Joined: 29 Sep 2013, 16:58

Re: How to generate 432 000 random dates faster for excel?

Post by SKAN » 25 Jan 2022, 14:58

@DanRim

Why are you insisting on Clipboard?
You could create the entire data in CSV format, in memory and write it to a file, and load it excel?
For about 200 chars per record, you could pre-allocate a var for 100MB and then do the concatenation.

Required: #MaxMem 100

DanRim
Posts: 153
Joined: 20 Jul 2018, 15:16

Re: How to generate 432 000 random dates faster for excel?

Post by DanRim » 25 Jan 2022, 16:16

@SKAN You right, in ideal world it would be great to run code which generates needed valid random values and create .CSV file, but at the moment with my knowledge it was simpler just to run code, store values in clipboard which I could transfer myself where I want. It is manual work and it was my thoughts at that time. And I did not want overload people with complicated requests. After I getting feedback I always try to understand code and do modification if i can.

I believe that data which is stored in clipboard I can create .csv file and paste there data with AHK. But at the moment such automation I do not require :)

Thank you for your comment. All comments and ideas are welcome. Allows to learn more. :)

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: How to generate 432 000 random dates faster for excel?

Post by FanaticGuru » 25 Jan 2022, 16:59

SKAN wrote:
25 Jan 2022, 14:58
@DanRim

Why are you insisting on Clipboard?
You could create the entire data in CSV format, in memory and write it to a file, and load it excel?
For about 200 chars per record, you could pre-allocate a var for 100MB and then do the concatenation.

Required: #MaxMem 100

@DanRim Again, if you are wanting to create the dates in Excel and don't actually need the dates in AHK, then might as well let Excel do most of the work.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.numberFormat := "dd.mm.yyyy hh:mm;@" ; can be formatted anyway you want
xlRange.Formula := "=RANDBETWEEN(10000,445610000)/10000" ; 1/1/1900 to 12/31/2021 with time decimals 
xlRange.Value2 := xlRange.Value2 ; converts dynamic formula to static text
About 600 ms on my computer to not only create the dates and times but get them in Excel. This is going to be quicker than creating them in AHK then transferring them to Excel.

You can change the date range to whatever you want. Just change the "1" and "44561" to the correct date. To find the number just type a date in Excel then change the formatting of the cell to general. All dates are actually stored in Excel as the number of days from the start of year 1900. "1/1/2021" for example is 44197 days from the start of 1900 and is the number stored by Excel for that date.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: How to generate 432 000 random dates faster for excel?

Post by FanaticGuru » 25 Jan 2022, 18:25

He is a complete solution for all columns with Excel doing all the hard work.

Code: Select all

SetBatchLines, -1 ; makes all scripts faster but used mainly just to get more consistant results
S := A_TickCount
xlApp := ComObjActive("Excel.Application")

; Recieved Time
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.numberFormat := "dd.mm.yyyy hh:mm;@" ; can be formatted anyway you want
xlRange.Formula := "=RANDBETWEEN(10000,445610000)/10000" ; 1/1/1900 to 12/31/2021 with time decimals

; Process
xlRange := xlApp.ActiveSheet.Range("B1:B432000")
xlRange.Formula := "=""Process name "" & RANDBETWEEN(1,6)"

; Sub-Process
xlRange := xlApp.ActiveSheet.Range("C1:C432000")
xlRange.Formula := "=""Sub-process "" & RANDBETWEEN(1,12)"

; Case ID 1
xlRange := xlApp.ActiveSheet.Range("D1:D432000")
xlRange.Formula := "=RANDBETWEEN(1111111,1999999)"

; Case ID 2
xlRange := xlApp.ActiveSheet.Range("E1:E432000")
xlRange.Formula := "=RANDBETWEEN(1111111,1999999)"

; Amount
xlRange := xlApp.ActiveSheet.Range("F1:G432000")
xlRange.Formula := "=RANDBETWEEN(1,8)"

; Status
xlRange := xlApp.ActiveSheet.Range("G1:G432000")
xlRange.Formula := "=CHOOSE(RANDBETWEEN(1,3),""Pending"",""Completed"",""In Progress"")"

; Employee
xlRange := xlApp.ActiveSheet.Range("H1:H432000")
xlRange.Formula := "=CHOOSE(RANDBETWEEN(1,3),""bobdin"",""johntt"",""mjbbaw"")"

; Team
xlRange := xlApp.ActiveSheet.Range("I1:I432000")
xlRange.Formula := "AHK"

; Created
xlRange := xlApp.ActiveSheet.Range("J1:J432000")
xlRange.numberFormat := "dd.mm.yyyy hh:mm;@" ; can be formatted anyway you want
xlRange.Formula := "=A1+RANDBETWEEN(0,1234)/100000" ; random time from Receive Time

; Handled
xlRange := xlApp.ActiveSheet.Range("K1:K432000")
xlRange.numberFormat := "hh:mm:ss;@" ; can be formatted anyway you want
xlRange.Formula := "=J1-A1"

xlRange := xlApp.ActiveSheet.Range("A1:K432000")
xlRange.Value2 := xlRange.Value2 ; converts dynamic formula to static text

MsgBox % A_TickCount - S
Under 12 seconds on my computer. That is millions of random data items generated.

You could easily format the column widths, add headers, etc.

You could not use the xlRange.Value2 := xlRange.Value2 then it would take under 5 seconds and the formulas will be dynamic. Which means you can go to the "Formulas" tab, hit "Calculate Sheet" and all the data will be randomized again in a fraction of a second.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to generate 432 000 random dates faster for excel?

Post by flyingDman » 25 Jan 2022, 23:42

This is the method using a safearray and leaving the randomizing to AHK. I think we were all clear that Excel handles the randomizing a bit quicker than AHK. As @FanaticGuru noted the speed of his script improves dramatically if you do not convert the formulas to strings. With the script below there is no such conversion needed and it gains speed there. Net-net is takes ~8 seconds compared with ~11.5 sec. (w/ formula conversion) and 4.5 sec (no formula conversion) of FG's script. It is pretty compact as well and fairly easy to read.

Code: Select all

st := a_tickcount
rows := 432000
cols := 11
sArr := ComObjArray(12, rows, cols)    ; creates the safearray with the correct number of rows and columns
loop, %rows%
	{
	sarr[a_index-1,0] := dt := random(10000,445610000) / 10000	; safearrays are base 0 hence a_index -1 
	sarr[a_index-1,1] := "Process Name " random(1,6)
	sarr[a_index-1,2] := "Sub-process " random(1,12)
	sarr[a_index-1,3] := random(1000000,2000000) 
	sarr[a_index-1,4] := random(1000000,2000000)
	sarr[a_index-1,5] := Random(1,8) 
	sarr[a_index-1,6] := ["In Progress","Completed","Pending"][random(1,3)]
	sarr[a_index-1,7] := ["Bobdin","Jbbaw","Johntt"][random(1,3)] 
	sarr[a_index-1,8] := "AHK" 
	sarr[a_index-1,9] := dt + (et := random(1,9999) / 10000)
	sarr[a_index-1,10] := et
	}
xl := ComObjActive("excel.application")
xl.range(xl.cells(1,1),xl.cells(rows,cols)) := sarr

xl.range(xl.cells(1,1),xl.cells(rows,1)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,10),xl.cells(rows,10)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,11),xl.cells(rows,11)).numberFormat := "hh:mm:ss;@"
xl.activesheet.range(xl.cells(1,1),xl.cells(1,cols)).columns.autofit
msgbox % a_tickcount - st

Random(Min, Max)
	{
	local out 
	Random, out, min, max
	return out
	}
14.3 & 1.3.7

User avatar
SKAN
Posts: 1551
Joined: 29 Sep 2013, 16:58

Re: How to generate 432 000 random dates faster for excel?

Post by SKAN » 26 Jan 2022, 02:49

@flyingDman

:thumbup:

You need to add SetBatchLines -1 in above example.
Might not be obvious to everybody.

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: How to generate 432 000 random dates faster for excel?

Post by FanaticGuru » 26 Jan 2022, 03:06

@flyingDman Nice job!

I particularly like the ["Bobdin","Jbbaw","Johntt"][random(1,3)] . I had not thought about doing a selection from a list quite like that. Slick and compact method.

AHK is actually faster than I would have guessed to generate millions of random data items and the safearray gets the data in to Excel pretty quick too.

Whole lot better than 120 hours. :)

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks

amateur+
Posts: 655
Joined: 09 Oct 2021, 15:43

Re: How to generate 432 000 random dates faster for excel?

Post by amateur+ » 26 Jan 2022, 06:33

Maybe you'll kill me since the code will become x2 lines, but I guess you still may improve the speed by not using the function random(min,max):
Spoiler
Have found any drawback in my code or approach? Please, point it out. /The moderator ordered to remove the rest of the signature, I had obeyed.
And I really apologize for our russian president. Being a citizen of an aggressor country is very shameful. Personally I tried to avoid this trying to defend elections from fraud being a member of the election commission of one of the precincts but only was subjected to a hooligan attack and right before the vote count was illegally escorted from the polling station and spent the night behind bars (in jail) in a result of illegal actions of corrupt policemen.

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to generate 432 000 random dates faster for excel?

Post by flyingDman » 26 Jan 2022, 14:46

SKAN wrote:
26 Jan 2022, 02:49
@flyingDman

:thumbup:

You need to add SetBatchLines -1 in above example.
Might not be obvious to everybody.
It goes without saying .... but, you're right, and particularly here where it makes a big difference: ~17.5 seconds vs ~7.7 sec
14.3 & 1.3.7

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to generate 432 000 random dates faster for excel?

Post by flyingDman » 26 Jan 2022, 14:50

amateur+ wrote:
26 Jan 2022, 06:33
I guess you still may improve the speed by not using the function random(min,max):
Yeah, the function might add a few ticks but it makes the script a lot more "readable" and compact. In general I believe that many overuse functions, just because it's more sexy, but here it's warranted.
14.3 & 1.3.7

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: How to generate 432 000 random dates faster for excel?

Post by flyingDman » 26 Jan 2022, 14:51

FanaticGuru wrote:
26 Jan 2022, 03:06
@flyingDman Nice job!
Thank you!
14.3 & 1.3.7

amateur+
Posts: 655
Joined: 09 Oct 2021, 15:43

Re: How to generate 432 000 random dates faster for excel?

Post by amateur+ » 26 Jan 2022, 21:45

For my not so fast computer luxury of saving 9 lines of code for beauty costs additional 15% or 3.5 seconds. Here is the full code for the test:

Code: Select all

SetBatchLines, -1
rows := 432000
cols := 11
sArr := ComObjArray(12, rows, cols)    ; creates the safearray with the correct number of rows and columns
Sleep 1
st := A_TickCount
loop, %rows%
	{
		Random, a, 10000, 445610000
	sarr[a_index-1,0] := dt := a / 10000	; safearrays are base 0 hence a_index -1 
		Random, a, 1, 6
	sarr[a_index-1,1] := "Process Name " a
		Random, a, 1, 12
	sarr[a_index-1,2] := "Sub-process " a
		Random, a, 1000000, 2000000
	sarr[a_index-1,3] := a
		Random, a, 1000000, 2000000
	sarr[a_index-1,4] := a
		Random, a, 1, 8
	sarr[a_index-1,5] := a
		Random, a, 1, 3
	sarr[a_index-1,6] := ["In Progress","Completed","Pending"][a]
		Random, a, 1, 3
	sarr[a_index-1,7] := ["Bobdin","Jbbaw","Johntt"][a] 
	sarr[a_index-1,8] := "AHK" 
		Random, a, 1, 9999
	sarr[a_index-1,9] := dt + (et := a / 10000)
	sarr[a_index-1,10] := et
	}
xl := ComObjActive("excel.application")
xl.range(xl.cells(1,1),xl.cells(rows,cols)) := sarr

xl.range(xl.cells(1,1),xl.cells(rows,1)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,10),xl.cells(rows,10)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,11),xl.cells(rows,11)).numberFormat := "hh:mm:ss;@"
xl.activesheet.range(xl.cells(1,1),xl.cells(1,cols)).columns.autofit

ssap := ( a_tickcount - st)

; Lets run the second approach.

sArr := ""
sArr := ComObjArray(12, rows, cols)    ; creates the safearray with the correct number of rows and columns
WinActivate, ahk_exe excel.exe
WinWaitActive, ahk_exe excel.exe
Sleep 100
send +{F11}
Sleep 2000
st := a_tickcount
loop, %rows%
	{
	sarr[a_index-1,0] := dt := random(10000,445610000) / 10000	; safearrays are base 0 hence a_index -1 
	sarr[a_index-1,1] := "Process Name " random(1,6)
	sarr[a_index-1,2] := "Sub-process " random(1,12)
	sarr[a_index-1,3] := random(1000000,2000000) 
	sarr[a_index-1,4] := random(1000000,2000000)
	sarr[a_index-1,5] := Random(1,8) 
	sarr[a_index-1,6] := ["In Progress","Completed","Pending"][random(1,3)]
	sarr[a_index-1,7] := ["Bobdin","Jbbaw","Johntt"][random(1,3)] 
	sarr[a_index-1,8] := "AHK" 
	sarr[a_index-1,9] := dt + (et := random(1,9999) / 10000)
	sarr[a_index-1,10] := et
	}
xl := ComObjActive("excel.application")
xl.range(xl.cells(1,1),xl.cells(rows,cols)) := sarr

xl.range(xl.cells(1,1),xl.cells(rows,1)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,10),xl.cells(rows,10)).numberFormat := "dd.mm.yyyy hh:mm;@"
xl.range(xl.cells(1,11),xl.cells(rows,11)).numberFormat := "hh:mm:ss;@"
xl.activesheet.range(xl.cells(1,1),xl.cells(1,cols)).columns.autofit

ftap := a_tickcount - st
Gbox("1st approach:`t" ssap  "`n2nd approach:`t" ftap 
	. "`nDelta is " (delta := ftap-ssap) "ms = " Round(delta/ssap*100, 1) "% (" (delta>0?"1st":"2nd") " is faster)")

GBox(pStr := "You've forgotten to pass a string") {
	gui, Font, s12
	gui, Add, Edit, , % pStr
	gui, Show, , % "GuiBox - " . A_ScriptName
	return

	GuiEscape:
	GuiClose:
	Gui, Destroy
	Exit
}

Random(Min, Max)
	{
	local out 
	Random, out, min, max
	return out
	}
Have found any drawback in my code or approach? Please, point it out. /The moderator ordered to remove the rest of the signature, I had obeyed.
And I really apologize for our russian president. Being a citizen of an aggressor country is very shameful. Personally I tried to avoid this trying to defend elections from fraud being a member of the election commission of one of the precincts but only was subjected to a hooligan attack and right before the vote count was illegally escorted from the polling station and spent the night behind bars (in jail) in a result of illegal actions of corrupt policemen.

Post Reply

Return to “Ask for Help (v1)”