Ahmed
--- Will get back to U soon.
JN
--- I don't know why the company details are not showing. What U can do is manually load them into a table. Using SQL, update company table.
I understand speed is an issue. So lets follow another technique.
Let me put in steps.
1. Make copy of Fcharts. All the below steps apply for this copy.
2. Convert to Access Database.
3. Every Day, insert new daily data using FCharts.
4. Run Sector Data Update Macro (U don't have to close FCharts). This will insert all the sector group data.
5. Using FCharts, export data for today. This is Ur total daily File.
Load the total daily file to Original FCharts, which has the binary database. So, there is Ur speed. What U say?
Create Select Query: SectorDataUpdate
SELECT Sector AS code, Prices.date, cstr(datediff('d','12/30/1899',Prices.date)) & Sector AS UID, sum(open) AS SOpen, sum(close) AS SClose, sum(low) AS SLow, sum(high) AS SHigh, sum(volume) AS SVolume, sum(timedata) AS STimedata
FROM Companies INNER JOIN Prices ON Companies.Code=Prices.Code
WHERE sector NOT IN ("-","")
and Prices.date = date()
GROUP BY Sector, Prices.date;
Create Select Query: SectorDataUpdateGroup
SELECT Sector AS code, Prices.date, cstr(datediff('d','12/30/1899',Prices.date)) & Sector AS UID, sum(open) AS SOpen, sum(close) AS SClose, sum(low) AS SLow, sum(high) AS SHigh, sum(volume) AS SVolume, sum(timedata) AS STimedata
FROM Companies INNER JOIN Prices ON Companies.Code=Prices.Code
WHERE Sector in ("AUTOMOBILES","CHEMICALS","COMPUTERS","TELECOMUTE","TEXTILES")
and Prices.date = date()
GROUP BY Sector, Prices.date;
Create Insert Query: DailySectorInsert
INSERT INTO Prices ( Code, [Date], UID, [Open], [Close], Low, High, Volume, TimeData )
SELECT sectordataupdate.code, sectordataupdate.date, sectordataupdate.UID, sectordataupdate.SOpen, sectordataupdate.SClose, sectordataupdate.SLow, sectordataupdate.SHigh, sectordataupdate.SVolume, sectordataupdate.STimedata
FROM sectordataupdate;
Create Insert Query: DailySectorInsertGroup
INSERT INTO Prices ( Code, [Date], UID, [Open], [Close], Low, High, Volume, TimeData )
SELECT sectordataupdategroup.code, sectordataupdategroup.date, sectordataupdategroup.UID, sectordataupdategroup.SOpen, sectordataupdategroup.SClose, sectordataupdategroup.SLow, sectordataupdategroup.SHigh, sectordataupdategroup.SVolume, sectordataupdategroup.STimedata
FROM sectordataupdategroup;
Create a macro: DailySectorInsertMacro
SetWarnings (value = No)
OpenQuery (Drag DailySectorInsert query. This will create OpenQuery item)
SetWarnings (No)
OpenQuery (Drag DailySectorInsertGroup query. This will create OpenQuery item)
Once U create a macro, right click on it and create shortcut wherever U want it.
--- Will get back to U soon.
JN
--- I don't know why the company details are not showing. What U can do is manually load them into a table. Using SQL, update company table.
I understand speed is an issue. So lets follow another technique.
Let me put in steps.
1. Make copy of Fcharts. All the below steps apply for this copy.
2. Convert to Access Database.
3. Every Day, insert new daily data using FCharts.
4. Run Sector Data Update Macro (U don't have to close FCharts). This will insert all the sector group data.
5. Using FCharts, export data for today. This is Ur total daily File.
Load the total daily file to Original FCharts, which has the binary database. So, there is Ur speed. What U say?
Create Select Query: SectorDataUpdate
SELECT Sector AS code, Prices.date, cstr(datediff('d','12/30/1899',Prices.date)) & Sector AS UID, sum(open) AS SOpen, sum(close) AS SClose, sum(low) AS SLow, sum(high) AS SHigh, sum(volume) AS SVolume, sum(timedata) AS STimedata
FROM Companies INNER JOIN Prices ON Companies.Code=Prices.Code
WHERE sector NOT IN ("-","")
and Prices.date = date()
GROUP BY Sector, Prices.date;
Create Select Query: SectorDataUpdateGroup
SELECT Sector AS code, Prices.date, cstr(datediff('d','12/30/1899',Prices.date)) & Sector AS UID, sum(open) AS SOpen, sum(close) AS SClose, sum(low) AS SLow, sum(high) AS SHigh, sum(volume) AS SVolume, sum(timedata) AS STimedata
FROM Companies INNER JOIN Prices ON Companies.Code=Prices.Code
WHERE Sector in ("AUTOMOBILES","CHEMICALS","COMPUTERS","TELECOMUTE","TEXTILES")
and Prices.date = date()
GROUP BY Sector, Prices.date;
Create Insert Query: DailySectorInsert
INSERT INTO Prices ( Code, [Date], UID, [Open], [Close], Low, High, Volume, TimeData )
SELECT sectordataupdate.code, sectordataupdate.date, sectordataupdate.UID, sectordataupdate.SOpen, sectordataupdate.SClose, sectordataupdate.SLow, sectordataupdate.SHigh, sectordataupdate.SVolume, sectordataupdate.STimedata
FROM sectordataupdate;
Create Insert Query: DailySectorInsertGroup
INSERT INTO Prices ( Code, [Date], UID, [Open], [Close], Low, High, Volume, TimeData )
SELECT sectordataupdategroup.code, sectordataupdategroup.date, sectordataupdategroup.UID, sectordataupdategroup.SOpen, sectordataupdategroup.SClose, sectordataupdategroup.SLow, sectordataupdategroup.SHigh, sectordataupdategroup.SVolume, sectordataupdategroup.STimedata
FROM sectordataupdategroup;
Create a macro: DailySectorInsertMacro
SetWarnings (value = No)
OpenQuery (Drag DailySectorInsert query. This will create OpenQuery item)
SetWarnings (No)
OpenQuery (Drag DailySectorInsertGroup query. This will create OpenQuery item)
Once U create a macro, right click on it and create shortcut wherever U want it.
Last edited:
