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
amateur+
Posts: 655
Joined: 09 Oct 2021, 15:43

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

Post by amateur+ » 23 Jan 2022, 10:56

It a real cheat with excel approach! Just wow! Also it is nice idea sometimes to use its functions instead of inventing a wheel :).
As I said here, FormatTime, XDate, %Date%, MM/dd/yyyy is a poor choice that significantly loses in perfomance to Array[A_Index] := SubStr(date, 5, 2) "/" SubStr(date, 7, 2) "/" SubStr(date, 1, 4).
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 » 23 Jan 2022, 13:18

amateur+ wrote:
23 Jan 2022, 10:56
FormatTime, XDate, %Date%, MM/dd/yyyy is a poor choice that significantly loses in perfomance to Array[A_Index] := SubStr(date, 5, 2) "/" SubStr(date, 7, 2) "/" SubStr(date, 1, 4).
Not here!. I ran the code below 12 times. FormatTime beats Substr every time with an average of 304 ms vs 697. More than twice as fast.

Code: Select all

cnt := 432000

st := A_TickCount
loop, %cnt% 
	{
	random, var, 19000101, 20000101
	lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
	}
msgbox % a_tickcount - st
	
lst := "" 

st := A_TickCount
loop, % cnt 
	{
	random, var, 19000101, 20000101
	FormatTime,dt, %var%, ShortDate
	lst .= dt "`n"
	}
msgbox % a_tickcount - st
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 » 23 Jan 2022, 13:25

FanaticGuru wrote:
22 Jan 2022, 23:57
Seems better to let Excel generate the random numbers.
Marginally, but your comparison is not entirely fair. Your code results in 432,000 cells with formulas. Converting them back to dates using xlRange.Value := xlRange.Value adds a full second or more!
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+ » 23 Jan 2022, 13:35

@flyingDman, we should generate valid dates. If we do this then sofista wins.
FormatTime,dt, %var%, ShortDate saves some microseconds when it quickly assigns blank to dt in case of invalid dates (a huge majority of your generated dates are invalid, so it saves a lot). But if you regenerate after these cases to get exactly 432000 valid dates, it will waste a tonne of time.

Code: Select all

cnt := 432000
list := ""
maxdays := A_Now
maxdays -= 16010101, Days
st := A_TickCount
loop, %cnt% 
	{
	random, days, 0, % maxdays
	var := 16010101
	var += days, Days
	lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
	}
msgbox % a_tickcount - st
	
lst := "" 

st := A_TickCount
loop, % cnt 
	{
	random, days, 0, % maxdays
	var := 16010101
	var += days, Days
	FormatTime,dt, %var%, ShortDate
	lst .= dt "`n"
	}
msgbox % a_tickcount - st
return
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 » 23 Jan 2022, 14:24

@amateur+ The issue was whether FormatTime is slower or faster than the SubString approach. My tests show that FormatTime is faster.
In my other scripts I validate the dates using If var is date. Yes, it weeds out non-dates, but the script generates 432,000 valid random dates and that is what counts.
My latest attempt for a non-Excel solution follows. Using a safearray seems to be a bit faster than generating a list. In addition, it makes it easy to later paste in Excel (using xl.range("a1.a432000").value := safearray). I also got rid of the FormatTime as I already have yr, mo, and dy.

Code: Select all

st := A_TickCount
cnt := 0, sArr := ComObjArray(12, 432000, 1)
loop
	{
	random, yr, 1900, 2050
	random, mo, 1, 12
	random, dy, 1, 31
	var := yr . SubStr("0" . mo, -1) . SubStr("0" . dy, -1)
	if var is date
		sarr[++cnt-1,0] := mo "/" dy "/" yr
	if (cnt = 432000)
		break
	}
MsgBox % A_TickCount - st
msgbox % sarr[243,0]
It runs in 516 ms to 532 ms.
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+ » 23 Jan 2022, 14:26

flyingDman wrote:
23 Jan 2022, 14:24
My tests show that FormatTime is faster.
For valid dates it is obviously wrong.
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.

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

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

Post by amateur+ » 23 Jan 2022, 14:46

Here is how we check on validity and get 432 000 instead of 43 200 valid dates:

Code: Select all

cnt := 432000
i := 0
st := A_TickCount
loop
{	random, var, 19000101, 20000101
	if var is time
	{	lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
		if (i++ = cnt)
			break
	}
}
msgbox % "SubStr approach: " (ssap := a_tickcount - st)
	
lst := "" 
i := 0
st := A_TickCount
loop
{	random, var, 19000101, 20000101
	if var is time
	{	FormatTime,dt, %var%, ShortDate
		lst .= dt "`n"
		if (i++ = cnt)
			break
	}
}
msgbox % "FormatTime approach: " a_tickcount - st "`n`nSubStr approach was: " ssap
return
If you get FormatTime approach faster in some iterations, it is just a pure luck of generating valid dates way more often. But if we generate valid dates from the begining, then SubStr will be faster every time.
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.

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

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

Post by amateur+ » 23 Jan 2022, 15:05

And here is a pure test "SubStr vs FormatTime" without generating. We just take a constant valid date and run two loops:

Code: Select all

cnt := 432000
var := 20010101
st := A_TickCount
loop, % cnt
	lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
ssap := a_tickcount - st
	
lst := "" 
st := A_TickCount
loop, % cnt
{	FormatTime,dt, %var%, ShortDate
	lst .= dt "`n"
}
msgbox % "SubStr approach:`t`t" ssap  "`n`nFormatTime approach:`t" a_tickcount - st
return
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 » 23 Jan 2022, 16:35

Earlier you said "[FormatTime] is slow. This method [i.e. substr] is much faster".
In you last post the Substr method showed a time 594 ms while the FormatTime method is 625 ms. In other words a whopping 31 ms faster. This does not move the needle much... In addition abstracting from date validation but using randomly generated dates, my test here remains valid and shows much more favorable results for FormatTime.
That said, as my last script avoids FormatTime altogether, it might be a moot point here.
14.3 & 1.3.7

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

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

Post by just me » 24 Jan 2022, 06:09

DanRim wrote:
22 Jan 2022, 09:35
Hello,
...

Code: Select all

...
varr := incommetDay . "." . incommetMonth . ".2022 " . incommetH . ":" . incommetMin . ":" . incommetSec
Clipboard := varr
Just another one:

Code: Select all

#NoEnv
SetBatchLines, -1

MinDate := "20220101"
MaxDate := "20221231"

MaxSecs := MaxDate
MaxSecs -= MinDate, S

DateArr := []
DateArr.SetCapacity(432000)

S := A_TickCount
Loop, 432000 {
   Random, Secs, 0, %MaxSecs%
   Date := MinDate
   Date += %Secs%, S
   ; FormatTime, Date, %Date%, dd.MM.yyyy HH:mm:ss
   ; DateArr.Push(Date)
   DateArr.Push(RegExReplace(Date, "(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)", "$3.$2.$1 $4:$5:$6"))
}
T := A_TickCount - S

MsgBox, % DateArr.Length() . " in " . T . " ms!"
MsgBox, % DateArr[25]
As a result of my test RegExReplace() is faster than FormatTime and also SubStr().

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

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

Post by amateur+ » 24 Jan 2022, 08:36

A pure test "SubStr vs RegExReplace" on dates without hhmmss. On my computer RegExReplace is slower.
Here are results of 10 consecutive runs:
Spoiler
Code:

And here is a pure test "SubStr vs FormatTime". I don't know how had it happened, but FormatTime managed to win in 1 of 10 consecutive runs. I expected zero.
Spoiler
Code:
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.

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

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

Post by just me » 24 Jan 2022, 09:08

@amateur+: With your slightly changed test script the difference is maximum 3% here:

Code: Select all

#NoEnv
SetBatchLines, -1
cnt := 432000
var := 20010101
Sleep, 1
st := A_TickCount
loop, % cnt
	lst .= RegExReplace(var, "(\d{4})(\d\d)(\d\d)", "$3.$2.$1") "`n"
rrap := a_tickcount - st
lst := ""
Sleep, 1
st := A_TickCount
loop, % cnt
	lst .=  SubStr(var, 1, 4) "/" SubStr(var, 5, 2) "/" SubStr(var, 7, 2) "`n"
ssap := a_tickcount - st
Gbox("SubStr approach:`t`t" ssap  "`nRegExReplace approach:`t" rrap
	. "`nDelta is " (delta := rrap-ssap) "ms = " Round(delta/rrap*100, 1) "% (" (delta>0?"SubStr":"RegExReplace") " is much faster)")
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
	ExitApp
}
BTW: The originally requested format is dd.MM.yyyy HH:mm:ss.

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

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

Post by amateur+ » 24 Jan 2022, 09:20

@flyingDman
flyingDman wrote:
23 Jan 2022, 16:35
Earlier you said "[FormatTime] is slow. This method [i.e. substr] is much faster".
In you last post the Substr method showed a time 594 ms while the FormatTime method is 625 ms. In other words a whopping 31 ms faster.
You know, in one of my runs FormatTime has even won! Maybe "SubStr is much faster" wasn't a good phrase. I think "FormatTime is consistently slower" would sound better.
flyingDman wrote: In addition abstracting from date validation but using randomly generated dates, my test here remains valid and shows much more favorable results for FormatTime.
You are too smart and experinced to make such mistakes. It is quite strange. That not you explain it to me but I have to explain it to you.
Lets do you again.
Frankly speaking, I would just repeat this my quote:
Spoiler
... but as soon as you won't read it again and try to comprehend I really should tell the same with other words.
FormatTime indeed sets blank those vars whose YYYYMMDDHH24MISS prototype was invalid (is not time). If you don't believe me, then just go and run a test. If you want to take 432000 prototypes that contain 90% invalid dates and compete with SubStr then it is simply a nonsense it shows nothing. SubStr composes strings like 1951/35/50 (yes, these are invalid dates, but SubStr composes them and then concatenates to lst and wastes time) while FormatTime composes empty strings. It is really a nonsense to compete this way. Of course to compose empty strings costs nothing and FormatTime wins in that trully irrelevant test. What else did you expect? This way you will beat even lst .= var "`n" and even lst .= var.
Here is what SubStr approach generates:
Spoiler

And here is how FormatTime fools you:
Spoiler

But if you run any fair test with valid dates (that won't allow to FormatTime cheat with composition of empty strings) then you will see the reality: who it fast and who is slower.
That said, as my last script avoids FormatTime altogether, it might be a moot point here.
We're discussing SubStr vs FormatTime at the moment. As soon as we know that Excel is a total champion.

EDIT: Fixed a misleading typo: "Then" was fixed to -> "That" in wrong "Then not you explain it to me but I have to explain it to you".
Last edited by amateur+ on 26 Jan 2022, 09:18, edited 2 times in total.
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.

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

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

Post by amateur+ » 24 Jan 2022, 09:27

just me wrote: @amateur+: With your slightly changed test script the difference is maximum 3% here:[/c].
@just me, thanks for the correction, I will know this nuance for the future!
just me wrote: BTW: The originally requested format is dd.MM.yyyy HH:mm:ss.
Oh, God! You're right...
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
boiler
Posts: 16768
Joined: 21 Dec 2014, 02:44

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

Post by boiler » 24 Jan 2022, 10:22

amateur+ wrote: @just me, thanks for the correction, I will know this nuance for the future!
Perhaps you will remember that anyone can make mistakes the next time you are about to reply to a respected forum member like this:
amateur+ wrote: You are too smart and experinced to make such mistakes. It is quite strange. Then not you explain it to me but I have to explain it to you.

I would hesitate to point that out, but such off-putting remarks seem to be a habit with you.

RussF
Posts: 1242
Joined: 05 Aug 2021, 06:36

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

Post by RussF » 24 Jan 2022, 10:50

@amateur+: I thought that my explanation was so clear that even a child wouldn't have any problems in understanding, but nope...
Yup, shut me down in an earlier thread.

Russ

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

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

Post by amateur+ » 24 Jan 2022, 11:36

boiler wrote: Perhaps you will remember that anyone can make mistakes the next time you are about to reply to a respected forum member like this:
I would say anyone MAKES mistakes. Every day. If you think that I'm ashamed of doing mistakes and don't love to admit them then you've just made another one.
But what I would be ashamed of is to deny and repeat my mistakes if I'm competent enough already to see them and people honored me with multiple pointing out to them with explanations, examples, tests, with more explanations, more examples, more tests...
And when I see that around I may often become slightly irritated, because it looks like something impossible, something that is hard to understand.
It is obvious that flyingDman is too competent to demonstrate a lack in comprehending what he was told and shown multiple times from different sides. He isn't a newcomer (like me) with several months of membership and a hundred of posts who may ask something like how to run Paint after pressing PrintScreen button trice. So it was strange, so that made me a little bit irritated because it was me with him in that discussion. And what? And I had told that! That it was strange. And that being obviously smart and experienced he should had seen his obvious simple for his competence mistake a long time ago after he had got initial explanations or a couple.
Was I right? Most likely not. I need to work at myself. Since I write it here and those my words are in your quote, there isn't much sense in deleting them.

And btw, boiler, just don't use approaches like this one:
Spoiler
... and with quite high probability you won't get something similar back to your side of the court.

@RussF, sorry for that post, I was wrong. My explanations weren't clear. And even if they would be clear, you were a newcomer. And even if you wouldn't be a newcomer, that was too rude and disrespectful. I was irritated by that your "negative number" incomprehension that I had lost control like a child. And also I was rude to the topic starter.
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 » 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 :)
just me wrote:
24 Jan 2022, 06:09
DanRim wrote:
22 Jan 2022, 09:35
Hello,
...

Code: Select all

...
varr := incommetDay . "." . incommetMonth . ".2022 " . incommetH . ":" . incommetMin . ":" . incommetSec
Clipboard := varr
Just another one:

Code: Select all

#NoEnv
SetBatchLines, -1

MinDate := "20220101"
MaxDate := "20221231"

MaxSecs := MaxDate
MaxSecs -= MinDate, S

DateArr := []
DateArr.SetCapacity(432000)

S := A_TickCount
Loop, 432000 {
   Random, Secs, 0, %MaxSecs%
   Date := MinDate
   Date += %Secs%, S
   ; FormatTime, Date, %Date%, dd.MM.yyyy HH:mm:ss
   ; DateArr.Push(Date)
   DateArr.Push(RegExReplace(Date, "(\d{4})(\d\d)(\d\d)(\d\d)(\d\d)(\d\d)", "$3.$2.$1 $4:$5:$6"))
}
T := A_TickCount - S

MsgBox, % DateArr.Length() . " in " . T . " ms!"
MsgBox, % DateArr[25]
As a result of my test RegExReplace() is faster than FormatTime and also SubStr().

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 » 24 Jan 2022, 18:33

@DanRim Could you provide an example of what the final output should look like in Excel?
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 » 24 Jan 2022, 19:01

Here is my fastest non-Excel approach for getting 432,000 dates into an AHK array.

Code: Select all

SetBatchLines, -1 ; makes all scripts faster but used mainly just to get more consistant results
S := A_TickCount
Array := {}
BaseDate := "19000101"
loop, 432000
{
	Random, dt, 1, 44561
	Date := BaseDate
	Date += dt, Days
	Array[A_Index] := SubStr(Date, 1, 4) "/" SubStr(Date, 5, 2) "/" SubStr(Date, 7, 2)
}
MsgBox % A_TickCount - S
MsgBox % Array[3]
This comes in at 641 ms for me but it is all relative. This was the fastest approach on my computer.

Things I noticed:
  • Date := BaseDate faster than Date := "19000101" or Date = 19000101
  • Array[A_Index] faster than Array.Push
  • RegExReplace faster than FormatTime
  • SubStr faster than RegExReplace
  • Noticed no difference with SetCapacity
  • Noticed no difference with Array := ComObjArray(12, 432000, 1)
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

Post Reply

Return to “Ask for Help (v1)”