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

How to generate 432 000 random dates faster for excel?

Post by DanRim » 22 Jan 2022, 09:35

Hello,

I need to generate 432 000 random lines of dummy dates and some random process names like (Car1, Dog2, human3) and so on.

Maybe someone knows faster way or tool to do that? Because if I will use AHK it will took me generate and paste all date to Excel ~120 hours?

I need just random dates at the moment.

Processes I probably will do using Array and for loops. If some one have better idea, please share.

Code generates each rando number separate and if value have 1 digits, script adds 0. For February random generator does not work correctly, but it is dummy data so at the moment I do not need this, But if some one have fast solution and how to generate correct dates I would not mind to see :)

Have a nice weekend :)

Code: Select all

#SingleInstance, Force
SetKeyDelay, -1


loop, 5 
{
	Random, incommetMonth, 1, 6
if(incommetMonth~="\d{2}") {
	incommetMonth := incommetMonth
}	else {
incommetMonth := "0"incommetMonth
}
sleep, 10


Random, incommetDay, 1, 31
if(incommetDay~="\d{2}") {
	incommetDay := incommetDay
}	else {
incommetDay := "0"incommetDay
}
sleep, 10

Random, incommetH, 0, 23
if(incommetH~="\d{2}") {
	incommetH := incommetH
}	else {
incommetH := "0"incommetH
}
sleep, 10

Random, incommetMin, 0, 59
if(incommetMin~="\d{2}") {
	incommetMin := incommetMin
}	else {
incommetMin := "0"incommetMin
}
sleep, 10

Random, incommetSec, 0, 59
Random, incommetSec, 0, 59
if(incommetSec~="\d{2}") {
	incommetSec := incommetSec
}	else {
incommetSec := "0"incommetSec
}
sleep, 10

varr := incommetDay . "." . incommetMonth . ".2022 " . incommetH . ":" . incommetMin . ":" . incommetSec
Clipboard := varr
;~ send, %incommetDay%.%incommetMonth%.2022 %incommetH%:%incommetMin%:%incommetSec%
;~ send, ^v
;~ Send, {Enter}

MsgBox,    %incommetDay%.%incommetMonth%.2022 %incommetH%:%incommetMin%:%incommetSec%
}


^!x::
TrayTip, %A_ScriptName% is closed, `n, 5, 17 ; `n makes empty, 5 means 5 sec, 17 type of icon 18 19 20 
/*
SetTimer, HideTrayTip, -2000

HideTrayTip() {
    TrayTip
}
*/

ExitApp


User avatar
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 22 Jan 2022, 10:00

Code: Select all

Loop, 25
 ToolTip, % list := randomDate() (list ? "`n" : "") list

F3::MsgBox, 64, Random date, % randomDate()

randomDate() {
 Loop {
  Random, julian, 2159351, 2459601
  ; FormatTime, date, % gregorian(julian), yyyy/MM/dd
  FormatTime, date, % gregorian(julian), dd/MM
 } Until date
 Return date
}

gregorian(julian) {
  Q := julian + 0.5               , Z := Floor(Q)               , W := Floor((Z - 1867216.25) / 36524.25)
  X := Floor(W / 4)               , A := Z + 1 + W - X          , B := A + 1524
  C := Floor((B - 122.1) / 365.25), D := Floor(365.25 * C)      , E := Floor((B - D) / 30.6001)
  F := Floor(30.6001 * E)         , m := E > 13 ? E - 13 : E - 1
  Return Format("{}{:02}{:02}", m < 3 ? C - 4715 : C - 4716, m, Floor(B - D - F + Q - Z))
}
Or:

Code: Select all

Loop, 25
 ToolTip, % list := randomDate() (list ? "`n" : "") list

F3::MsgBox, 64, Random date, % randomDate()

randomDate() {
 Random, month, 1, 12
 Random, day, 1, [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31][month]
 Return Format("{:02}/{:02}", day, month)
}

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

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

Post by amateur+ » 22 Jan 2022, 11:56

Here is an extension of the second mikeyww's script that allows to generate dates with years also as his the first script does.

Code: Select all

Loop, 25
 ToolTip, % list := randomDate(1) (list ? "`n" : "") list

F3::MsgBox, 64, Random date, % randomDate()

randomDate(includeYear := false) {
 if y := !!includeYear
  Random, year, 1200, % A_YYYY
 Random, month, 1, % year = A_YYYY ? A_MM : 12
 days := [31, Mod(year, 4) || !Mod(year, 100) && Mod(year, 400) ? 28 : 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
 Random, day, 1, % year . Format("{:02}", month) = A_YYYY . A_MM ? A_DD : days[month]
 Return Format((y?"{1:}/":"") . "{" . 2+y . ":02}/{" . 3-y . ":02}", year, day, month)
}
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.

sofista
Posts: 645
Joined: 24 Feb 2020, 13:59
Location: Buenos Aires

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

Post by sofista » 22 Jan 2022, 12:25

Indeed, adding the year to mikeyww's code. As the following is a simpler approach, it doesn't take into account leap years:

Code: Select all

Loop, 25
 ToolTip, % list := randomDate() (list ? "`n" : "") list

F3::MsgBox, 64, Random date, % randomDate()

randomDate() {
	Random, R_year, 1600, A_Year
	Random, R_day, 1, 365
	R_date := R_year . 01 . 01
	R_date += R_day, days
	return RegExReplace(R_date, "(\d{4})(\d{2})(\d{2}).+", "$1/$2/$3")
}

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

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

Post by flyingDman » 22 Jan 2022, 12:56

I believe the scripts above are still slow. The following generates 432,000 valid dates in less than 2 seconds:

Code: Select all

cnt = 0
loop
	{
	random, yr, 1900, 2050                        ; your choice
	random, mo, 1, 12
	if mo in 1,3,5,7,8,10,12
		random, dy, 1, 31
	else
		random, dy, 1, 30
	mo := SubStr("0" . mo, -1)
	dy := SubStr("0" . dy, -1)
	var := yr mo dy
	if var is date
		{
		FormatTime,dt, %var%, ShortDate                    ; choose format
		lst .= dt "`n"
		++cnt
		}
	if (cnt = 432000)
		break
	}
clipboard := lst	                           ; ready to paste in excel
edit: this is about as fast:

Code: Select all

cnt = 0
loop
	{
	random, yr, 1900, 2050
	random, mo, 1, 12
	random, dy, 1, 31
	mo := SubStr("0" . mo, -1)
	dy := SubStr("0" . dy, -1)
	var := yr mo dy
	if var is date
		{
		FormatTime,dt, %var%, ShortDate
		lst .= dt "`n"
		++cnt
		}
	if (cnt = 432000)
		break
	}
clipboard := lst	
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 » 22 Jan 2022, 13:41

I used this to create a series of random strings and integers, 8 digits and 8 characters separated by a tab:

Code: Select all

loop, 10
	{
	loop, 8
		str .= chr(Random(65 , 90))
	Loop, 8
		num .= chr(Random(48 ,57))
	lst .= str "`t" num "`n"
	str := num := ""
	}
msgbox % lst

Random(Min, Max){
	local out 
	Random, out, min, max
	return out
}	
Combine that with the 1st script and you should have 432,000 date/string/integer combinations in - I guess (depending on your processor) - less than 5 seconds.
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+ » 22 Jan 2022, 17:20

FormatTime,dt, %var%, ShortDate is slow. This method is much faster:

Code: Select all

lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
And even with this improvement this method loses ~14% to modified @sofista's method:

Code: Select all

SetBatchLines, -1 ; I've implemented this directive to your script too, of course.
timeStamp := A_TickCount
list := ""
maxdays := A_Now
maxdays -= 16010101, Days
Loop, 432000 {
	Random, days, 0, % maxdays ;153789
	date := 16010101
	date += days, Days
	list .= SubStr(date, 1, 4) "/" SubStr(date, 5, 2) "/" SubStr(date, 7, 2) . "`n"
}
clipboard := list	
delta :=  A_TickCount - timeStamp
Gbox(delta)
return

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
}
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
Chunjee
Posts: 1400
Joined: 18 Apr 2014, 19:05
Contact:

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

Post by Chunjee » 22 Jan 2022, 18:12

https://biga-ahk.github.io/biga.ahk/#/?id=times will call {{x}} function {{y}} times and put all the results into an array.

Code: Select all

A := new biga() ; requires https://www.npmjs.com/package/biga.ahk

randomDatesArray := A.times(432000, Func("randomDate"))
; => ["95990212000000", "55390314000000", "74940829000000", "98870419000000", ...]


; --- functions ---
randomDate() {
	random, r_year, 1600, 9999
	random, r_day, 1, 365
	r_date := r_year 01 01
	r_date += r_day, days
	return r_date
}
Took about 14 seconds on my computer.
Last edited by Chunjee on 22 Jan 2022, 19:28, edited 3 times in total.

User avatar
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 22 Jan 2022, 18:30

On my machine:
My method #2 (no year): 6250 ms
flyingDman (has year): 7094 ms
My method #1 (year via Julian): 24546 ms

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

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

Post by amateur+ » 22 Jan 2022, 18:48

mikeyww wrote: On my machine:
My method #2 (no year): 6250 ms
flyingDman (has year): 7094 ms
My method #1 (year via Julian): 24546 ms
And what for sofista's method and modified sofista's?
Remove SetBatchLines, -1 if neccessary.
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
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 22 Jan 2022, 19:06

sofista: 6828 ms
amateur+ modified sofista: 4593 ms :bravo:

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

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

Post by amateur+ » 22 Jan 2022, 19:21

flyingDman's shouldn't lose that much. He gave us two scripts for dates, the second one should be faster. Also SetBatchLines, - 1 should be included by all scripts that compete or none of them.
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
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 22 Jan 2022, 19:27

flyingDman "about as fast": 6187 ms

All batch lines at default. CPU ~94% idle at baseline.

Operating system : Microsoft Windows 10 Pro
OS version : 10.0.19043
Free physical memory : 6650608
Total physical memory : 16744828928
PC model : ASUS EXPERTBOOK B9400CEAV_B9450CEA
CPU : Intel64 Family 6 Model 140 Stepping 1
OS bitness : 64
CPU bitness : 64
BIOS manufacturer : ASUSTeK COMPUTER INC.
BIOS name : B9400CEAV.303
Motherboard manufacturer : ASUSTeK COMPUTER INC.
Motherboard product : B9400CEAV
Motherboard version : 1.0
AHK version : 1.1.33.10

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

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

Post by DanRim » 22 Jan 2022, 19:48

@mikeyww, @amateur+, @sofista, @flyingDman , @Chunjee Thank you all guys for discussion and examples. Your examples are really valuable and allows me to learn more on how to think writing code and you really saved me 120 hours of work. I really appreciate your time and effort, people like you makes life better and happier to those who seeking for help. I hope one day I will be skillful as you and will be able to help others.

p.s I felt like great minds get into competition whose approach is faster. Valuable discussion :)

I will use flyingDman approach, because I can control random range easy, I can control format easy and I am not sure, but script generates random values so fast and stores in clipboard and it is very easy to paste values to excel or .txt doc. Others examples will be studies as well for sure, I need figure out how everything works.

Huge thank you :)

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 » 22 Jan 2022, 20:34

DanRim wrote:
22 Jan 2022, 09:35
I need to generate 432 000 random lines of dummy dates...

This code will generate random dates in Excel cells A1 through A432000 in about 1300 ms on my computer.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.Formula := "=RANDBETWEEN(1,44561)" ; 1/1/1900 to 12/31/2021
xlRange.Value := xlRange.Value ; convert cells formulas to text to not be dynamic (this is the slow part, taking about 1000 ms)

All the other examples appear to be just generating the random dates but not in Excel.

If you don't care that the cells values change randomly every time Excel recalculates its formulas then the line with xlRange.Value := xlRange.Value can be removed which quickens it to about 300 ms.

If you need to get the array back out of Excel, this will do it in 140 ms on my computer

Code: Select all

SafeArray := xlRange.Value
MsgBox % SafeArray[3,1]	;  row 3, column 1 of the SafeArray

FG
Last edited by FanaticGuru on 22 Jan 2022, 20:46, edited 1 time in total.
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
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 22 Jan 2022, 20:37

671 ms here. Lightning! :clap: :crazy: :wtf:

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 » 22 Jan 2022, 21:09

For curiosity:

Code: Select all

xlApp := ComObjActive("Excel.Application")
S := A_TickCount
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.Formula := "=RANDBETWEEN(1,44561)" ; 1/1/1900 to 12/31/2021
SafeArray := xlRange.Value
MsgBox % A_TickCount - S
MsgBox % SafeArray[3,1]	;  row 3, column 1 of the SafeArray

This creates an array of 432,000 random dates in AutoHotkey using Excel as a generator in 420 ms on my computer.

I don't think you could do it that fast with any just AutoHotkey code.

I mean you could use MCode but that is just running C++ code with AutoHotkey. It is also a whole other level of difficulty.

There have been a few times I have used xlApp.WorksheetFunction to access a function from Excel even when the script had nothing to do with Excel because it was too much trouble to replicate the function in AHK.

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 » 22 Jan 2022, 21:17

I do not believe that the OP wanted to put these straight in Excel. Doing it in Excel though is so much more fun!
I was able to shave off a few ms from FG's script on my computer with this:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
sArr := ComObjArray(12, 432000, 1)
loop, 432000
	{
	random, dt, 1, 44561
	sarr[a_index-1,0] := dt
	}
xl.range("a1:a432000").value := sarr
msgbox % a_tickcount - st
328 ms

BTW this takes 109 ms:

Code: Select all

st := a_tickcount
loop, 432000
	{
	random, dt, 1, 44561
	lst := dt "`n"
	}
msgbox % a_tickcount - st
14.3 & 1.3.7

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 » 22 Jan 2022, 23:57

flyingDman wrote:
22 Jan 2022, 21:17
I do not believe that the OP wanted to put these straight in Excel. Doing it in Excel though is so much more fun!
I was able to shave off a few ms from FG's script on my computer with this:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
sArr := ComObjArray(12, 432000, 1)
loop, 432000
	{
	random, dt, 1, 44561
	sarr[a_index-1,0] := dt
	}
xl.range("a1:a432000").value := sarr
msgbox % a_tickcount - st
328 ms

BTW this takes 109 ms:

Code: Select all

st := a_tickcount
loop, 432000
	{
	random, dt, 1, 44561
	lst := dt "`n"
	}
msgbox % a_tickcount - st

OP seemed happy to get them in the clipboard and then paste them to Excel and with Excel in the topic name, it seemed getting them in Excel was the point.

But the original point is kind of irrelevant as it has evolved more into the fastest way to get an array of 432,000 random dates in AHK.

The above examples are just getting you random numbers not actual dates like "8/6/1987", "2/29/2000", etc. accounting for leap years and other oddities. You don't end up with an array of dates in AHK.

I was cheating a little bit because I noticed on testing again, I was assuming the cells in Excel were already formatted for "Date" because they were from my experimenting. But you would really need to formate the range to "Date" like this:

Code: Select all

xlApp := ComObjActive("Excel.Application")
S := A_TickCount
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.numberFormat := "mm/dd/yyyy;@"
xlRange.Formula := "=RANDBETWEEN(1,44561)" ; 1/1/1900 to 12/31/2021
SafeArray := xlRange.Value
MsgBox % A_TickCount - S
MsgBox % SafeArray[3,1]	;  row 3, column 1 of the SafeArray
That comes in at about 460 ms for me.

If you generate the random numbers straight into the SafeArray and then put into Excel and then back out to AHK to have actual dates:

Code: Select all

xlApp := ComObjActive("Excel.Application")
S := A_TickCount
xlRange := xlApp.ActiveSheet.Range("A1:A432000")
xlRange.numberFormat := "mm/dd/yyyy;@"
SafeArray := ComObjArray(12, 432000, 1)
loop, 432000
	{
	Random, dt, 1, 44561
	SafeArray[a_index-1,0] := dt
	}
xlRange.Value := SafeArray
SafeArray := xlRange.Value
MsgBox % A_TickCount - S
MsgBox % SafeArray[3,1]	;  row 3, column 1 of the SafeArray
This is a little slower at 860 ms for me. Seems better to let Excel generate the random numbers.

Even with the formatting of the range, 460 ms seems pretty hard to beat for getting an array of actual dates in AHK. Maybe there is an efficient formula for accounting for leap years and stuff while still giving every date the same probability. Some of the examples calculating a random month then a random day in the month gives skewed probability distribution as the odds of getting a day in February is less than the odds of getting a day in January. Plus since you are formatting the cells anyways, you could format the dates in the array anyway you want.

Heck, it might be good for other formatting, like getting an array of fractions, "1/2", "3/4", "7/8", etc. Although even with rounding rules that is considerable easier than calculating dates. Dates seem the hardest to calculate but you could create some pretty complex custom formatting in Excel.

After a little thought, here is a purely AHK approach that is pretty good but still slower than using Excel.

Code: Select all

S := A_TickCount
Array := {}
FormatTime, BaseDate, 19000101, yyyyMMdd
loop, 432000
	{
	Random, dt, 1, 44561
	Date := BaseDate
	Date += dt, Days
	FormatTime, XDate, %Date%, MM/dd/yyyy
	Array[A_Index] := XDate
	}
MsgBox % A_TickCount - S
MsgBox % Array[3]
About 3,000 ms for me. This gives the advantage of formatting the dates anyway you want but it can impact speed. Long dates are much slower than short dates.

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
mikeyww
Posts: 26588
Joined: 09 Sep 2014, 18:38

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

Post by mikeyww » 23 Jan 2022, 06:56

Those are great points, and it's interesting to see how fast Excel is. Earlier in the thread, the "base date" approach was used by sofista for individual years, and then by amateur+ more broadly.

Post Reply

Return to “Ask for Help (v1)”