|
> General / Reports and documents / Excel Integration - on premise only / Excel Integration parameters - on premise only
|
/
More
|
Use the functions list in Excel to customise the information seen on your spreadsheet. Sage have created a number of special functions for use so that you can retrieve additional accounts information from your Sage 200 program.
|
Function name |
Ledger |
Parameters |
|||
|---|---|---|---|---|---|
|
SgAccAlternativeStock |
Stock |
Returns the alternative stock code of the specified stock item. |
|||
|
SgAccAlternativeStockList |
Stock |
StockCode |
Provides a drop-down list of alternative items for the specified stock code. |
Drop-down |
|
|
SgAccBankAccountNameList |
Cash Book |
Returns a drop-down list of bank account names belonging to the company. |
Drop-down |
||
|
SgAccBankBalance |
Cash Book |
AccountName |
Returns the bank balance for the selected account. |
Real |
Enter the bank account name, e.g. Main Bank Account. |
|
SgAccBankLedgerPeriod |
Cash Book |
Returns the current ledger period number for the Cash Book. |
Integer |
||
|
SgAccBankLedgerPeriodYear |
Cash Book |
Returns the current ledger year for the Cash Book. |
String |
||
|
SgAccBankNominalLedgerAccountNumber |
Nominal Ledger |
AccountName |
Returns the nominal account number of a bank account. |
Integer |
Enter the bank account name, e.g. Main Bank Account. |
|
SgAccCategoryAccountBalance |
Nominal Ledger |
Category |
Returns the total balance for a nominal ledger category type, e.g. Sales. |
Real |
Nominal ledger report category description |
|
SgAccCategoryPeriodActualBalance |
Nominal Ledger |
CategoryDesc Period |
Returns the year-to-date balance for a nominal account category up to, and including the period specified. |
Single |
Category Desc - Nominal ledger report category description Period - Financial period, e.g. 1,2,3...12. |
|
SgAccCategoryYearToDateActualBalance |
Nominal Ledger |
CategoryDesc Period |
Returns the year-to-date balance for a nominal account category, up to and including the selected period. |
Single |
Category Desc - Nominal ledger report category description. Period - Financial period, e.g. 1,2,3...12. |
|
SgAccCompanyName |
Misc |
Returns the company name of a customer. |
String |
||
|
SgAccCostPrice |
Stock |
Returns the cost price for the specified stock item. |
|||
|
SgAccCustomerAddress |
Sales |
Customer LineNumber |
Returns the customer's address for the specified line number. |
String |
|
|
SgAccCustomerAgedBalance |
Sales |
Calculates the aged balance for a customer. |
|||
|
SgAccCustomerBalance |
Sales |
Customer |
This returns the balance for the specified year and period from the customer's Costcentre history. |
Integer |
|
|
SgAccCustomerContact |
Sales |
Customer |
Returns the customer's contact name. |
String |
|
|
SgAccCustomerCostCentre |
Sales |
Customer |
Returns the default cost centre for a specified customer. |
String |
|
|
SgAccCustomerCreditLimit |
Sales |
Customer |
Returns the customer's credit limit. |
||
|
SgAccCustomerCurrency |
Sales |
Customer |
Returns the customer's default currency. |
||
|
SgAccCustomerCurrentCredit |
Sales |
AccountNumber |
Returns the credit limit for the selected customer account number. |
Integer |
|
|
SgAccCustomerDelAddress |
Sales |
Customer LineNumber AddressName |
Returns the customer's delivery address line for a specified address name. |
String |
|
|
SgAccCustomerDelPostcode |
Sales |
Customer AddressName |
Returns the post code of a customer's delivery address for the specified address name. |
String |
|
|
SgAccCustomerDepartment |
Sales |
Customer |
Returns the default department of a specified customer. |
String |
|
|
SgAccCustomerEmailAddress |
Sales |
Customer |
Returns the customer's e-mail address for the specified customer. |
String |
|
|
SgAccCustomerFax |
Sales |
Customer |
Returns the fax number of a specified customer. |
String |
|
|
SgAccCustomerMargin |
Sales |
Customer |
Returns the margin for the specified year and period from the customer's CostCentre history |
||
|
SgAccCustomerList |
Sales |
Customer |
Provides a drop-down list of customers linked to the company. |
Drop-down |
|
|
SgAccCustomerName |
Sales |
Customer |
Returns the company name of a customer. |
String |
|
|
SgAccCustomerNetSales |
Sales |
Customer |
Returns the net sales for the specified year and period from the customer's CostCentre history. |
Integer |
|
|
SgAccCustomerNominalLedger |
Sales |
Customer |
Returns the default nominal code for a specified customer. |
String |
|
|
SgAccCustomerOrders |
Sales |
AccountNumber Optional: Year Optional: Period |
Returns the number of orders taken from a customer in a selected year and month. |
Integer |
Account Number: e.g. ABB001 Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccCustomerOrderValue |
Sales |
Customer Optional: Year Optional: Period |
Returns the value of orders taken from a customer in a selected year and month. |
Real |
Account Number: e.g. ABB001 Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccCustomerPaymentDays |
Sales |
Customer |
Returns the number of days available to a customer before payment is due. |
Integer |
|
|
SgAccCustomerPhone |
Sales |
Customer |
Returns the telephone number for the specified customer. |
String |
|
|
SgAccCustomerPostcode |
Sales |
Customer |
Returns the postcode for a specified customer. |
String |
|
|
SgAccCustomerSettlementDays |
Sales |
Customer |
Returns the early settlement period, in days, for a specified customer. |
Integer |
|
|
SgAccCustomerTaxCode |
Sales |
Customer |
Returns the tax code for the specified customer. |
String |
|
|
SgAccFreeStock |
Stock |
ProductCode |
Returns the quantity of free stock for a product (quantity in stock minus quantity allocated). |
Single |
e.g. CA/BASE/SNG/BEECH |
|
SgAccFunctionsVersion |
Misc |
Returns the version number of the Sage Excel add-in. |
String |
||
|
SgAccNominalCodeList |
Nominal Ledger |
Record Index |
Returns a list of nominal codes. |
Drop-down |
|
|
SgAccNominalLedgerAccountCode |
Nominal Ledger |
AccountName Optional: DetailsReturned |
Returns the nominal ledger account code, cost centre or department as a single line of numbers and text. |
String |
Account Name: The nominal ledger account name, e.g. Sales - Bespoke Kitchens. Details Returned: 0 or blank - value returned = AccountCode 1 - value returned = CostCentre, e.g. 31100 2 - value returned = Department, e.g. SAL. 3 - value returned = Account/CC/Dept, e.g. 31100/SAL/BES. |
|
SgAccNominalLedgerActual |
Nominal Ledger |
Year Optional: Period Optional: Currency NominalLedgerCode Optional: CostCentre Optional: Department |
Returns the actuals value for the selected nominal account in the year, period and currency. |
Real |
|
|
SgAccNominalLedgerBudget |
Nominal Ledger |
Year Optional: Period Optional: Currency NominalLedgerCode Optional: CostCentre Optional: Department |
Returns the budget value for the selected nominal account in the year, period and currency. |
Real |
|
|
SgAccNominalLedgerCostCentreCredit |
Nominal |
Returns the Nominal Ledger credit value for the specified currency, year, period, NominalLedgerCode and CostCentre. |
Integer |
||
|
SgAccNominalLedgerCostCentreDebit |
Nominal |
Returns the Nominal Ledger debit value for the specified currency, year, period, NominalLedgerCode and CostCentre. |
Integer |
||
|
SgAccNominalLedgerCostCentreName |
Nominal |
Returns the name of the specified cost centre. |
String |
||
|
SgAccNominalLedgerCredit |
Nominal |
Returns the Nominal Ledger credit value for the specified currency, year, period and NominalLedgerCode. |
Integer |
||
|
SgAccNominalLedgerDebit |
Nominal |
Returns the Nominal Ledger debit value for the specified currency, year, period and NominalLedgerCode. |
Integer |
||
|
SgAccNominalLedgerName |
Nominal |
NominalLedgerCode CostCentre Department |
Returns the name of the nominal ledger account for the specified account code, cost centre and department. |
String |
|
|
SgAccNominalNameList |
Nominal Ledger |
Record Index |
Returns a list of nominal account names. |
Drop-down |
|
|
SgAccNominalLedgerPeriod |
Nominal Ledger |
Returns the current ledger period number for the nominal ledger. |
Integer |
||
|
SgAccNominalLedgerPeriodYear |
Nominal Ledger |
Returns the current ledger year for the nominal ledger. |
String |
||
|
SgAccNominalLedgerTotalBalance |
Nominal Ledger |
NominalCode Optional: CostCentre Optional: Department |
Returns the current total balance for a nominal ledger account, including balances carried forward from the prior year. |
Real |
Nominal Code: The nominal Ledger account name, e.g. 31100. Cost Centre: Cost centre code, e.g. SAL. Department Code: The department code, e.g. BES. |
|
SgAccNominalLedgerYearBalance |
Nominal Ledger |
AccountName |
Returns the balance for a nominal ledger account for the current financial year. |
Real |
The nominal ledger account number, e.g. Sales - Bespoke Kitchens. |
|
SgAccNumberOfBankAccounts |
Cash Book |
Returns the number of bank accounts for the company. |
Integer |
||
|
SgAccNumberOfCustomers |
Customer |
Returns the number of customers listed in the company records. |
Integer |
||
|
SgAccNumberOfNominalAccounts |
Nominal Ledger |
Returns the number of nominal accounts for the company. |
Integer |
||
|
SgAccNumberOfNominalTransactions |
Nominal Ledger |
NominalCode Optional: CostCentre Optional: Department |
Returns the number of nominal transactions for a nominal ledger account. |
Integer |
Nominal Code: The nominal Ledger account name, e.g. 31100. Cost Centre: Cost centre code, e.g. SAL. Department Code: The department code, e.g. BES. |
|
SgAccNumberOfStockItems |
Stock |
Returns the number of stock items listed for the company. |
Integer |
||
|
SgAccNumberOfSuppliers |
Suppliers |
Returns the number of suppliers listed for the company. |
Integer |
||
|
SgAccPeriodPL |
Nominal |
Period |
Returns the retained profit value for the year up to, and including, the selected period. |
Single |
Financial period, e.g. 1,2,3...12. |
|
SgAccPeriodRate |
Misc |
Currency |
Returns the exchange rate used for the specified currency. |
Real |
|
|
SgAccProductSalesValue |
Nominal Ledger |
ProductCode Optional: Year Optional: Period |
Returns the value of sales for a product for a selected year and month. |
Single |
Product Code: e.g. PM/SINK/DOUBLE. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccPurchaseLedgerPeriod |
Purchase |
Returns the current ledger period number for the purchase ledger. |
Integer |
||
|
SgAccPurchaseLedgerPeriodYear |
Purchase |
Returns the current ledger year for the purchase ledger. |
String |
||
|
SgAccSalesLedgerPeriod |
Customer |
Returns the current ledger period number for the sales ledger. |
Integer |
||
|
SgAccSalesLedgerPeriodYear |
Customer |
Returns the current ledger year for the sales ledger. |
String |
||
|
SgAccSalesPrice |
Stock |
Stock Code Price Band |
Returns the sales price for a selected product. |
Real |
|
|
SgAccSalesProductQuantity |
Customer |
ProductCode Optional: Year Optional: Period |
Returns the total quantity sold for a stock item in a selected year and month. |
Integer |
Product Code: e.g. PM/SINK/DOUBLE. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccServerVersion |
Misc |
Returns the Sage 200 version number. |
String |
||
|
SgAccSpotRate |
Misc |
Currency |
Returns the exchange rate used for the specified currency. |
Real |
|
|
SgAccStockBaseUnit |
Stock |
StockCode |
Returns the base unit for the specified stock item. |
String |
|
|
SgAccStockCommodity |
Stock |
StockCode |
Returns the Intrastat commodity code for the specified stock item. |
String |
|
|
SgAccStockCostCentre |
Stock |
Returns the default CostCentre for the specified stock item. |
|||
|
SgAccStockCostOfSaleNominalLedger |
Stock |
Returns the cost of sales Nominal Ledger code for the specified stock item. |
|||
|
SgAccStockLocation |
Stock |
StockCode Warehouse |
Provides a drop-down list of locations in a warehouse that are associated with a specified stock item. |
String |
|
|
SgAccStockMargin |
Stock |
Returns the stock margin value for the specified year, period and currency. |
Integer |
||
|
SgAccStockName |
Stock |
StockCode |
Returns the name of the stock item from the code specified. |
String |
|
|
SgAccStockQuantityAllocated |
Stock |
StockCode Warehouse |
Returns the quantity of an item in stock at one or all warehouses. |
Real |
|
|
SgAccStockQuantityOnOrder |
Stock |
StockCode Warehouse |
Returns the quantity on order for a specified stock item. |
Real |
|
|
SgAccStockQuantitySold |
Stock |
Returns the quantity sold for the specified stock item in the specified year, period and currency. |
Integer |
||
|
SgAccStockSalesPrice |
Stock |
StockCode PriceBand |
Returns the sales price for the selected item and price band. |
Real |
|
|
SgAccStockSupplier |
Stock |
StockCode |
Returns the preferred supplier code for the specified stock item. |
String |
|
|
SgAccStockTotalQuantityInStock |
Stock |
StockCode Warehouse |
Returns the quantity sold of a stock item during the specified year and accounting period. |
Real |
|
|
SgAccStockType |
Stock |
StockCode |
Returns the type of stock, i.e. Stock, Miscellaneous or Services/Labour. |
String |
|
|
SgAccStockUnit |
Stock |
StockCode |
Returns the stocking unit for the specified stock item. |
String |
|
|
SgAccStockUnitList |
Stock |
StockCode |
Returns a drop-down list of units for a specified stock item. |
Drop-down |
|
|
SgAccStockValue |
Stock |
ProductCode |
Returns the value of the stock item currently in stock. |
Single |
Product Code: e.g. PM/SINK/DOUBLE. |
|
SgAccStockWarehouse |
Stock |
StockCode |
Returns a drop-down list of warehouses associated with a specified stock item. |
Drop-down |
|
|
SgAccStockWarehouseMaximum |
Stock |
StockCode Optional: Warehouse |
Returns the maximum stock level set for an item at a warehouse. |
Real |
|
|
SgAccStockWarehouseMinimum |
Stock |
StockCode Optional: Warehouse |
Returns the minimum stock level set for an item in a warehouse. |
Real |
|
|
SgAccStockWarehouseReOrder |
Stock |
StockCode Optional: Warehouse |
Returns the reorder level for a specified stock item and warehouse. |
Real |
|
|
SgAccStockWeight |
Stock |
StockCode |
Returns the weight (mass) for the specified stock item. |
Real |
|
|
SgAccSupplierAddress |
Purchase |
SupplierCode LineNumber |
Returns the text in the specified line of a supplier's address record. The LineNumber default value is 0 and returns all lines in one string. |
String |
|
|
SgAccSupplierAgedBalance |
Purchase |
This calculates the aged balance for a supplier in the same way as the object controller in Exchequer SG_erprise. |
Integer |
||
|
SgAccSupplierBalance |
Purchase |
Returns the balance from the supplier's CostCentre history for the specified year and period. |
Integer |
||
|
SgAccSupplierContact |
Purchase |
SupplierCode |
Returns the contact name for the specified supplier. |
String |
|
|
SgAccSupplierCostCentre |
Returns the CostCentre for the selected supplier. |
||||
|
SgAccSupplierCreditLimit |
Returns the supplier's credit limit. |
||||
|
SgAccSupplierCurrency |
Returns the supplier's default currency. |
||||
|
SgAccSupplierDepartment |
Purchase |
SupplierCode |
Returns the default department for a specified supplier. |
String |
|
|
SgAccSupplierEmailAddress |
Purchase |
SupplierCode |
Returns the specified supplier's e-mail address. |
String |
|
|
SgAccSupplierFax |
Purchase |
SupplierCode |
Returns the fax number for the specified supplier. |
String |
|
|
SgAccSupplierList |
Purchase |
Provides a drop-down list of all suppliers listed in the purchase ledger. |
Drop-down |
||
|
SgAccSupplierName |
Purchase |
SupplierCode |
Returns the company name of a supplier. |
String |
|
|
SgAccSupplierNominalLedger |
Returns the supplier's sales nominal ledger code. |
||||
|
SgAccSupplierOrders |
Purchase |
Account Optional: Year Optional: Period |
Returns the number of orders placed with a supplier in a selected year and month. |
Integer |
Purchase ledger account number. E.g. ATL001. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccSupplierOrderValue |
Purchase |
Account Optional: Year Optional: Period |
Returns the value of orders placed with a supplier in a selected year and month. |
Real |
Account Number: Purchase ledger account number, e.g. ATL001. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccSupplierPaymentDays |
Returns the supplier's payment terms. |
||||
|
SgAccSupplierPhone |
Purchase |
SupplierCode |
Returns the telephone number for the specified supplier. |
String |
|
|
SgAccSupplierPostcode |
Purchase |
SupplierCode |
Returns the postcode for the selected supplier. |
String |
|
|
SgAccSupplierSettlementDays |
Returns the settlement days from the supplier's record. |
||||
|
SgAccSupplierTaxCode |
Purchase |
SupplierCode |
Returns the tax code for the specified supplier. |
String |
|
|
SgAccThisYearNetProfit |
Nominal Ledger |
Returns the net profit for this year. |
Single |
||
|
SgAccTopCustomerByValue |
Sales |
Index Optional: Year Optional: Period |
Returns a customer name based on its rank by sales order value in a year or month. |
String |
Index: The rank index by sales order value. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccTopSalesProductCode |
Sales |
SalesValueIndex Optional: FinancialYear Optional: Period |
Returns a customer name, based on its rank by number of sales orders in a year and month. |
String |
Sales Index: The rank index by product sales order value. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccTopStockLevelProductCode |
Stock |
Index |
Returns a stock item name based on its rank by stock value in a year and month. |
String |
Sales Index: The rank index by product sales order value. |
|
SgAccTopSupplierByValue |
Purchase |
Index Optional: Year Optional: Period |
Returns a supplier name based on its rank by purchase order value in a year or month. |
String |
Index: The rank index by purchase order value. Financial Year: The financial year to report. Default value is 0 so that the whole financial year is selected. Period: The financial period to report. |
|
SgAccYearToDatePL |
Nominal Ledger |
Period |
Returns the year-to-date profit and loss. |
Single |
The financial period to report on, e.g. 1,2,3,4...12. |