I mean, you can try to use VBA, but it will be rather slow and Excel may not be able to handle the volume (we have many millions of rows in our event tables, for example). Better use something more robust.
To give you an idea of the required computation here is the scalar-value SQL function:
CREATE FUNCTION [SFDC].[OpportunityStatus]
(
-- Add the parameters for the function here
@ID as varchar(20)
,@TimeStamp as datetime
,@CreatedDate as datetime
,@Field as varchar(50)
,@CurrentValue as varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @TimeStampValue as varchar(255)
SELECT @TimeStampValue=
-- return NULL when the opportunity was not alive at the time
case when @TimeStamp < @CreatedDate then NULL
else isnull((SELECT TOP 1 [New Value] FROM [sfdc].[OpportunityFieldChanges]
WHERE [Opportunity Id] = @ID AND [Field Event] = @Field AND [Edit Date] <= @TimeStamp
order by [Edit Date] desc, [History ID] COLLATE SQL_Latin1_General_CP437_BIN ) -- any change data before the snapshot? [Edit Date] desc or [History ID] ?
,isnull((SELECT TOP 1 [Old Value] FROM [sfdc].[OpportunityFieldChanges]
WHERE [Opportunity Id] = @ID AND [Field Event] = @Field AND [Edit Date] > @TimeStamp
order by [Edit Date],[History ID] COLLATE SQL_Latin1_General_CP437_BIN desc) -- any change data after the snapshot?
,@CurrentValue)) -- use the current data from SFDC if no changes have been recorded
end
-- Return the result of the function
RETURN @TimeStampValue
END
