In Excel, you can grab data from an external database. In Excel 2002, it is through Data/Import External Data. In Excel 2007, it is through Data/From Other Sources. I can connect to the Financial Edge database, but I am not familiar enough with the table structure to find the data I am looking for.
I want to build a spreadsheet that grabs the following data:
Account # Description Jan Feb Mar Apr etc Total
410-10-17 Wages $50 $70 $65 $45
456-10-17 Office Supplies $10 $20 $25 $30
etc
Then I can refresh the data whenever I want and get it automatically in Excel. I then use that data for various analyses. I can also filter by account number for manual reconciliations...it automatically grabs the GL balance for me, so I don't have to look it up each time.
Does anyone know the tables and fields where I can find this data?
Thanks for your help,
Mark
I am close, but not quite there yet.
The table "GLViewForGetAcctBegBalByPeriod" contains some useful information. I grab the Account Number, Debit, Credit, GL7FiscalPeriodsID, and other data. I then add a column to add the Debit and subtract the Credit to get the net activity for the month. I also use the table GL7FiscalPeriods to convert the GL7FiscalPeriodsID to a date.
Using that data, I build a Pivot Table with Account numbers down the left side and month-end dates accross the top. The net activity is in the body of the pivot table. This gives me the chart described above. At the end of each month, I can then look at the history of all accounts and question anything that looks too high or too low.
Now for the problem - This only gives me data for years that have been closed. That's useless. I need current information.
Does anyone know what table holds the current data?
Thanks