Overview

Our Excel Add-In is a tool that pulls real-time Yellow Dog Inventory data into Microsoft Excel. This data can then be used by retail sites to create custom reports in Excel that use and manipulate this data, including open to buy reports. Most of the functions available with the add-in provide data based on store, level, and vendor and with a little setup, allow the user to pull a variety of data in a single report that would normally require multiple reports in Yellow Dog.

Installation

  1. Copy the appropriate 32- or 64-bit files (based on the version of Excel being used) from the Excel folder in the ftp to the client YellowDogInventory folder. The Add-In files must be in the same folder as the Yellow Dog settings.ini.
  2. Launch Excel.
  3. Click File > Options > Add-Ins tab > Manage (Excel Add-Ins) > Go.
  4. Click Browse and select ydiExcelAddIn2.xll in the client YellowDogInventory folder.
  5. Click OK.
  6. You should now have a new category called "Yellow Dog Inventory" in the Insert Function dialog.

Limitations

  • The detail of the data returned makes this a useful tool for retail, but not F&B sites.
  • All calculations are made by the parent item.
  • Results provide detail by level, not by item.

Functions

FunctionsDescription
AddYearsAdds years to the passed in value.
EndofMonthRounds the value to the last second of the month
fetchManualAdjustmentsCostReturns calculated cost made by manual adjustments. Calculated by parent.
fetchManualAdjustmentsQuantityReturns quantity made by manual adjustments. Calculated by parent.
fetchManualAdjustmentsRetailReturns the historical retail made by manual adjustments. Calculated by parent.
fetchOnHandCostReturns the total cost on hand as of date/time. Calculated by parent.
fetchOnHandQuantityReturns quantity on hand as of date/time. Calculated by parent.
fetchOnHandRetailReturns the historical retail on hand as of date/time. Calculated by parent.
fetchPhysicalInventoriesCostReturns the variance calculated cost due to physical inventory counts. Calculated by parent.
fetchPhysicalInventoriesQuantityReturns the variance quantity due to physical inventory counts. Calculated by parent.
fetchPhysicalInventoriesRetailReturns the variance historical retail due to physical inventory counts. Calculated by parent.
fetchReceiptsCostReturns calculated cost of adjustments made by receipts. Calculated by parent.
fetchReceiptsQuantityReturns quantity of adjustments made by receipts. Calculated by parent.
fetchReceiptsRetailReturns the historical retail of adjustments made by receipts. Calculated by parent.
fetchReturnToVendorCostReturns calculated cost of adjustments made by return to vendors. Calculated by parent.
fetchReturnToVendorQuantityReturns quantity of adjustments made by return to vendors. Calculated by parent.
fetchReturnToVendorRetailReturns the historical retail of adjustments made by return to vendors. Calculated by parent.
fetchSalesCostReturns calculated cost of adjustments made by sales. Calculated by parent. Returns as negative. Multiply by negative to show positive values.
fetchSalesQuantityReturns quantity of adjustments made by sales. Calculated by parent. Multiply by negative to show positive values.
fetchSalesRetailReturns the historical retail of adjustments made by sales. Calculated by parent. Multiply by negative to show positive values.
getLastAdjustmentReturns the datetime of the last cached adjustment.
getLevel1Returns a Level1 (typically Department) based on index. Index starts at 1. Only Levels with adjustments will be shown.
getLevel2Returns a Level2 (typically Category) based on index. Index starts at 1. Only Levels with adjustments will be shown.
getLevel3Returns a Level3 based on index. Index starts at 1. Only Levels with adjustments will be shown.
getLevel4Returns a Level4 based on index. Index starts at 1. Only Levels with adjustments will be shown.
getStoreReturns a Store by index. Index starts at 1.
getVendorReturns a Vendor by index. Index starts at 1. Only vendors with adjustments are shown. Only the first vendor for each item is used.
StartofNextMonth Rounds the value to the beginning of next month.

Notes

  • To refresh, use CTRL + ALT + SHIFT + F9
  • If Store is blank all stores will be reported.
  • If Level is blank all levels will be reported.
  • If Vendor is blank all vendors will be reported.
  • FromDate will be rounded down to 12:00 AM of that day.
  • ToDate will be rounded up to 11:59:59 PM of that day.
  • AsOfDateTime isn't rounded.